How to resolve -122

skumars.tripathi · New User Joined: 27 May 2010 Posts: 12 Location: Kolkata

I have to execute following query in the program.
I tried to execute the query in spufi with some hard coded values for file inputs. I am getting -122.
Please help me out !!!!!!!

SELECT SUM(A.AM_SKU_RETAIL - "Some file input") *A.QU_STRINV_ACT, SUM(A.QU_STRINV_ACT)
FROM TMERC132 AS A, TMATT02 AS B
WHERE A.ID_SKU_NUM = B.ID_SKU_NUM AND
A.ID_STORE IN(SELECT ID_FACILITY FROM CHARM.TFCLTY01_STORE
WHERE IN_PRICE_ZONE = "Some file input")
GROUP BY ID_DVSC_DEPT, ID_VENDOR, ID_SKU_STYLE, COLOR_CODE
One more problem is, i dont have any host variable corresponding to the first file input that is going to be substracted from the retail ammount. How to pass that in the program query??

expat · Posted: Mon Jul 05, 2010 6:34 pm

Have you found out what -122 means.

dbzTHEdinosauer · Posted: Mon Jul 05, 2010 6:36 pm

1. 122 problem. normally, I just copy the select statement to the group by statement. The columnar function needs to be in the group by, also.

2. you need to define a host variable in working storage, that you will populate with your input file data before executing the sql.

to make the code/jcl in your posts more readable,
you should use the [code] tags, explained here

skumars.tripathi · New User Joined: 27 May 2010 Posts: 12 Location: Kolkata

Hi,
as i told you I have to use the SUM function like this-

SUM(COLUMN-NAME - Input value)

But we cant pass any host variable in the sum funtion. it should be either column name or any hardcoded static value.

Is there any way I can replace the input value with some host variable??

dbzTHEdinosauer · Posted: Mon Jul 05, 2010 7:58 pm

skumars.tripathi · New User Joined: 27 May 2010 Posts: 12 Location: Kolkata

If i m puting any variable in the query in program, While compiling the program itself i was getting MAXXCC=8 since there was -206 during bind, saying that column not found....

dbzTHEdinosauer · Posted: Mon Jul 05, 2010 8:32 pm

well, a column not found has nothing to do with a host variable.

why don't we do this:

you stop guessing and assuming, and we'll stop wasting time.

Since you made it to a bind, that means the db2 pre-compiler was ok.
if the host variable was an issue, the db2 pre-compile would have returned an error.

provide your imbedded sql (using [code] tags - for help with bbcode see this)

along with BIND error statements
and we will attempt to help you.

dbzTHEdinosauer · Posted: Mon Jul 05, 2010 8:38 pm

by the way, a host variable requires a colon ':' preceding the reference name:

host variable is variance-factor same pic type as affected column
sql should be
SUM (column_name - :variance-factor)

skumars.tripathi · New User Joined: 27 May 2010 Posts: 12 Location: Kolkata

I Know that host variable in query should be used with ':'
If i m using the host variable in query with : as given below

SELECT SUM(A.AM_SKU_RETAIL - :TMERC132-AM-SKU-RTL-NEW)

Its giving -401.

skumars.tripathi · New User Joined: 27 May 2010 Posts: 12 Location: Kolkata

Thanx guys, The problem is resolved. It was due to two columns from 2 tables that i was comparing. One was having small int type and other char . Therfore i need to type cast that before comparing.
I was confused and tried with different options.
Now got your point that we can use host variable inside aggegate function and its working fine.

Thanx a lot for your time.
Sanjay

dick scherrer · Posted: Tue Jul 06, 2010 9:42 am

Hello,

At the top of the page is a link to "IBM Manuals". Among these are some db2 Messages & Codes manuals.

Look up the -401 code and then at your code and correct the problem.

If you find something in the manual that is not clear, post what you found and your doubt. Someone will be able to clarify.