ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

sql ERROR SQL0305

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

  • sql ERROR SQL0305

    Code:
    Message . . . . :   Indicator variable required.                             
    Cause . . . . . :   A FETCH, an embedded SELECT, a CALL or a SET or VALUES   
      INTO statement has resulted in a null value, but an indicator variable was 
      not specified for host variable ONHOLD.  The relative position of the host 
      variable in the INTO clause or parameter list is 7. If the host variable   
      name is *N, an SQLDA was specified.                                        
    Recovery  . . . :   Specify an indicator variable, and precompile the program
      again.

    This was throwing the error

    Code:
    c                   clear                   OnHold        
    c/exec sql                                                
    c+ select sum(IOLCNQ)                         
    c+    into :OnHold                                        
    c+    from inofhld                                        
    c+    where iocomp    =  : imcomp      and                
    c+          iobranch  =  : imbranch    and                
    c+          iomat     =  : immat       and                
    c+          ioanal    =  : imanal      and                
    c+          iosize    =  : imsize      and                
    c+          iolin     =  : imlin                          
    c/end-exec

    I did this to get rid of error message

    Code:
    c                   clear                   OnHold        
    c/exec sql                                                
    c+ select COALESCE(sum(IOLCNQ),0)                         
    c+    into :OnHold                                        
    c+    from inofhld                                        
    c+    where iocomp    =  : imcomp      and                
    c+          iobranch  =  : imbranch    and                
    c+          iomat     =  : immat       and                
    c+          ioanal    =  : imanal      and                
    c+          iosize    =  : imsize      and                
    c+          iolin     =  : imlin                          
    c/end-exec

    can anyone explain why?

    Thanks
    Jamie
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  • #2
    Re: sql ERROR SQL0305

    Yes, if the file you are querying is NULL capable you must specify:

    Code:
    H ALWNULL(*INPUTONLY)
    Predictions are usually difficult, especially about the future. ~Yogi Berra

    Vertical Software Systems
    VSS.biz

    Comment


    • #3
      Re: sql ERROR SQL0305

      I dont have to put the H spec in if I use COALESCE....

      What does that mean/do I looked it up and it said

      Code:
      To grow together; fuse. 
      To come together so as to form one whole; unite: 
      The rebel units coalesced into one army to fight the invaders.
      What does that mean in SQL language?
      All my answers were extracted from the "Big Dummy's Guide to the As400"
      and I take no responsibility for any of them.

      www.code400.com

      Comment


      • #4
        Re: sql ERROR SQL0305

        In SQL, COALESCE means select the 1st value in the list that is NOT null. SO if you "SUM" value is null, the "0" is used. If not null the value of "sum" is used.

        Comment


        • #5
          Re: sql ERROR SQL0305

          Thanks guys......They said I wouldnt be able to learn anything new

          I found this

          Question:
          Code:
          What will the last two lines print. Will they be the same?
          
          DECLARE @test VARCHAR(2)
          DECLARE @first VARCHAR(4)
          DECLARE @second VARCHAR(4)
          
          SELECT @first = ISNULL(@test, 'test')
          SELECT @second = COALESCE(@test, 'test')
          
          PRINT @first
          PRINT @second

          answer:
          Code:
          Very interesting. Personally I'd never heard of the coalesce 
          keyword until reading this post (had to run to BOL to check it out). 
          isnull truncates the replacement value to the size of the expression 
          being checked whereas coalesce does not? Interesting behavior, 
          and not at all what I expected.

          kinda cool does this mean I could have used:
          coalesce
          isnull
          --or-- the header spec?

          thank you again
          jamie
          All my answers were extracted from the "Big Dummy's Guide to the As400"
          and I take no responsibility for any of them.

          www.code400.com

          Comment


          • #6
            Re: sql ERROR SQL0305

            Looks like we dont have the isnull

            ???
            jamie
            All my answers were extracted from the "Big Dummy's Guide to the As400"
            and I take no responsibility for any of them.

            www.code400.com

            Comment


            • #7
              Re: sql ERROR SQL0305

              Wouldn't be the same. Using the header you would result in a "null" value for the variable. With the coalesce, you get a numeric zero. Whether you care of not may depend on how you will use the resulting variable.

              Comment


              • #8
                Re: sql ERROR SQL0305

                Thats interesting the program ran as expected only it generated a joblog (it was a report) about a mile long...It never stopped running. So somehow it just ignored the null value.

                thank you for the time....
                if anyone else interested see below

                The COALESCE function can also handle a subset of the
                functions provided by CASE expressions. The result of using COALESCE(e1,e2)
                is the same as using the expression:

                Code:
                   
                CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 ENDVALUE
                can be specified as a synonym for COALESCE.

                Example 1: Assume that SCORE1 and SCORE2 are SMALLINT columns
                in table GRADES, and that nulls are allowed in SCORE1
                but not in SCORE2. Select all the rows in GRADES for which
                SCORE1 + SCORE2 > 100, assuming a value of 0 for SCORE1
                when SCORE1 is null.

                Code:
                     SELECT * FROM GRADES
                     WHERE COALESCE(SCORE1,0) + SCORE2 > 100;

                Example 2: Assume that a table named DSN8810.EMP contains a DATE
                column named HIREDATE, and that nulls are allowed for
                this column. The following query selects all rows in
                DSN8810.EMP for which the date in HIREDATE is either
                unknown (null) or earlier than 1 January 1960.




                Code:
                 
                   SELECT * FROM DSN8810.EMP
                     WHERE COALESCE(HIREDATE,DATE('1959-12-31')) < '1960-01-01'
                The predicate could also be coded as COALESCE(HIREDATE,'1959-12-31')
                because for comparison purposes, a string representation of a date can
                be compared to a date.

                Example 3: Assume that for the years 1993 and 1994 there is a table
                that records the sales results of each department. Each
                table, S1993 and S1994, consists of a DEPTNO column and
                a SALES column, neither of which can be null. The following
                query provides the sales information for both years.

                Code:
                   
                     SELECT COALESCE(S1993.DEPTNO,S1994.DEPTNO) AS DEPT, S1993.SALES, S1994.SALES
                     FROM S1993 FULL JOIN S1994 ON S1993.DEPTNO = S1994.DEPTNO
                     ORDER BY DEPT
                The full outer join ensures that the results include all departments,
                regardless of whether they had sales or existed in both years.
                The COALESCE function allows the two join columns to be combined
                into a single column, which enables the results to be ordered.
                All my answers were extracted from the "Big Dummy's Guide to the As400"
                and I take no responsibility for any of them.

                www.code400.com

                Comment


                • #9
                  Re: sql ERROR SQL0305

                  The VALUE keyword is synonomous with COALESCE.
                  Predictions are usually difficult, especially about the future. ~Yogi Berra

                  Vertical Software Systems
                  VSS.biz

                  Comment


                  • #10
                    Re: sql ERROR SQL0305

                    If you are doing a select using a field that could have a null value (esp a field from a join file), you can use a "null field indicator" instead of using a coalesce--actually you must have one or the other or you'll get those errors you found in your program.

                    You can define such a field as:

                    dnullFlag s 4b 0

                    then in your SQL, you would do this:

                    Select inField into :thsFld :nullFlag from thislib/thisfile

                    Notice there is no comma between :thsFld and :nullFlag. When I first ran into this problem it drove me crazy until I found out what was going on.

                    Comment


                    • #11
                      Re: sql ERROR SQL0305

                      Thank You I will give this a try also.


                      jamie
                      All my answers were extracted from the "Big Dummy's Guide to the As400"
                      and I take no responsibility for any of them.

                      www.code400.com

                      Comment


                      • #12
                        Re: sql ERROR SQL0305

                        SQL0305 ... Hmmm...

                        @#%@#%@#%@#% that sounds so FAMILIAR!!

                        Comment

                        Working...
                        X