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
 

 

How to resolve -122

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

New User


Joined: 27 May 2010
Posts: 12
Location: Kolkata

PostPosted: Mon Jul 05, 2010 6:16 pm    Post subject: How to resolve -122
Reply with quote

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??
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon Jul 05, 2010 6:34 pm    Post subject:
Reply with quote

Have you found out what -122 means.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jul 05, 2010 6:36 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
skumars.tripathi

New User


Joined: 27 May 2010
Posts: 12
Location: Kolkata

PostPosted: Mon Jul 05, 2010 6:59 pm    Post subject: Reply to: How to resolve -122
Reply with quote

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??
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jul 05, 2010 7:58 pm    Post subject:
Reply with quote

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


Why can't you? are you getting an error of somekind because of the host variable.
Back to top
View user's profile Send private message
skumars.tripathi

New User


Joined: 27 May 2010
Posts: 12
Location: Kolkata

PostPosted: Mon Jul 05, 2010 8:13 pm    Post subject: Reply to: How to resolve -122
Reply with quote

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....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jul 05, 2010 8:32 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jul 05, 2010 8:38 pm    Post subject:
Reply with quote

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)
Back to top
View user's profile Send private message
skumars.tripathi

New User


Joined: 27 May 2010
Posts: 12
Location: Kolkata

PostPosted: Tue Jul 06, 2010 8:54 am    Post subject: Reply to: How to resolve -122
Reply with quote

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.
Back to top
View user's profile Send private message
skumars.tripathi

New User


Joined: 27 May 2010
Posts: 12
Location: Kolkata

PostPosted: Tue Jul 06, 2010 9:41 am    Post subject: Reply to: How to resolve -122
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jul 06, 2010 9:42 am    Post subject:
Reply with quote

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.
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 How to resolve IMS DB status code FM Priyanka Boga IMS DB/DC 3 Fri Jun 10, 2016 1:54 pm
No new posts how to Resolve SQL Error Code -924 Rajesh3825 ABENDS & Debugging 2 Thu Dec 10, 2015 2:39 pm
No new posts How to resolve -532 pkmurali DB2 3 Thu Aug 06, 2015 10:03 pm
No new posts Resolve symbolics - other than starti... Shrinika Rajendran JCL & VSAM 4 Tue May 27, 2014 6:15 pm
No new posts SOC7 abend- How to resolve it sandeep kumar302 COBOL Programming 24 Wed Apr 30, 2014 5:57 pm


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