-303 sqlcode - mismatch in pic clause of host variable

lanand_hps · New User Joined: 05 Dec 2007 Posts: 82 Location: chennai

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..

dbzTHEdinosauer · Posted: Mon Mar 17, 2008 4:01 pm

stodolas · Posted: Mon Mar 17, 2008 5:30 pm

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.

lanand_hps · New User Joined: 05 Dec 2007 Posts: 82 Location: chennai

Mr.Steve, I have used MAX function that made me use null indicator, even if the field itself is NOT NULL by definition.

stodolas · Posted: Mon Mar 17, 2008 6:40 pm

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.

dbzTHEdinosauer · Posted: Mon Mar 17, 2008 8:02 pm

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.

stodolas · Posted: Mon Mar 17, 2008 8:08 pm

Early morning reading is not always recommended.

I had never seen the :wsvar:ws-null-ind used anywhere in my world, only the . notation...

dbzTHEdinosauer · Posted: Mon Mar 17, 2008 8:31 pm

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.

lanand_hps · New User Joined: 05 Dec 2007 Posts: 82 Location: chennai

The picture clause i use for null indicators is S9(04) COMP.
We always use :Host-var notation and not "."