SELECTING 2 ROWS AS ONE

[Johnny48]

Hello,
Trying to built a simple ACCPAC report which requires OPTIONAL FIELD TABLE VALUES to be JOINED to the MAIN select ......

I have this :

SELECT POPORH1.DATE, POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORH1.EXPARRIVAL AS PO_ETD, POPORL.EXPARRIVAL AS LINE_ETA, POPORL.OQORDERED, POPORL.OQRECEIVED, POPORL.OQOUTSTAND, ICITEMO.VALUE
FROM (POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEMO ON POPORL.ITEMNO = ICITEMO.ITEMNO
WHERE (((POPORH1.PONUMBER)="004061") AND ((POPORH1.ISCOMPLETE)=0) AND ((POPORL.COMPLETION)=1) AND ((ICITEMO.OPTFIELD)="4250"));

I would need to also ADD the

 ICITEMO.OPTFIELD="4110"

to the above COLUMNS returned.....

Can anyone help with this ?

Cheers,
Johnny

 

[mirtheil]

Probably something like:

SELECT POPORH1.DATE, POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORH1.EXPARRIVAL AS PO_ETD, POPORL.EXPARRIVAL AS LINE_ETA, POPORL.OQORDERED, POPORL.OQRECEIVED, POPORL.OQOUTSTAND, ICITEMO.VALUE
FROM (POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEMO ON POPORL.ITEMNO = ICITEMO.ITEMNO
WHERE (((POPORH1.PONUMBER)="004061") AND ((POPORH1.ISCOMPLETE)=0) AND ((POPORL.COMPLETION)=1) AND ((ICITEMO.OPTFIELD)="4250") OR ICITEMO.OPTFIELD="4110");

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Johnny48]

Thanks Mirtheil,

However does not seem return the «both» values as columns for the ICITEMO.VALUES.....

SELECT POPORH1.DATE, POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORH1.EXPARRIVAL AS PO_ETD, POPORL.EXPARRIVAL AS LINE_ETA, POPORL.OQORDERED, POPORL.OQRECEIVED, POPORL.OQOUTSTAND, ICITEMO.VALUE (4250), ICITEMO.VALUE (4110)

 

[mirtheil]

Can you give an example of what you want it to return and what it actually returns?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Johnny48]

Thanks Mirtheil,


I needed to add a bracket after the AND in the WHERE clause....
Now I retreive 2 rows with the below, one row with the value column heading of VALLUE containing the value for ICITEMO.OPTFIELD="4250 and another row with the value ICITEMO.OPTFIELD)="4110.

Can this be returned as one row as an added column ?

SELECT POPORH1.DATE, POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORH1.EXPARRIVAL AS PO_ETD, POPORL.EXPARRIVAL AS LINE_ETA, POPORL.OQORDERED, POPORL.OQRECEIVED, POPORL.OQOUTSTAND, ICITEMO.VALUE
FROM (POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEMO ON POPORL.ITEMNO = ICITEMO.ITEMNO
WHERE (((POPORH1.PONUMBER)="004061") AND ((POPORH1.ISCOMPLETE)=0) AND ((POPORL.COMPLETION)=1) AND (((ICITEMO.OPTFIELD)="4250") OR ICITEMO.OPTFIELD="4110"));

I would to know if its possible to I guess «UNION» or «COMBINE» somehow to