IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to Fix SQLCODE=-206


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Wed Oct 10, 2018 9:20 pm
Reply with quote

Hi ,

I am getting SQLcode=-206 when I am running below code from program.

Code:
SELECT  VZSPA.PARAM_VALUE
INTO    :VZDSPA.PARAM-VALUE
        :VZLSPA-NULL.IND-PARAM-VALUE
FROM    VVZSPA VZSPA
WHERE   VZSPA.VZSPD_DEF_PK1       =
        :VZPC202I-SEARCH.VZSPD-DEF-PK1
AND     VZSPA.PARAM_EFF_DT        =
        (SELECT COALESCE(MAX(VZSPA1.PARAM_EFF_DT),
                         DATE('31.12.9999'))
         FROM VVZSPA VZSPA1
         WHERE VZSPA1.VZSPD_DEF_PK1 =
                         :VZPC202I-SEARCH.VZSPD-DEF-PK1
         AND   VZSPA1.PARAM_EFF_DT <= CURRENT_DATE
         AND ((:VZPC202I-SEARCH.VZNSC-FOR-FK4 = '  '
                AND VZSPA1.VZNSC_FOR_FK4 IS NULL)
             OR VZSPA1.VZNSC_FOR_FK4   =
                       :VZPC202I-SEARCH.VZNSC-FOR-FK4))



Same query, when I am running on SPUFI, its running perfect.


Code:
SELECT  VZSPA.PARAM_VALUE
--INTO    :VZDSPA.PARAM-VALUE
--        :VZLSPA-NULL.IND-PARAM-VALUE
FROM    ygt.VVZSPA VZSPA
WHERE   VZSPA.VZSPD_DEF_PK1       = '73'
--        :VZPC202I-SEARCH.VZSPD-DEF-PK1
AND     VZSPA.PARAM_EFF_DT        =
        (SELECT COALESCE(MAX(VZSPA1.PARAM_EFF_DT),
                         DATE('31.12.9999'))
         FROM ygt.VVZSPA VZSPA1
         WHERE VZSPA1.VZSPD_DEF_PK1 = '0073'
--                         :VZPC202I-SEARCH.VZSPD-DEF-PK1
         AND   VZSPA1.PARAM_EFF_DT <= CURRENT_DATE
         AND ((--:VZPC202I-SEARCH.VZNSC-FOR-FK4
                                    '  ' = '  '
                AND VZSPA1.VZNSC_FOR_FK4 IS NULL)
             OR VZSPA1.VZNSC_FOR_FK4   = '  '
             --          :VZPC202I-SEARCH.VZNSC-FOR-FK4

))
Note: -In program, I am getting VZNSC-FOR-FK4 as a blank and VZSPD-DEF-PK1 as a 0073
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Wed Oct 10, 2018 9:34 pm
Reply with quote

Start looking at sysout for Object-Name or Column Name when SQLCODE -206 or SQLCODE +206 is issued.

Why do you have two host variables in INTO ( that too without ',')when there is only one column in SELECT?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Oct 10, 2018 10:01 pm
Reply with quote

When you mention sql error code include a negative sign.

What is the object-name when you run into trouble? This should give you an idea on what is wrong.

Quote:
-206 object-name IS NOT VALID IN THE CONTEXT WHERE IT IS USED


Explanation


An object is specified in a context where it is not valid.
object-nameThe name of the object that could not be resolved.


Rohit, second host variable should be a null indicator.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Wed Oct 10, 2018 11:22 pm
Reply with quote

This is more appropriate representation of using the NULL indicator if that's so.
Code:
SELECT  VZSPA.PARAM_VALUE
 INTO    :VZDSPA.PARAM-VALUE :VZLSPA-NULL.IND-PARAM-VALUE
 FROM    VVZSPA VZSPA
 WHERE   VZSPA.VZSPD_DEF_PK1       =
         :VZPC202I-SEARCH.VZSPD-DEF-PK1
 AND     VZSPA.PARAM_EFF_DT        =
         (SELECT COALESCE(MAX(VZSPA1.PARAM_EFF_DT),
                          DATE('31.12.9999'))
          FROM VVZSPA VZSPA1
          WHERE VZSPA1.VZSPD_DEF_PK1 =
                          :VZPC202I-SEARCH.VZSPD-DEF-PK1
          AND   VZSPA1.PARAM_EFF_DT <= CURRENT_DATE
          AND ((:VZPC202I-SEARCH.VZNSC-FOR-FK4 = '  '
                 AND VZSPA1.VZNSC_FOR_FK4 IS NULL)
              OR VZSPA1.VZNSC_FOR_FK4   =
                        :VZPC202I-SEARCH.VZNSC-FOR-FK4))

However, I doubt how BIND step executed successfully ? SQLCODE -206 should come during the BIND and not during the execution unless you set off Validate NO for BIND.
Back to top
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Thu Oct 11, 2018 1:43 pm
Reply with quote

This is SQLCA displayed on CICS when I am executing this program:-

