Nested Selects

5 pts.
Tags:
AS/400 database
AS/400 Query
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following