Home » SQL & PL/SQL » SQL & PL/SQL » case statement (oracle 11g)
case statement [message #589766] Wed, 10 July 2013 10:48 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi,
In the following query which is highlighted ,I need to consider the records which have T.CURRENT_STATE='COMPLETE' AND 'CMPSCSRC' AND 'FINISHED' when M.MAINTAINED_FLAG = 'Y' AND S.SALE_LOCATION_ID = 118443 .So when i tried to write by using case stmt as follows it is giving me records only with T.CURRENT_STATE='COMPLETE'.But i want the records that satisfies all three current_states .How can i write this.Can anyone please help.
SELECT INI.UPC_ID,S.SALE_LOCATION_NAME,S.SALE_LOCATION_ID,I.KEYCAT_ID AS INITIAL_KEYCAT_ID,M.XLONG_NAME AS INITIAL_KEYCAT_NAME,
CASE WHEN M.MAINTAINED_FLAG = 'Y' THEN 'MAINTAINED' ELSE 'NON MAINTAINED' END AS INITIAL_MAINTAIN_DESC,
I.APPROVAL_USER_ID AS INITIAL_APPROVED_USER_ID,I.APPROVAL_DATE AS INITIAL_APPROVAL_DATE
FROM INITIALKEYCAT INI
JOIN ITEM_KEYCAT I ON
INI.UPC_ID = I.UPC_ID AND INI.INITIALDATE = I.APPROVAL_DATE AND INI.SALE_LOCATION_ID=I.SALE_LOCATION_ID
JOIN ITEM T ON
I.UPC_ID = T.UPC_ID AND I.SALE_LOCATION_ID=T.SALE_LOCATION_ID
JOIN SALE_LOCATION S ON
I.SALE_LOCATION_ID = S.SALE_LOCATION_ID
JOIN KEYCAT_MV M ON
I.KEYCAT_ID=M.KEYCAT_ID AND I.SALE_LOCATION_ID=M.SALE_LOCATION_ID AND S.DEFAULT_LANGUAGE_ID = M.LANGUAGE_ID
WHERE [color=limegreen]T.CURRENT_STATE =  CASE WHEN M.MAINTAINED_FLAG = 'Y' AND S.SALE_LOCATION_ID = 118443 THEN 'COMPLETE'
                              WHEN M.MAINTAINED_FLAG = 'Y' AND S.SALE_LOCATION_ID = 118443 THEN 'CMPSCSRC'
                              WHEN M.MAINTAINED_FLAG = 'Y' AND S.SALE_LOCATION_ID =  118443 THEN 'FINISHED'
                              ELSE T.CURRENT_STATE END[/color]


Thanks
Re: case statement [message #589767 is a reply to message #589766] Wed, 10 July 2013 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


since we don't have your tables or you data, we can't run, test, debug or improve posted SQL
Re: case statement [message #589771 is a reply to message #589767] Wed, 10 July 2013 11:26 Go to previous messageGo to next message
mikomi
Messages: 33
Registered: July 2013
Member
That's a case expression not a case statement. Case statements are used in PL/SQL and are executable statements. Case expressions are as the name suggests part of a larger statement or expression.
The following link may help:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:237794600346808562

As ever, it's often easier and/or more fruitful if you can break a problem up into pieces, solving it bit by bit or with trivial cases before putting the whole back together again.

Re: case statement [message #589794 is a reply to message #589771] Wed, 10 July 2013 14:42 Go to previous message
LKBrwn_DBA
Messages: 427
Registered: July 2003
Location: WPB, FL
Senior Member
Is this what you need?:
SELECT ini.upc_id
       , s.sale_location_name
       , s.sale_location_id
       , i.keycat_id AS initial_keycat_id
       , m.xlong_name AS initial_keycat_name
       , CASE WHEN m.maintained_flag = 'Y' THEN 'MAINTAINED' ELSE 'NON MAINTAINED' END AS initial_maintain_desc
     , i.approval_user_id AS initial_approved_user_id
     , i.approval_date AS initial_approval_date
  FROM initialkeycat ini
       JOIN item_keycat i
         ON ini.upc_id = i.upc_id
        AND ini.initialdate = i.approval_date
        AND ini.sale_location_id = i.sale_location_id
       JOIN item t
         ON i.upc_id = t.upc_id
        AND i.sale_location_id = t.sale_location_id
       JOIN sale_location s ON i.sale_location_id = s.sale_location_id
       JOIN keycat_mv m
         ON i.keycat_id = m.keycat_id
        AND i.sale_location_id = m.sale_location_id
        AND s.default_language_id = m.language_id
 WHERE m.maintained_flag = 'Y'
   AND s.sale_location_id = 118443
   AND t.current_state IN ('COMPLETE', 'CMPSCSRC', 'FINISHED')
/

[Updated on: Wed, 10 July 2013 15:11] by Moderator

Report message to a moderator

Previous Topic: Merge statement required with Logic
Next Topic: Oracle 11g,sql developer
Goto Forum:
  


Current Time: Thu Jul 31 20:05:02 CDT 2014

Total time taken to generate the page: 0.16351 seconds