General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
John,
Not clear what does ?v_invoices.lpaid mean: sign "?" indicates that user should type the value, but v_invoices.lpaid seems it is the field lpaid in the already open table v_invoices.
Anyway, this should work:
CREATE SQL VIEW V_lineitems ;
AS SELECT lineitems.entdate, lineitems.ytotal_revenue,;
lineitems.ytotal_tax;
FROM lineitems;
WHERE lineitems.invoicesid =IIF(?v_invoices.lpaid,;
v_invoices.invoiceid, space(len(lineitems.invoicesid)))
>Table Invoices: 1 rec per invoice
>Table LineItems: 1 rec per lineitem that can be attached to any ONE invoice.
>
>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, 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.
>
>That is what i want to do. It is not working.
>
>Thanks,
>John
Previous
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