IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQLCODE = -122 while using the scalar functions in the query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Taranprietsingh

New User


Joined: 25 Nov 2021
Posts: 10
Location: UK

PostPosted: Wed Feb 02, 2022 7:34 pm
Reply with quote

Hi Team, I am getting below error

SQLCODE = -122, ERROR: COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT VALID
SQLSTATE = 42803 SQLSTATE RETURN CODE

I am running below query in a JCL. I have checked IBM site also for this error, where it says all the columns being selected in the SELECT should be mentioned in the Group by clause. But still not working, Please help.

Code:

//SYSIN    DD *                                                 
SELECT CHAR(A.STORE_NUMBER ||','||                             
            A.ITEM_CODE ||',"'||                               
    SUBSTR(CHAR(A.WK_END_DATE),1,4) CONCAT '-' CONCAT           
    SUBSTR(CHAR(A.WK_END_DATE),5,2) CONCAT '-' CONCAT           
    SUBSTR(CHAR(A.WK_END_DATE),7,2) ||'",'||                   
    (CASE WHEN A.STORE_NUMBER IN(4910,4915) THEN 'V' ELSE       
    CASE WHEN A.STORE_NUMBER BETWEEN 4920 AND 4934 THEN 'A' ELSE
    CASE WHEN A.STORE_NUMBER BETWEEN 3000 AND 3999 THEN 'O' ELSE
    CASE WHEN B.COUNTRY_CODE IN('GB','GS','JS') THEN 'U' ELSE   
    CASE WHEN B.COUNTRY_CODE = 'IE' THEN 'R' ELSE ' '           
    END END END END END) ||'","'||                             
   TO_CHAR(CURRENT DATE,'YYYY-MM-DD') ||' '||                   
      CHAR(CURRENT TIME,JIS)||'"')                             
FROM OPCSUB.MSVSKUSL A                                         
LEFT OUTER JOIN OPCSUB.SDVSTRF2 B                               
ON B.STORE_NUMBER = A.STORE_NUMBER                             
WHERE A.WK_END_DATE >= LEFT(VARCHAR_FORMAT(                     
((CURRENT DATE) - ((DAYOFWEEK(CURRENT DATE)+13)DAYS)+6 DAYS)   
,'YYYYMMDD'),8)                                                 
GROUP BY                                                       
A.STORE_NUMBER, A.ITEM_CODE,                                   
SUBSTR(CHAR(A.WK_END_DATE),1,4) CONCAT '-' CONCAT               
SUBSTR(CHAR(A.WK_END_DATE),5,2) CONCAT '-' CONCAT               
SUBSTR(CHAR(A.WK_END_DATE),7,2),                               
(CASE WHEN A.STORE_NUMBER IN(4910,4915) THEN 'V' ELSE           
CASE WHEN A.STORE_NUMBER BETWEEN 4920 AND 4934 THEN 'A' ELSE   
CASE WHEN A.STORE_NUMBER BETWEEN 3000 AND 3999 THEN 'O' ELSE   
CASE WHEN B.COUNTRY_CODE IN('GB','GS','JS') THEN 'U' ELSE       
CASE WHEN B.COUNTRY_CODE = 'IE' THEN 'R' ELSE ' '               
END END END END END),                                           
CHAR(CURRENT DATE) CONCAT ' ' CONCAT                           
SUBSTR(CHAR(CURRENT TIME),1,2) CONCAT ':' CONCAT               
SUBSTR(CHAR(CURRENT TIME),4,2) CONCAT ':' CONCAT               
SUBSTR(CHAR(CURRENT TIME),7,2)     
FETCH FIRST 100 ROWS ONLY;         
/*                                 



What I have noticed is that issue is coming due to A.WK_END_DATE field. If I remove it from SELECT and group by, then query works OK. But I need to have it in my SELECT as well as in group by clause.
Please help with this. Many thanks in advance.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2945
Location: NYC,USA

PostPosted: Thu Feb 03, 2022 5:49 pm
Reply with quote

Carefully check again the Select and Group by columns as per the Rules for Group by clause .
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1614

PostPosted: Sun Feb 06, 2022 4:46 am
Reply with quote

Your WK_END field has been used in multiple places of your complex SQL.

Try to add each single instance of this field one by one, rather than all of them together. If you run the query each time one new WK_END has been added, you would easily detect the incorrect addition.

I always use this approach instead of putting all my garbage together into one single mess, and then appealing to the forum: “What can be wrong??!!”
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2945
Location: NYC,USA

PostPosted: Sun Feb 06, 2022 8:54 pm
Reply with quote

In addition you don’t need hardcoding of those substr and concat for dates which makes look more complex and incorrect.

To convert dates to a different format , explore VARCHAR_FORMAT function.
Back to top
View user's profile Send private message
Taranprietsingh

New User


Joined: 25 Nov 2021
Posts: 10
Location: UK

PostPosted: Tue Feb 08, 2022 3:58 pm
Reply with quote

Thank you for prompt reply.
I am trying to work out your suggestions. I had tried using VARCHAR_FORMAT and TO_CHAR for that WK_END field but getting errors. I have tried it like this

Code:

TO_CHAR(A.WK_END_DATE,'YYYY-MM-DD')


But it gives me error saying - SQLCODE = -20476, ERROR: THE TO_CHAR FUNCTION WAS INVOKED WITH AN INVALID FORMAT STRING YYYY-MM-DD

It has not been defined as a DATE field in the table, rather, it is defined as

COLTYPE LENGTH
-------+---------
INTEGER 4

So inside the table, when I check, the date is stored as YYYYMMDD

And I need to have the this date in my output in this format - YYYY-MM-DD

Can you please let me know how that can be done succesfully.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
No new posts DB2, write report based on query outp... DB2 1
No new posts Using WITH UR in SELECT SUM query DB2 1
No new posts DSNTIAUL and DB2 Scalar Functions DB2 17
No new posts SQLCODE = -16002 when using XMLEXISTS DB2 1
Search our Forums:

Back to Top