Home » SQL & PL/SQL » SQL & PL/SQL » CASE statement with Exists clause
CASE statement with Exists clause [message #302701] Tue, 26 February 2008 12:12 Go to next message
arksjd
Messages: 13
Registered: November 2007
Junior Member
HI ALL

I have the below query where i have to output acct_no and product code from a fact table.

I WANT TO CHECK IF VALUE OF THE PRODUCT_CODE IN EACH ROW of fact table IS PRESENT IN THE DIMension TABLE. IF NOT PRESENT I HAVE TO RETURN 'OTHERS' . I am getting an syntax error here. How can I acheive the same. Kindly help me..

SELECT
acct_no,
CASE prod_code
WHEN NULL THEN 'MISSING'
WHEN '0' THEN 'MISSING'

WHEN NOT EXISTS(SELECT 1 FROM DIM_PRODUCT B WHERE B.PROD_CODE = A.PROD_CODE) THEN 'OTHERS'

ELSE Prod_code END prod_code
FROM
FACT_TABLE A

Thanks
Re: CASE statement with Exists clause [message #302704 is a reply to message #302701] Tue, 26 February 2008 12:22 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rewrite it without this not exists part and with nvl((select to_char(product_code) ...),'OTHERS') in ELSE.

Regards
Michel
Previous Topic: Regarding Case
Next Topic: difference between decode Vs case...when
Goto Forum:
  


Current Time: Mon Dec 05 19:17:16 CST 2016

Total time taken to generate the page: 0.11024 seconds