• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL Can Return One or a Few Records

    February 23, 2005 Hey Ted

    SQL’s SELECT INTO statement works fine as long as only one record matches the criteria in the WHERE clause. If the query returns more than one record, the host variables are loaded with data, but the SQL State variable, SQLSTT, has a value of 21000, which according to the manual indicates a “Cardinality Violation.” Can I assume that the record loaded into the host variables really is the first record in the returned dataset? Or do you have a better suggestion?

    –Brad

    I suspect that the data in the host variables comes from the record you’re looking for, but being the ultra-conservative I am (compared to me, Ronald Reagan is in the same league with Karl Marx), I wouldn’t count on it. Let me give you another suggestion.

    If you’re at V5R1 or above, add the FETCH FIRST n ROWS ONLY clause to the SELECT statement. The number n can be omitted and defaults to the value one, and either ROW and ROWS is acceptable. Here’s an example:

    select * into :SomeDataStructure
      from SomeTable
     Where Something = SomethingElse
     order by SomeField
     fetch first row only
    

    If you’re running an earlier release, you’ll have to go to the trouble of declaring a cursor, opening it, and fetching from it one time.

    You can use the FETCH FIRST n ROWS ONLY clause with the regular SELECT statement, not just SELECT INTO. I threw together the following short program to illustrate this usage. It lists the first five customers in alphabetical order by name. That is to say, it sorts the records in alphabetical order, then returns the first five records from the sorted data.

    Fqsysprt   o    f  132        printer
    
    D SqlNormal       c                   const('00000')
    D SqlEOF          c                   const('02000')
    D Error           s             12a
    D CustRec       e ds                  extname(QCUSTCDT)
    
    C/exec sql
    C+ set option closqlcsr=*endmod
    C/end-exec
    
    C/exec sql
    C+ declare input cursor for
    C+ select * from qcustcdt
    C+ order by lstnam,init
    C+ fetch first 5 rows only
    C/end-exec
    
    C                   eval      *inlr = *on
    
    C/exec sql
    C+ open input
    C/end-exec
    
    C                   if        SqlStt <> SqlNormal
    C                   eval      Error = 'Open'
    C                   return
    C                   endif
    
    C                   dow       '1'
    C/exec sql
    C+ fetch input into :CustRec
    C/end-exec
    C                   if        SqlStt = SqlEOF
    C                   leave
    C                   endif
    C                   if        SqlStt <> SqlNormal
    C                   eval      Error = 'Fetch'
    C                   except    ErrorLine
    C                   return
    C                   endif
    C                   except    DtlLine
    C                   enddo
    
    Oqsysprt   e            ErrorLine   1
    O                       Error
    O                       SqlStt           +0001
    Oqsysprt   e            DtlLine     1
    O                       CusNum
    O                       LstNam           +0001
    O                       Init             +0001
    

    I sometimes use FETCH FIRST n ROWS only in interactive SQL to create a small result set. It’s similar to using the Number of Records to Copy (NBRRCDS) parameter of the Copy File (CPYF) command.


    I have read that some database management systems permit you to use a host variable to specify the number of rows to return. DB2/400 does not support this feature, at least not in V5R2. I tried it to make sure. I understand all databases in the DB2 family require you to hardcode a literal number of records.

    –Ted

    Click here to contact Ted Holt by e-mail.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Aldon Opens Up to Linux and AIX with Lifecycle Manager 5.0 Counterpane Brings OS/400 Into Its Managed Security Fold

    Leave a Reply Cancel reply

Volume 5, Number 8 -- February 23, 2005
THIS ISSUE
SPONSORED BY:

T.L. Ashford
WorksRight Software
COMMON

Table of Contents

  • CPYSPLF and AFPDS
  • SQL Can Return One or a Few Records
  • Admin Alert: Configuring Output Queues to Print Multi-Copy Spooled Files on Remote Printers

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle