in the below query i am using 'case ' statement.Thsi throws an error, please help me to frame the case statement
select mct1.cproductid,mct1.c_summ_strength_fsn,mct1.csumofstrengthnumeratoruomid,
mct1.csumofstrengthdenomuomid,round(mct1.CSUMOFSTRENGTHNUMFRVALUE0) FrNum,round(mct1.csumofstrengthnumtovalue) ToNum,
round(mct1.CSUMOFSTRENGTHDENOMVALUE0) Denom,(case when ToNum is null then FrNum end) SORR
FROM
MCT_34498 mct1,
principalKey pkey1,
goldenCopy gc
where pkey1.catalogid = gc.catalogid
AND pkey1.productkeyid = mct1.cproductkeyid
AND pkey1.modversion = mct1.cmodversion
AND mct1.cmodversion = gc.version
AND gc.productkey = mct1.cproductkeyid
AND pkey1.active = 'Y';
END;
I have changed the case statement like this, teh error says missing right paranthesis..can you please help me to fix this
case (when (CSUMOFSTRENGTHNUMFRVALUE0 is not null) and
(csumofstrengthnumtovalue is null) and (CSUMOFSTRENGTHDENOMVALUE0 is null)
then (CSUMOFSTRENGTHNUMFRVALUE0))
select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
case when (CSUMOFSTRENGTHNUMFRVALUE0 is not null) and
(csumofstrengthnumtovalue is null) and (CSUMOFSTRENGTHDENOMVALUE0 is null)
then CSUMOFSTRENGTHNUMFRVALUE0 end
also, there might be syntax error in other parts of query.. try above modification... if u continue to face the issue, post the complete query in question
hope this helps...
Cheers!
KK
Please mark as Answer if post helps in resolving your issue
My Site
Marked as answer by poojakulkarni on Dec 13, 2011 06:53 AM
poojakulkarn...
Member
24 Points
109 Posts
Help to use case statement in oracle
Dec 13, 2011 01:46 AM|LINK
Hi ,
in the below query i am using 'case ' statement.Thsi throws an error, please help me to frame the case statement
select mct1.cproductid,mct1.c_summ_strength_fsn,mct1.csumofstrengthnumeratoruomid,
mct1.csumofstrengthdenomuomid,round(mct1.CSUMOFSTRENGTHNUMFRVALUE0) FrNum,round(mct1.csumofstrengthnumtovalue) ToNum,
round(mct1.CSUMOFSTRENGTHDENOMVALUE0) Denom,(case when ToNum is null then FrNum end) SORR
FROM
MCT_34498 mct1,
principalKey pkey1,
goldenCopy gc
where pkey1.catalogid = gc.catalogid
AND pkey1.productkeyid = mct1.cproductkeyid
AND pkey1.modversion = mct1.cmodversion
AND mct1.cmodversion = gc.version
AND gc.productkey = mct1.cproductkeyid
AND pkey1.active = 'Y';
END;
plsql
kedarrkulkar...
All-Star
34013 Points
5468 Posts
Re: Help to use case statement in oracle
Dec 13, 2011 03:16 AM|LINK
the case when statement looks fine... what is error message u r getting?
KK
Please mark as Answer if post helps in resolving your issue
My Site
poojakulkarn...
Member
24 Points
109 Posts
Re: Help to use case statement in oracle
Dec 13, 2011 04:10 AM|LINK
I have changed the case statement like this, teh error says missing right paranthesis..can you please help me to fix this
case (when (CSUMOFSTRENGTHNUMFRVALUE0 is not null) and
(csumofstrengthnumtovalue is null) and (CSUMOFSTRENGTHDENOMVALUE0 is null)
then (CSUMOFSTRENGTHNUMFRVALUE0))
nirmal.bond
Member
290 Points
92 Posts
Re: Help to use case statement in oracle
Dec 13, 2011 04:14 AM|LINK
Hello,
Refer the links below
http://www.techonthenet.com/oracle/functions/case.php
kedarrkulkar...
All-Star
34013 Points
5468 Posts
Re: Help to use case statement in oracle
Dec 13, 2011 06:23 AM|LINK
try to change it to
case when (CSUMOFSTRENGTHNUMFRVALUE0 is not null) and
(csumofstrengthnumtovalue is null) and (CSUMOFSTRENGTHDENOMVALUE0 is null)
then CSUMOFSTRENGTHNUMFRVALUE0 end
also, there might be syntax error in other parts of query.. try above modification... if u continue to face the issue, post the complete query in question
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site