Can a nested query be run in AS/400?
There are multiple lines (sequenced) of my records in a table with the same Key.
Example of the table Student No. Name Grade 1001 Michelle Fail 1001 Michelle Fail 1025 Peter Fail 1025 Peter Pass 1025 Peter Pass
I want to query Students with no Pass meaning. Select Student No. Where Grade = Fail and Student No. not in (Grade = Pass)
I can write it in SQL but how can I write this nested query in Select records of AS/400?
Software/Hardware used:
AS/400
Example of the table Student No. Name Grade 1001 Michelle Fail 1001 Michelle Fail 1025 Peter Fail 1025 Peter Pass 1025 Peter Pass
I want to query Students with no Pass meaning. Select Student No. Where Grade = Fail and Student No. not in (Grade = Pass)
I can write it in SQL but how can I write this nested query in Select records of AS/400?
Software/Hardware used:
AS/400
ASKED:
November 26, 2009 11:19 AM
UPDATED:
November 30, 2009 9:41 PM
Answer Wiki:
Nested Queries can be run on the AS/400. Use command STRSQL to test your query interactively.
I'm pretty sure I don't understand exactly what you are trying to do. I'm going to assume you want ot find students that have never had a passing grade. In your example, Michelle has never had a passing grade so you want her to show up - not Peter.
I'm also going to assume the name of the table is Students.
Select *
From Students
Where Student No. not in (
Select DISTINCT Student No.
From Students
Where Grade = 'Pass')
Best wishes: Craig
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks Craig, I can only use SELECT RECORDS in AS/400. Is there any way to write that nested query using SELECT RECORDS?
Example of the table
Student No. Name Grade
1001 Michelle Fail
1001 Michelle Fail
1025 Peter Fail
1025 Peter Pass
1025 Peter Pass
Are you saying that you want to use AS400 query and not SQL?
Craig’s example will run on the AS400.
You can either do a STRSQL and run the statement or put that code in a source member and use the RUNSQLSTM c ommand.
I can write it in SQL but how can I write this nested query in Select records of AS/400?
That doesn’t make much sense. I assume that you must be seeing “Select records” on some screen from something like Query/400. (Why would you use Query/400? It’s been practically obsolete for well over a decade.)
Why not just use SQL?
Tom
I can only use SELECT RECORDS in AS/400.
Why? Are you restricted by some administrator? All AS/400s (and iSeries and System i) have had SQL available for the past 20 years or so. The major obstacle is that many sites haven’t installed some of the nicer interfaces for SQL. But it’s still in the system and can be accessed and used in a variety of ways.
Tom
Why? Are you restricted by some administrator? All AS/400s (and iSeries and System i)…
It sounds like some sort of homework assignment for a query class.