Skip to Content
0
Former Member
Jan 19, 2011 at 05:47 PM

SQL commands

148 Views

Crystal Reports generates this SQL:

SELECT LOAD.SPARENTLOAD, ORDERHEADER.SCARRIERID, ORDERHEADER.SSOLDCUSTOMER, LOAD.SLOADID

FROM (LOAD LOAD LEFT OUTER JOIN LOADLINEITEM LOADLINEITEM ON LOAD.SLOADID=LOADLINEITEM.SLOADID)

LEFT OUTER JOIN ORDERHEADER ORDERHEADER ON LOADLINEITEM.SORDERID=ORDERHEADER.SORDERID

WHERE ORDERHEADER.SCARRIERID='OTH' AND ORDERHEADER.SSOLDCUSTOMER LIKE '20151%';

After it gives me the records where LOAD.SLOADID = LOADLINEITEM.SLOADID, I need to select additional records from the LOAD table in a loop and I cannot figure out how to do that.

The LOAD table has nested Load IDs and I am trying to find a LOAD record that is

NOT( LOAD.SLOADID = LINIEITEMLOAD.SLOADID) and LOAD.SLOADID = LOAD.SPARENTLOAD.

StringVar Pallet;

BooleanVar DONE = FALSE;

WHILE DONE = FALSE

If LOAD.SLOADID <> LOAD.SPARENTLOAD then

SELECT LOAD.SLOADID, LOAD,SPARENTLOAD WHERE LOAD.SLOADID = LOAD.SPARENTLOAD

else

Pallet := LOAD.SPARENTLOAD

DONE := TRUE

endif

end WHILE

I have tried creating a command in the Database Expert, writing an SQL Expression, and creating a Formula.

In the command, I cannot figure out how create variable or how to put Pallet into the report

In SQL expression, I cannot do a SELECT.

In Formula, I cannot do a SELECT.

I am new to Crystal Reports and have searched your Forums but can't find a similar problem.

Edited by: jmrqzcr on Jan 19, 2011 6:48 PM

Edited by: jmrqzcr on Jan 19, 2011 6:52 PM