Home » SQL & PL/SQL » SQL & PL/SQL » difference in records [merged with] OR problem
difference in records [merged with] OR problem [message #332408] Tue, 08 July 2008 08:20 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
Please see the following 2 queries:

select distinct contract_number
from dw_contract_vw
where contract_type!='On Call'
and amendment_status_code in (3,8,9,10,11,12,13,14,15,16,17,18)
and firmsign is null
or
contract_type ='On Call'
and amendment_status_code in (1,5,6,7,8,9,10,11,12,13,14,15,16,17,18)
and firmsign is null

FOR THIS QUERY I'M GETTING 45 RECORDS, BUT WHEN I MAKE IT A PART OF THE MAIN QUERY I'M GETTING JUST 19 RECORDS,ANY IDEAS WHY,PLS ANY ADVICE IS GREATLY APPRECIATED.

MAIN QUERY:
SELECT 'scoping' 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 'scoping' calc_type
, TRUNC ( SYSDATE - selection_date ) days
, contract_number, worktype_code
, ROW_NUMBER ( ) OVER ( PARTITION BY contract_number ORDER BY contract_number )
rn
FROM DW_CONTRACT_VW
WHERE amendment_number = 0
AND amendment_status_code IN ( 1, 5, 6, 7 )
AND contract_type != 'On Call' )
WHERE rn = 1
UNION ALL
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 - NVL(RECPROP,AMENDREQ)) 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
or
contract_type ='On Call'
and amendment_status_code in (1,5,6,7,8,9,10,11,12,13,14,15,16,17,18)
and firmsign is null)
WHERE rn = 1
UNION ALL
SELECT 'Signature' 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 'Signature' calc_type
, TRUNC ( SYSDATE - FIRMSIGN ) days
, contract_number
, ROW_NUMBER ( ) OVER ( PARTITION BY contract_number ORDER BY contract_number )
rn
FROM DW_CONTRACT_VW
WHERE amendment_status_code IN (12,13,16,17,19,20,21,22,23,24,25,26,27,28,29,30,31)
AND FIRMSIGN IS NOT NULL
AND NTP_ACT IS NULL)
WHERE rn = 1
Thanks




Re: OR Problem [message #332413 is a reply to message #332408] Tue, 08 July 2008 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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: OR Problem [message #332425 is a reply to message #332408] Tue, 08 July 2008 08:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty sure you've got your query wrong. I think you need to bracket this query:
select distinct contract_number
from dw_contract_vw
where (contract_type!='On Call'
   and amendment_status_code in (3,8,9,10,11,12,13,14,15,16,17,18)
   and firmsign is null)
or    (contract_type ='On Call'
   and amendment_status_code in( 1,5,6,7,8,9,10,11,12,13,14,15,16,17,18)
   and firmsign is null)


The reason for the difference in rows is that there is a '!' missing from the Main query that is present in the sample you posted.
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
or
contract_type ='On Call'
and amendment_status_code in (1,5,6,7,8,9,10,11,12,13,14,15,16,17,18)
and firmsign is null)


Please format your posts next time - that was pretty hard to read.
differencce in records [message #332503 is a reply to message #332408] Tue, 08 July 2008 14:35 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi all
I have a subquery, which gives me 46 records when i execute it.When i run it as a part of the main query, it gives 38 records, pls see the sub and main query below.Any ideas what might be wrong?

SUB QUERY :
SELECT DISTINCT 'Negotiation' calc_type

, TRUNC(SYSDATE - NVL(RECPROP,AMENDREQ)) days

, TRUNC(SYSDATE - (SELECTION_DATE))

, 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)

or

(contract_type ='On Call'

and amendment_status_code in (1,5,6,7,8,9,10,11,12,13,14,15,16,17,18)

and firmsign is null))

MAIN QUERY:
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 - NVL(RECPROP,AMENDREQ)) days

, contract_number

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)
or
(contract_type ='On Call'
and amendment_status_code in (1,5,6,7,8,9,10,11,12,13,14,15,16,17,18)
and firmsign is null))

Thanks
Re: differencce in records [message #332504 is a reply to message #332503] Tue, 08 July 2008 14:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

It is not clear, at least to this reader, exactly what you are asking or expecting as an answer.

How would an independent observer conclude this issue has been successfully resolved?
Re: differencce in records [message #332505 is a reply to message #332504] Tue, 08 July 2008 14:45 Go to previous message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
sorry will reformat
Previous Topic: How to convert the TimeStamp from EST to CET??
Next Topic: Efficient SQL
Goto Forum:
  


Current Time: Sat Dec 10 16:46:31 CST 2016

Total time taken to generate the page: 0.19404 seconds