General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Creating a cursor from 4 tables with SUM()
Hello all.
I seem to be having a small problem with a query I am trying to build that will return a cursor that consists of 4 fields.
my query looks liek this:
SELECT Invheader.i_idx, Invheader.i_invdate,
SUM(Itemlist.pi_px) as suminv,
SUM(Itemlist.pi_px) - IIF(isnull(SUM(Payinvapp.ip_appamnt)),0.00,SUM(Payinvapp.ip_appamnt)) as sumdue
FROM invheader, itemlist, payinvapp, jobs
WHERE Jobs.j_custidx = ?custnum AND Invheader.i_jobid = Jobs.J_idx AND Payinvapp.ip_invidx = Invheader.i_idx
AND Itemlist.pi_invidx = Invheader.i_idx into cursor Ccustpay
The problem is that the cursor comes up empty unless the payinvapp table has a value that meets the conditions.
is there a better way to do this.. I have the IIF to attempt to fool it into thinking there is a value of 0 in there but this doesnt seem to want to work at all.
The cursor should have everything in it for the customer represented by custidx and then all invoices and payments they have in all their jobs... the idea is to see the invoices that havent been paid off fully... not to see what we have made payments to.
any help with this will be appreciated.
thanks
B
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only