how to do sub query in 4GL statement.

[mhtan88]

hi,

1) is 4GL code supported "sub query"?

2) if is supported. how if...

if in SQL statement. the statement as shown as below:

SELECT CustNum, (( SELECT Sum(TotalPaid) FROM pub.Invoice INV WHERE INV.CustNum = CustNum )) as Tutar
FROM
(
SELECT CustNum
FROM Pub.Customer CUST
)

how am i going to code in 4GL code using above sample?
Thank you.

TanMH

 

[Casper]

I think something like this will do the trick.

define variable isubTotal as decimal no-undo.
for each customer, 
   each invoice where invoice.custnum = customer.custnum
    break by invoice.custnum:
 
     if first of invoice.custnum
     then assign iSubtotal = 0.
 
     isubTotal = isubTotal + Totalpaid.
 
    if last-of(invoice.custnum)
    then display customer.custnum isubTotal.
 
end.

Casper.