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.
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??!!”
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.
