Need help with particular SQL

[johncook]

Table: Invoices - 1 record per invoice
Table: LineItems - Line Items not attached to any invoice. 1 record per lineitem.

User will select the items to pay on the invoice. In other words, The line Items table is really just a list of items that can be attached to any ONE invoice. Once the line item is selected, for the current invoice, upon save of the current invoice, the line item gets the invoices id placed in it.

I want a view that:
If the invoice has been paid (invoices.lpaid=.t.) the view will contain the invoice items that have this invoices id in lineitems.invoicesid
If the invoice is NOT paid, the view will contain all line items where lineitems.invoicesid is empty.

CREATE SQL VIEW "V_lineitems" ;
AS SELECT lineitems.entdate, lineitems.ytotal_revenue,;
lineitems.ytotal_tax;
FROM lineitems;
WHERE (IIF(?v_invoices.lpaid;
,lineitems.invoicesid =;
?v_invoices.invoiceid, EMPTY(lineitems.invoicesid)))

The view, v_invoices is in scope when this sql is executed.

I probably just don't know how to write the WHERE clause when the WHERE clause needs to contain an expression.
Just to explain once more, The WHERE clause I have (above) says:
If v_invoices.lpaid is true include in my view all lineitems where lineitems.invoicesid = v_invoices.invoiceid BUT if v_invoices.lpaid is false, include in my view all lineitems where lineitems.invoicesid is empty.

Thanks,
John

 

[AirCon]

John,

If I understand you correctly, can you just use IF..ENDIF ?

If (v_invoices.lpaid == .T.)
CREATE SQL VIEW "V_lineitems" ;
AS SELECT lineitems.entdate, lineitems.ytotal_revenue,;
lineitems.ytotal_tax;
FROM lineitems;
WHERE (lineitems.invoicesid = v_invoices.invoiceid)
else
CREATE SQL VIEW "V_lineitems" ;
AS SELECT lineitems.entdate, lineitems.ytotal_revenue,;
lineitems.ytotal_tax;
FROM lineitems;
WHERE EMPTY(lineitems.invoicesid)))
endif


-- AirCon --

 

[ramani]

Hi JohnCook,

If AirCons answers replies you correctly, that is good.!

If not, please rephrase the question again to help better.



Table: Invoices - 1 record per invoice
Table: LineItems - Line Items not attached to any invoice. 1 record per lineitem.

User will select the items to pay on the invoice. In other words, The line Items table is really just a list of items that can be attached to any ONE invoice. Once the line item is selected, for the current invoice, upon save of the current invoice, the line item gets the invoices id placed in it.



If the line items gets the invoice id and gets saved, it is no longer a free list availablle for all invoices.

There is some thing which is not apparent.

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com