| Oracle query [message #296562] |
Mon, 28 January 2008 04:24  |
sudhir1981
Messages: 2
Registered: January 2008
Location: PUNE
|
Junior Member |
|
|
i executed the below query:
select sum((DECODE (bls_rpt_compstmt.attribute_name, 'Months on Plan_RPT', bls_rpt_compstmt.compvalue, DECODE (bls_rpt_compstmt.attribute_name, 'Months on Plan', bls_rpt_compstmt.compvalue, 0))) ) As Months_on_Plan
from bls_rpt_compstmt where positionassignmentseq=23139 and PERIODSEQ=160
it displayed the output: Months_on_Plan: 2.
Because it is suming the both attributes 'Months on Plan_RPT','Months on Plan' Compvalues.
But my Requirmrnt is If the table contains attribute name 'Months on Plan_RPT'(First priorty)then it shouold disply only compvalue of 'Months on Plan_RPT' even though it has 'Months on Plan' attribute.Else it should display compvalue of 'Months on Plan(Second Priorty)' .Elase it will disply 0.
Please suggest me how can we write it by using Decode Function or Any other way.
Thanks
Sudhir
|
|
|
|
| Re: Oracle query [message #296564 is a reply to message #296562] |
Mon, 28 January 2008 04:41  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It is unreadable: please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
It is ununderstandable, post a test case.
Regards
Michel
[Updated on: Mon, 28 January 2008 05:38] Report message to a moderator |
|
|
|
| Re: Oracle query [message #296566 is a reply to message #296562] |
Mon, 28 January 2008 04:46 |
 |
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
If I understand your query ,
One way to reach the solution would be ,
1. Select group by sum for 'Months on Plan_RPT' and 'Months on Plan'
2. Use COALESCE Function to get the First Not null value from thye above subquery in the order you need the query (Here 'Months on Plan_RPT' and then 'Months on Plan' )
3. Apply NVL to the Above result.
Rajuvan
[Updated on: Mon, 28 January 2008 04:50] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
