1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Select Sum gets SQLCODE -1405

Discussion in 'SQL PL/SQL' started by Fran, Jul 26, 2012.

  1. Fran

    Fran Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    I am using embedded SQL in COBOL to do a sum as follows:

    EXEC SQL
    SELECT SUM(Amount1),
    SUM(Amount2)
    INTO :w-amount1,
    :w-amount2
    FROM FORMTAB
    WHERE Company = :FORMTAB-COMP
    AND SubComp = :FORMTAB-SubComp
    AND Policy = :FORMTAB-Policy
    AND Reg = :FORMTAB-Reg
    AND Sequence = :FORMTAB-Sequence
    AND FormID = :FORMTAB-FormID
    AND FormSeq = :FORMTAB-FormSeq
    END-EXEC.

    In some cases, there are no rows that meet the criteria, and I would expect the SQLCODE to be 1403, however it is returning -1405 (fetched row returned nulls). Is this correct? If so, why - when no rows are returned?

    Thanks.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which pre-compiler are you using? Normally in COBOL count(*) returns 0 and all other aggregate functions return NULL when no rows meet the criteria.
     
  3. Fran

    Fran Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    We are using procob, David.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    My previous response will be, I think, the correct one as aggregates other than count(*) return NULL in COBOL when no rows satisfy the conditions. Only count(*) returns an actual numeric result when no rows are found.
     
  5. Fran

    Fran Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Okay, David. Thanks very much for the info. It is difficult to find!