|
View previous topic :: View next topic
|
| Author |
Message |
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
Hi,
I am facing an error while using CASE in WHERE condition. I am unable to fix it. Could you please help me to fix on how can I use the CASE condition.
| Code: |
SELECT CUSTOMER_SUPP,
LOCATION,
ITEMID,
EFF_START,
EFF_STOP,
ORDER_METHOD,
LOT_QUANTITY,
KANBAN,
RESPONSIBLE_USER,
EMPLOYEE,
LIFE_CYCLE,
SHIPPING_LOCATION,
VENDOR_SHARE_TYPE,
VENDOR_SHARE,
CATEGORY,
MINIMUM_LOT_COUNT,
CONTAINER_CODE
FROM TMMTEST.PRUKANB
WHERE BUSINESS_ENTITY = '03'
AND TYPE = 'CD'
AND CSI_TYPE = 'SU'
AND (CASE BUSINESS_ENTITY
WHEN '03'
THEN (VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
WHEN '02'
THEN (((VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
OR (VENDOR_SHARE_TYPE = 'R'
OR VENDOR_SHARE_TYPE = 'F')))
ELSE NULL
END)
AND ((EFF_START <= '2020-03-18'
AND ((EFF_STOP >= '2020-03-18'
OR CUSTOMER_SUPP = '0091400')
OR EFF_STOP IS NULL))
OR EFF_START > '2020-03-18')
AND ORDER_METHOD IN ('A',
'C',
'K',
'B')
ORDER BY CUSTOMER_SUPP,
LOCATION,
ITEMID,
EFF_START
WITH UR;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "!=". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: ??( [ CONCAT || / MICROSECONDS MICROSECOND SECONDS
SECOND
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 1685 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
X'00000695' X'000001F6' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 46
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 64
******************************** Bottom of Data ********************************
|
|
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1243
Location: Bamberg, Germany
|
|
|
|
| STFW results in telling that != is not valid SQL. Use <> there instead. |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
| I tried that already, but was unable to resolve the issue. It still told <> is invalid. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Try ¬= |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
It still throws the same error,
| Code: |
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "^=". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: ??( [ CONCAT || / MICROSECONDS MICROSECOND SECONDS
SECOND
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 1685 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
X'00000695' X'000001F6' SQL DIAGNOSTIC INFORMATION |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
In the THEN you must code a final value and not a condition and if you want a condition you should code another CASE.
That should solve these issues once fixed. Give a try. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2017
Location: USA
|
|
|
|
The author tries to use COBOL-like syntax in SQL code.
This cannot work.
RTFM. |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
Actually in THEN clause, it contains the query condition. Let me know how to use it ?. I cannot assign just one value and if i use another condition, I do not think it will come into that loop because when the business entity is '02' I need the entire below set,
WHEN '02'
THEN (((VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
OR (VENDOR_SHARE_TYPE = 'R'
OR VENDOR_SHARE_TYPE = 'F'))) . |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Reread what I said, you need to use CASE if you want conditions , in THEN usually you should have a resulting value.
Rather tell us what’s your business case is and may then some can help in better way. |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
My requirement is,
1. Based on the business entity, I need the condition to be attached to the existing SELECT query in WHERE clause,
When BE is '03', I need a condition like below attached to the existing query,
| Code: |
(CASE BUSINESS_ENTITY
WHEN '03'
THEN (VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
|
When BE is '02', I need a condition like below attached to the existing query,
| Code: |
WHEN '02'
THEN (((VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
OR (VENDOR_SHARE_TYPE = 'R'
OR VENDOR_SHARE_TYPE = 'F')))
|
|
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| abdulrafi wrote: |
| Code: |
SELECT CUSTOMER_SUPP,
...
CONTAINER_CODE
FROM TMMTEST.PRUKANB
WHERE BUSINESS_ENTITY = '03'
AND TYPE = 'CD'
AND CSI_TYPE = 'SU'
AND (CASE BUSINESS_ENTITY
WHEN '03'
THEN (VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
WHEN '02'
THEN (((VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
OR (VENDOR_SHARE_TYPE = 'R'
OR VENDOR_SHARE_TYPE = 'F')))
ELSE NULL
END)
AND ((EFF_START <= '2020-03-18'
AND ((EFF_STOP >= '2020-03-18'
OR CUSTOMER_SUPP = '0091400')
OR EFF_STOP IS NULL))
OR EFF_START > '2020-03-18')
AND ORDER_METHOD IN ('A',
'C',
'K',
'B')
|
|
Just no !
What happened to a plain
| Code: |
AND ((BUSINESS_ENTITY = '03' AND VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
OR (BUSINESS_ENTITY = '02' AND VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE IN ('P', 'F', 'R'))) |
or something like that (not tested)
Anyway the whole original WHERE is just a big mess: the first "WHERE BUSINESS_ENTITY = '03'" already makes the "CASE"obsolete, the dates selection is a mess as well. |
|
| Back to top |
|
|
|
|
