INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SELECTING 2 ROWS AS ONE

SELECTING 2 ROWS AS ONE

(OP)
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
 

RE: SELECTING 2 ROWS AS ONE

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
http://www.mirtheil.com

RE: SELECTING 2 ROWS AS ONE

(OP)
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)

RE: SELECTING 2 ROWS AS ONE

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

RE: SELECTING 2 ROWS AS ONE

(OP)
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  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close