Hi Elgin,
I propose to you to divide your select statement by parts. Do each FULL join separately in the temp cursor and then create a final result by joining all 6 (?) cursors.
If you get this working correctly, then you can substitute intermediate cursors with the derived tables in your original Select.
>All
>
>I want to join various fields from six tables with one SELECT statement. Can that be done?
>
>I need it to be a full join because I need all records from all tables that match my criteria. I have started a SELECT statement with five of the six tables as shown below. I think I need to use the FORCE keyword here to control the join evauluation, do I not?
>
>In the results I am looking at now a record in the "furanppm" table is being excluded that should be there. I have checked this record and I can't find a record in any of the other tables that match its date. Could that be why it is not included? I thought the FULL JOIN would handld that and that means to bring it into the result set regardless.
>
>Here's my current work:
>
>
>select v.custid, v.upsino, v.datinp, v.optemp, v.pktemp, ;
> l.dielectric, l.neut_no, l.ift, l.h20, ;
> d.hydrogen, d.methane, d.ethylene, ;
> i.aluminum, i.copper, i.iron, ;
> f.hydroxymth, f.furfuryl, f.furaldehyd ;
> from force visual v ;
> full join (liquid l full join dga d on d.custid = l.custid and d.upsino = l.upsino and d.puldat = l.puldat ;
> full join icp i on i.custid = d.custid and i.upsino = d.upsino and i.puldat = d.puldat ;
> full join furanppm f on f.custid = d.custid and f.upsino = d.upsino and f.puldat = d.puldat) on ;
> l.custid = v.custid and l.upsino = v.upsino and v.datinp = l.puldat ;
> where v.custid = "SC100234" and v.upsino = "0000004" ;
> order by v.upsino, v.datinp
>
>
>Thanks a million.
If it's not broken, fix it until it is.
My Blog