>I am trying to get all the invoice lines accociated with a specific invoices. This is the block of code I am trying to use.
>
>CREATE SQL VIEW invc_ln_view;
> CONNECTION myconnect AS;
> SELECT invc_ln.invc_ln_no, invc_ln.invc_no;
> FROM PROD.invc_ln invc_ln, ( temptbl ) maintbl;
> WHERE invc_ln.invc_no = maintbl.invc_no
>
>I get an oracle error saying that it doesn't like the referance to 'maintbl'. 'maintbl' is a temp table holding all the user chosen invoice numbers that they want a to print. What I want is to get the invoice lines for those invoices only. Is there a better way? Or just a minor adjustment to this code?
>
>Thanks in advance
If MainTbl is not an Oracle table, this will always fail. You might try limiting the number of invoices the user can retrieve line items for to 1 invoice at a time. Then change the select to create a parameterized view:
SELECT invc_ln.invc_ln_no, invc_ln.invc_no;
FROM PROD.invc_ln invc_ln;
WHERE invc_ln.invc_no = ?nInvc_No
Then all you have to do is:
use invc_ln_view nodata
*-- initialize the parameter for the view when you actually need to populate the view with data
local nInvc_No
nInvc_No = ThisForm.txtInvoiceNo.Value && convert to numeric if necessary
requery(Invc_Ln_View)
ThisForm.Refresh()
HTH
Mark McCasland
Midlothian, TX USA