we have been reviewing one of our scripts and real...
# suiteqls
Sim Greenbaum
10/12/2023, 7:55 PMwe have been reviewing one of our scripts and realized our SQL is wrong but not sure how o fix . THis sql will only return the amount billed on the po but we need the amount billed on that ( NextTransactionLineLink.nextdoc = 18348140) so if there is one bill per P.O. it works fine but if not there is trouble
Sim Greenbaum
10/12/2023, 7:55 PM Copy code
SELECT
transactionLine.transaction,
transactionLine.uniquekey,
transactionLine.memo,
transactionLine.item as item_id,
NVL(transactionLine.quantityshiprecv,0) recive,
transactionLine.quantitybilled,
NVL(transactionLine.units,0),
NVL((SELECT conversionrate FROM unitsTypeUom WHERE internalid = transactionLine.units),0) AS conversionrate,
NVL(transactionLine.quantityonshipments,0) AS on_boat ,
transactionLine.* ,
FROM transactionLine
INNER JOIN NextTransactionLineLink ON
NextTransactionLineLink.previousline = transactionLine.id AND
NextTransactionLineLink.previousdoc = transactionLine.transaction
WHERE NextTransactionLineLink.nextdoc = 18348140c
Clay Roper
10/12/2023, 7:56 PM@Sim Greenbaum What's wrong with it?
s
Sim Greenbaum
10/12/2023, 7:58 PMi just edit the message
Sim Greenbaum
10/12/2023, 7:59 PMso if there is one bill per P.O. it works fine but if there are two bills per po it will give me the total
c
Clay Roper
10/12/2023, 8:03 PMTo make sure I understand, the nextdoc (18348140) is a specific bill?
s
Sim Greenbaum
10/12/2023, 8:35 PMyes
c
Clay Roper
10/12/2023, 8:36 PMAnd this issue is coming when you have more than one bill associated with a particular PO line?
Clay Roper
10/12/2023, 8:38 PMIf that's the case, you would need to get the quantity from the bill instead of the PO line, because one PO line could have an associated line on more than one Bill. For example, let's say the PO line is for qty 100. Bill 18348140 could be for qty 20 and Bill 777777777 could be for qty 80. The query you have above will pull the full quantitybilled from any PO line that is associated with Bill 18348140.
s
Sim Greenbaum
10/12/2023, 8:43 PMcorrect but i need the uniquekey from the po to create an inbound shipment and qty from the bill
Sim Greenbaum
10/12/2023, 8:44 PMhence I'm confused how to have my cake and eat it
Sim Greenbaum
10/12/2023, 8:45 PMwe are creating a inbound shipment off th ebill
c
Clay Roper
10/12/2023, 8:46 PMJoin the transactionline table again as something like
v_billClay Roper
10/12/2023, 8:46 PMinstead of getting the quantitybilled from the PO
Clay Roper
10/12/2023, 8:50 PMSomething like this (untested)
s
Sim Greenbaum
10/12/2023, 9:15 PMso add another join after the where ?
c
Clay Roper
10/12/2023, 9:18 PMNo, sorry. I missed the WHERE due to its matching indentation to the lines above. Put the JOIN where a join belongs.
Clay Roper
10/12/2023, 9:19 PMedited the snippet
s
Sim Greenbaum
10/12/2023, 9:24 PMthowing syntax error
c
Clay Roper
10/12/2023, 9:25 PMI'll poke at it when I can spend some time to run it in my environment
Clay Roper
10/12/2023, 9:29 PMLooking at it, I goofed the CTE. Fixed in the snippet. I can't guarantee there won't be another issue though.
s
Sim Greenbaum
10/12/2023, 9:30 PMwhat does CTE stand for ?
c
Clay Roper
10/12/2023, 9:31 PMOh, also, since you're selecting transactionLine.*, you might want to rename that v_bill.quantity to something other than quantity or quantitybilled
Clay Roper
10/12/2023, 9:32 PMCommon Table Expression - that WITH statement at the top
s
Sim Greenbaum
10/12/2023, 9:41 PMturns out that for some reason THE SQL EDITOR IS THROWING THE SYNTAX ERORR NOT YOUR CODE !!!
Sim Greenbaum
10/12/2023, 10:00 PMthe issue this will only pull the first line of the bill
c
Clay Roper
10/12/2023, 10:02 PMWhen I run this query in my environment, it pulls all bill lines - e.g. on a bill with 2 lines, both appear. Is it possible that on the bill you're referencing a line was added that's not on the PO?
s
Sim Greenbaum
10/12/2023, 10:06 PMi am having two isssues first it is only pulling one line of the bill both bill and po have 10 lines 2nd for example if i run this 2nd bill it is chossing the amount of the first biill
c
Clay Roper
10/12/2023, 10:08 PMDid you change the ID in both places in the query?
Clay Roper
10/12/2023, 10:08 PMwhen looking at a second bill
s
Sim Greenbaum
10/12/2023, 10:08 PMlo am i sleep
Sim Greenbaum
10/12/2023, 10:08 PMhold on
Sim Greenbaum
10/12/2023, 10:09 PMmy old rule never write sql at 6pm when you usally leave work at 530
c
Clay Roper
10/12/2023, 10:09 PMIt's a solid rule 😄
s
Sim Greenbaum
10/12/2023, 10:12 PMthanx that was close
2 Views