Home » SQL & PL/SQL » SQL & PL/SQL » Oracle query
Oracle query [message #296562] Mon, 28 January 2008 04:24 Go to next message
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.


Re: Oracle query [message #296564 is a reply to message #296562] Mon, 28 January 2008 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
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.


[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 Go to previous message
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.

Thumbs Up

[Updated on: Mon, 28 January 2008 04:50]

Report message to a moderator

Previous Topic: Date
Next Topic: Exception Handling
Goto Forum:

Current Time: Sat Aug 19 00:44:51 CDT 2017

Total time taken to generate the page: 0.26972 seconds