|
View previous topic :: View next topic
|
| Author |
Message |
Ashishpanpaliya
New User
Joined: 13 Oct 2017
Posts: 34
Location: India
|
|
|
|
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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
Ashishpanpaliya
New User
Joined: 13 Oct 2017
Posts: 34
Location: India
|
|
|
|
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 |
|
|
Ashishpanpaliya
New User
Joined: 13 Oct 2017
Posts: 34
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1205
Location: Dublin, Ireland
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1205
Location: Dublin, Ireland
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
|
|
