Nested Selects
A project I am working on requires that I do some stuff on a iSeries/AS400 DB (which I'm accessing through a linked server on a remote machine).
I'm trying to build a query, but some of the AS400 syntax is giving me problems, and I was hoping that someone would be able to help me with this.
the table looks something like:
DEAL5 RO5 LINE5 RODTE5 VISIT5
--------- --------- ----- ---------- ------
5018 23054 1 20080826 H
5446 203714 1 20080821 H
5446 203714 1 20080825
Basically, I want the first line to show up, because it has the H, but I don't want the second or third line because the H on the second line has been resolved on the third line.
SELECT A.DEAL5, A.RO5, A.LINE5, A.RODTE5 (SELECT B.RO5 FROM NCLM5 B WHERE A.RO5=B.RO5 AND VISIT5='H' ORDER BY RODTE5 FETCH FIRST 1 ROWS ONLY) FROM NCLM5 A GROUP BY A.RO5
Nested selects are something that I have trouble with normally, and the unfamiliar syntax is just making it harder.
I'm trying to build a query, but some of the AS400 syntax is giving me problems, and I was hoping that someone would be able to help me with this.
the table looks something like:
DEAL5 RO5 LINE5 RODTE5 VISIT5
--------- --------- ----- ---------- ------
5018 23054 1 20080826 H
5446 203714 1 20080821 H
5446 203714 1 20080825
Basically, I want the first line to show up, because it has the H, but I don't want the second or third line because the H on the second line has been resolved on the third line.
SELECT A.DEAL5, A.RO5, A.LINE5, A.RODTE5 (SELECT B.RO5 FROM NCLM5 B WHERE A.RO5=B.RO5 AND VISIT5='H' ORDER BY RODTE5 FETCH FIRST 1 ROWS ONLY) FROM NCLM5 A GROUP BY A.RO5
Nested selects are something that I have trouble with normally, and the unfamiliar syntax is just making it harder.
ASKED:
Oct 8, 2008 3:47 PM GMT
UPDATED:
October 8, 2008 4:18:15 PM GMT
RELATED QUESTIONS
- QSH on two differnet iSeries
- SQL Syntax Error SQL0104
- VB Script to delete old files fom the local and/or remote computer(s)
- Unable to browse local intranet PHP site
- VB Script to monitor files on Windows machines from a Zenoss server
- What is correct syntax for SQL for Maxrecords in OS400/DB2?
- Announcing the HP Elite Series Scavenger Hunt (sponsored)
- LEFT JOIN depending on more conditions
- RPG Free Array Processing Problem
- Error: 'd.getElementById(...)'is null or not object(show in main web page)
Answer Wiki:
You could try something like this.
The Subselect picks the list that you don't want so you use the NOT IN (the list)
SELECT A.DEAL5, A.RO5, A.LINE5, A.RODTE5 FROM NCLM5 A
where A.Visit5 = 'H' and A.R05 not in
(select A.R05 from NCLM5 where visit5 = ' ')
I believe that this is the same SQL command that you would use in any other database.
The Subselect picks the list that you don't want so you use the NOT IN (the list)
SELECT A.DEAL5, A.RO5, A.LINE5, A.RODTE5 FROM NCLM5 A
where A.Visit5 = 'H' and A.R05 not in
(select A.R05 from NCLM5 where visit5 = ' ')
I believe that this is the same SQL command that you would use in any other database.
36,420 pts.
To see other answers submitted to the Answer Wiki View Answer History.
