Can a nested query be run in AS/400?

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

Answer Wiki

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

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

Discuss This Question: 5  Replies

 
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
  • Bonbon78
    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
    30 pointsBadges:
    report
  • CharlieBrowne
    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.
    41,380 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • Teandy
    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.
    5,860 pointsBadges:
    report

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