Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

-303 sqlcode - mismatch in pic clause of host variable

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
lanand_hps

New User


Joined: 05 Dec 2007
Posts: 82
Location: chennai

PostPosted: Mon Mar 17, 2008 3:51 pm    Post subject: -303 sqlcode - mismatch in pic clause of host variable
Reply with quote

Hi,
The below query is resulting in -303 sqlcode.

SELECT MAX(A.PAPER_ONLY_DLVY)
INTO :WS-PAPER-ONLY-DLVY:WS-NULL-IND
FROM PAY_CAL_DETL A
WHERE A.OWNING_CARRIER = 'MU'
AND A.PAY_CALENDAR_ID = 'MU-MONTHLY'
AND A.PAPER_ONLY_DLVY <= CURRENT DATE


The field PAPER_ONLY_DLVY is DATE NOT NULL in database.
I've defined the host variable WS-PAPER-ONLY-DLVY as X(10)

I read the description for -303. It says mismatch in pic clause of host variable and table variable. But it is correct.

Your views please..
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Mar 17, 2008 4:01 pm    Post subject:
Reply with quote

Quote:

I read the description for -303. It says mismatch in pic clause of host variable and table variable. But it is correct.

Your views please..


i have found that db2 does not make that many mistakes. i imagine that the definition of the column is not as you think.

go to the ibm sys tables to get the definition of this column. do not rely on dclgen.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Mar 17, 2008 5:30 pm    Post subject:
Reply with quote

If it's NOT NULL, then why are you giving a NULL INDICATOR for it? That seems kind of silly. Also, why aren't you using the DCLGEN? It usually helps to eliminate things like this.
Back to top
View user's profile Send private message
lanand_hps

New User


Joined: 05 Dec 2007
Posts: 82
Location: chennai

PostPosted: Mon Mar 17, 2008 6:28 pm    Post subject:
Reply with quote

Mr.Steve, I have used MAX function that made me use null indicator, even if the field itself is NOT NULL by definition.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Mar 17, 2008 6:40 pm    Post subject:
Reply with quote

The only way MAX can return NULL is as follows (from the manual):

If the function is applied to an empty set, the result is the null value. Otherwise, the result is the maximum value in the set.

Because the field is defined NOT NULL, that would mean your whole table would have to be empty in order for you to get a NULL back from the MAX function.

However, if you still feel you need the null indicator defined, you should get the syntax on the command correct. See below.

Code:

SELECT MAX(A.PAPER_ONLY_DLVY)
INTO :WS-PAPER-ONLY-DLVY.WS-NULL-IND
FROM PAY_CAL_DETL A
WHERE A.OWNING_CARRIER = 'MU'
AND A.PAY_CALENDAR_ID = 'MU-MONTHLY'
AND A.PAPER_ONLY_DLVY <= CURRENT DATE


You put a . between a ws field and the null indicator for it, not a :
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Mar 17, 2008 8:02 pm    Post subject:
Reply with quote

Steve,

normally you are right on.

but, if the result set is Null (due to the where clause), he will indeed have a Null returned.

and his syntax works in my world. actually, you have created a qualifier 'ws-paper-only-dlvy for element ws-null-ind.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Mar 17, 2008 8:08 pm    Post subject:
Reply with quote

Early morning reading is not always recommended.

I had never seen the :wsvar:ws-null-ind used anywhere in my world, only the . notation...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Mar 17, 2008 8:31 pm    Post subject:
Reply with quote

Steve,

as I was preparing an answer to you, it occured to me to ask the OP,



what is the picture clause for WS-NULL-IND?



back to Steve;

normally i code :Host-var :Null-ind space inbetween - but the db2 precompiler for vsn 6 & 7 also allow for no space. I have always used a period to separate the qualifier from the element.
Back to top
View user's profile Send private message
lanand_hps

New User


Joined: 05 Dec 2007
Posts: 82
Location: chennai

PostPosted: Mon Mar 17, 2008 9:43 pm    Post subject:
Reply with quote

The picture clause i use for null indicators is S9(04) COMP.
We always use :Host-var notation and not "."
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Writing on to GSAM variable length rohanthengal JCL & VSAM 6 Fri Mar 03, 2017 7:46 pm
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am
No new posts Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm
No new posts DB2 9.1.0 not move values to Host-Var... Miguel Fernandez DB2 8 Wed Jan 25, 2017 8:24 pm
No new posts DB2 9.1.0 not move values to Host-Var... Miguel Fernandez DB2 0 Wed Jan 25, 2017 8:23 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us