Code:
SQL COMMUNICATION AREA:
 SQLCABC      = 136
 SQLCODE      =   -206
 SQLERRML     = 010
 SQLERRMC     = '31.12.9999'
 SQLERRP      = 'DSNXORSO'
 SQLERRD(1-6) = -100,  000, 00000, -1, 00000,  000
 SQLWARN(0-A) = '_ _ _ _ _ _ _ _ _ _ _'
 SQLSTATE     = 42703


My Bind step is successfully. Infact in same program, my first query is running fine and failing on this second query.
Back to top
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 34
Location: India

PostPosted: Thu Oct 11, 2018 6:52 pm
Reply with quote

Below lines of code is working fine:-
Code:
SELECT  VZSPA.PARAM_VALUE
FROM    VVZSPA VZSPA
WHERE   VZSPA.VZSPD_DEF_PK1       = '0073'
        AND     VZSPA.PARAM_EFF_DT  <= date(12/31/9999)



but when I am trying to use below lines of code, its failing with SQL code 206.

Code:
SELECT  VZSPA.PARAM_VALUE
FROM    VVZSPA VZSPA
WHERE   VZSPA.VZSPD_DEF_PK1       = '0073'
        AND     VZSPA.PARAM_EFF_DT  <= date('31.12.9999')



any pointer for causing this issue
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Thu Oct 11, 2018 7:45 pm
Reply with quote

Quote:
This is SQLCA displayed on CICS when I am executing this program:-

Where is the "ERROR:" part of SQLCODE?
I suggest you talk to DBA and find out why it failed because I tried at my site and it worked. So something to do with Environment settings or DB2 version.
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1205
Location: Dublin, Ireland

PostPosted: Thu Oct 11, 2018 8:07 pm
Reply with quote

One thing that springs to mind is the difference in the code...

Code:
AND VZSPA.PARAM_EFF_DT <= date(12/31/9999)

and
Code:
AND VZSPA.PARAM_EFF_DT <= date('31.12.9999')


these date formats are very different and the SQLCA for the -206 shows

Code:
SQLERRMC     = '31.12.9999'


Garry
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Thu Oct 11, 2018 8:16 pm
Reply with quote

Garry, Do you think its a problem with EUR format or use of a DATE function or I missed the point?
Quote:
Below lines of code is working fine:-
SELECT VZSPA.PARAM_VALUE
FROM VVZSPA VZSPA
WHERE VZSPA.VZSPD_DEF_PK1 = '0073'
AND VZSPA.PARAM_EFF_DT <= date(12/31/9999)
I am sure this will not work unless you specify Dates in single quotes, so how did it work?
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1205
Location: Dublin, Ireland

PostPosted: Thu Oct 11, 2018 8:20 pm
Reply with quote

I wouldn't be sure, Rohit - it just that there are differences. The working version does not have quotes and the format is mm/dd/yyyy while the failing version has quotes and the format is dd.mm.yyyy .

Garry
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Thu Oct 11, 2018 8:22 pm
Reply with quote

You are right, no dates format would work without quotes. It highly looks like a typo. Moreover, even with missing quotes TS should never get -206 for that. We don't know what he is posting here is as same as he has actually tried.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Oct 11, 2018 10:02 pm
Reply with quote

If you have got -206 at bind time it's time to check your code

Otherwise, SQLCODE -206 typically indicates an issue with a column name or an undefined column in a SQL query.

Column Name Issue:
SQLCODE -206 often occurs when referencing a column name that does not exist in the specified table or is misspelled in the SQL statement.
It can also occur when using an ambiguous column name that exists in multiple tables referenced in the query.

Undefined Column:
Another common scenario for SQLCODE -206 is attempting to use a column that is not defined in the context of the SQL query, such as in a SELECT, UPDATE, INSERT, or DELETE statement.

Steps to Resolve SQLCODE -206:

Review SQL Statement:
Carefully review the SQL statement that triggered the SQLCODE -206 error.
Check for any typos, misspellings, or ambiguous column references in the SELECT list, WHERE clause, JOIN conditions, or other parts of the query.

Verify Column Existence:
Ensure that all referenced column names exist in the specified table or are correctly qualified with the table name or alias if used in a join operation.
Use tools like IBM Data Studio, SPUFI, or other SQL query editors to inspect table structures and column names.

Table Aliases and Joins:
If using table aliases or performing JOIN operations, verify that column references are properly qualified with the appropriate table alias to avoid ambiguity.
Check JOIN conditions to ensure they correctly match columns between joined tables.

Schema Qualification:
If working with tables in different schemas, ensure that column references include the schema name or alias to avoid ambiguity and ensure correct resolution.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Thu Oct 11, 2018 11:07 pm
Reply with quote

Quote:
If you have got -206 at bind time it's time to check your code
Did you address it to TS? then TS already acknowledged it passed the BIND. I don't know how more to trust TS since we don't have evidence of it passed the BIND but TS can be right if VALIDATE(RUN) is provided in BIND card which issues Warning messages and pass the BIND.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SQLCODE=-311 in Cobol SP-DB2. COBOL Programming 2
No new posts SQLCODE = -122 while using the scalar... DB2 4
No new posts SQLCODE = -16002 when using XMLEXISTS DB2 1
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
No new posts SQLCODE=-204 SQLSTATE=42704 DB2 4
Search our Forums:

Back to Top