Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SELECTING 2 ROWS AS ONE

Status
Not open for further replies.

Johnny48

Technical User
Aug 19, 2010
18
CA
Hello,
Trying to built a simple ACCPAC report which requires OPTIONAL FIELD TABLE VALUES to be JOINED to the MAIN select ......

I have this :

Code:
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
Code:
 ICITEMO.OPTFIELD="4110"
to the above COLUMNS returned.....

Can anyone help with this ?

Cheers,
Johnny
 
Probably something like:
Code:
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
 
Thanks Mirtheil,

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

Code:
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)
 
Can you give an example of what you want it to return and what it actually returns?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
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 ?

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top