Home » SQL & PL/SQL » SQL & PL/SQL » help with query (oracle 10.1.2)
help with query [message #331227] Wed, 02 July 2008 12:40 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi all

I have the following sequel:

SELECT 'Negotiation' calc_type,
SUM(CASE WHEN days < 30 THEN 1 ELSE 0 END) days
, SUM(CASE WHEN days >= 31 AND days < 60 THEN 1 ELSE 0 END) days2
, SUM(CASE WHEN days >=61 AND days < 90 THEN 1 ELSE 0 END) days3
, SUM(CASE WHEN days >= 91 AND days < 120 THEN 1 ELSE 0 END) days4
, SUM(CASE WHEN days >=120 THEN 1 ELSE 0 END) days5
, COUNT(days) total_rows
FROM
( SELECT DISTINCT 'Negotiation' calc_type
, TRUNC ( SYSDATE - RECPROP ) days
, contract_number
, ROW_NUMBER ( ) OVER ( PARTITION BY contract_number ORDER BY contract_number )
rn
FROM DW_CONTRACT_VW
WHERE amendment_status_code IN ( 3,8,9,10,11,12,13,14,15,16,17,18)
AND contract_type = 'On Call'
AND FIRMSIGN IS NULL )
WHERE rn = 1

here in the calc_type TRUNC(SYSDATE-RECPROP) days
i'm getting the right output now i need to add a new condition wherein if the 'RECPROP' field is NULL, i had to use another field 'AMENDREQ'........can any one please guide me regarding this problem.
Re: help with query [message #331231 is a reply to message #331227] Wed, 02 July 2008 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read 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 (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: help with query [message #331235 is a reply to message #331227] Wed, 02 July 2008 13:07 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
NVL
Previous Topic: Select private procedures from data dictionary
Next Topic: Capturing SQL statements to a log table.
Goto Forum:
  


Current Time: Thu Dec 08 16:32:14 CST 2016

Total time taken to generate the page: 0.11364 seconds