ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Syntax Error ?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Syntax Error ?

    Is there a syntax error here ?
    All the fields exist.
    When I run my program, I think it fails here, because I get no output. When I comment it out, I get output.

    C/EXEC SQL
    C+ SELECT MIN(ARCCCU) INTO :SAVCUST#
    C+ FROM QTEMP/RAR
    C+ WHERE :W8RCST = RCUST and :W8WDOC = ARDOCN
    C+ AND :W8AMT = RAMT
    C/END-EXEC

    Edit, I just reran with some error trapping and got

    SQLCOD = 305
    SQLSTT = 22002

    gotta run and log off, will try to figure out codes later.
    Last edited by MFisher; March 18, 2020, 05:12 PM.

  • #2
    Looks like I am getting a NULL if no records found, so I guess I need to check first that count(*) is > 0

    22002
    "...A null value, or the absence of an indicator parameter was detected; for example, the null value cannot be assigned to a variable, because no indicator variable is specified...."

    Comment


    • #3
      Or, you can just change this:
      Code:
      MIN(ARCCCU)
      To this:
      Code:
      IFNULL(MIN(ARCCCU),0)
      Now if the value of min is null because there are no records, it will get converted to zero (or to whatever value you set as the 2nd argument of ifnull()) so the statement won't error.

      Comment


      • B.Hauser
        B.Hauser commented
        Editing a comment
        I'd prefer COALESCE instead of IFNULL just because it is more powerful
        Birgitta

    • #4
      Instead of running an additionally query to check if a value can be found, you have 2 other ways:
      1. Convert the NULL value into a default value with the COALESCE Scalar Function.
      Code:
      Exec SQL  Select Coalesce(Min(ARCCCU), 0) into :SAVCUST# ....
      2. Define and use an Indicator Variable (5I 0 / Int(5)).
      The Indicator variable must be specified immediately after the Host Variable separated only by a blank.
      If a NULL value is returned the Indicator Variable is set to -1 if a real value is returned the Indicator Variable is set to 0:
      Code:
          DCL-S  SavCust#Ind   Int(5);
      
          Exec SQL SELECT Min(ARCCCU) Into :SavCust# :SavCust#Ind
                FROM ....
      
          If SavCust#Ind = -1;   //NULL Value
              Clear SavCust#;
          EndIf;
      Birgitta

      Comment


      • #5
        Thanks for all the suggestions !

        Comment

        Working...
        X