Home » SQL & PL/SQL » SQL & PL/SQL » different results (oracle 10.1.2)
different results [message #348095] Mon, 15 September 2008 10:53 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Dear Gurus
I'm using the follwoing two queries to generate reports in the discoverer plus.My problem is they are giving me different set of results.When the queries are run they are supposed to give the results based on the "from and to" date range entered by the user.I'm following the calendar year quarter and when any one enters the date range it must give the records only for the quarters that are completed irrespective of the date range.My first query gives me the records only for the completed quarters where as the second query gives me the records for the quarters that are in progress as well, can any one please advice me.

Thanks
Query 1:

SELECT CALC_TYPE,
       COUNT(CONTRACT_NUMBER) OVER(PARTITION BY TO_CHAR(SELECTION_DATE,'yy"Q"q') ) CNT,
       CASE 
         WHEN T1.DAYS = NULL
              AND T2.NON_NULL_RECORDS > 4 * (T2.TOTAL_RECORDS) / 5 THEN T2.DAYS
         WHEN T1.DAYS = NULL
              AND T2.NON_NULL_RECORDS < 4 * (T2.TOTAL_RECORDS) / 5 THEN NULL
         ELSE T1.DAYS
       END DAYS,
       CONTRACT_NUMBER,
       NTPACT,
       SELECTION_DATE,
       AMENDREQ,
       RECPROP,
       AMEND_STATUS_CODE,
       AMENDMENT_NUMBER,
       SELECTION_DATE AS PARAM_DATE,
       TO_CHAR(SELECTION_DATE,'yy"Q"q') QUARTER,
       T2.NULL_RECORDS,
       T2.NON_NULL_RECORDS,
       T2.TOTAL_RECORDS
FROM   (SELECT DISTINCT ' Project Specific/Other' CALC_TYPE,
                        TRUNC(NTPACT - SELECTION_DATE) DAYS,
                        CONTRACT_NUMBER,
                        NTPACT,
                        SELECTION_DATE,
                        AMENDREQ,
                        RECPROP,
                        AMEND_STATUS_CODE,
                        AMENDMENT_NUMBER,
                        SELECTION_DATE AS PARAM_DATE
        FROM   CONTRACT_VW2
        WHERE  (CONTRACT_TYPE = 'Project Specific'
                 OR CONTRACT_TYPE = 'Other')
               AND AMENDMENT_NUMBER = 0
               AND AMEND_STATUS_CODE NOT IN (4,
                                             33,
                                             34,
                                             35,
                                             36,
                                             37)) T1,
       (SELECT   TO_CHAR(SELECTION_DATE,'yy"Q"q') YEAR_QTR,
                 COUNT(* ) AS TOTAL_RECORDS,
                 SUM(CASE 
                       WHEN NTPACT IS NULL  THEN 1
                     END) AS NULL_RECORDS,
                 SUM(CASE 
                       WHEN NTPACT IS NOT NULL  THEN 1
                     END) AS NON_NULL_RECORDS,
                 TRUNC(MAX(NTPACT - SELECTION_DATE)) DAYS
        FROM     CONTRACT_VW2
        WHERE    (CONTRACT_TYPE = 'Project Specific'
                   OR CONTRACT_TYPE = 'Other')
                 AND AMENDMENT_NUMBER = 0
                 AND AMEND_STATUS_CODE NOT IN (4,
                                               33,
                                               34,
                                               35,
                                               36,
                                               37)
        GROUP BY TO_CHAR(SELECTION_DATE,'yy"Q"q')) T2
WHERE  TO_CHAR(T1.SELECTION_DATE,'yy"Q"q') = T2.YEAR_QTR


QUERY2:
SELECT   calc_type,
         COUNT(contract_number) OVER(PARTITION BY TO_CHAR(AMENDREQ,'yy"Q"q')) cnt,
         CASE WHEN T1.DAYS=NULL AND T2.NON_NULL_RECORDS > 4*(T2.TOTAL_RECORDS)/5 THEN T2.DAYS
		      WHEN  T1.DAYS=NULL AND T2.NON_NULL_RECORDS < 4*(T2.TOTAL_RECORDS)/5 THEN NULL
              ELSE T1.DAYS
	          END DAYS,  
         CONTRACT_NUMBER,
         NTPACT,
         SELECTION_DATE,
         AMENDREQ,
         RECPROP,
         AMEND_STATUS_CODE,
         AMENDMENT_NUMBER,
         AMENDREQ AS PARAM_DATE,
	     TO_CHAR(AMENDREQ,'yy"Q"q') quarter,
		 T2.NULL_RECORDS,		  
		 T2.NON_NULL_RECORDS,
		 T2.TOTAL_RECORDS	  		  
FROM (SELECT DISTINCT 'Amendments' calc_Type
     ,TRUNC (NTPACT - AMENDREQ )Days
     ,Contract_Number
     ,NTPACT
     ,SELECTION_DATE
     , AMENDREQ
     ,RECPROP
     ,AMEND_STATUS_CODE
     ,AMENDMENT_NUMBER
     ,AMENDREQ AS PARAM_DATE
FROM contract_vw2
     WHERE  AMENDMENT_NUMBER!=0
     AND AMEND_STATUS_CODE!=34
     AND AMENDREQ IS NOT NULL)T1,
	 (SELECT 
	  TO_CHAR(AMENDREQ,'yy"Q"q') Year_Qtr,
      COUNT(*) AS TOTAL_RECORDS,
	  SUM(CASE WHEN NTPACT IS NULL THEN 1 END) AS NULL_RECORDS,
	  SUM(CASE WHEN NTPACT IS NOT NULL THEN 1 END) AS NON_NULL_RECORDS,
	  TRUNC(MAX(NTPACT-AMENDREQ))DAYS
	   FROM contract_vw2
	   WHERE  AMENDMENT_NUMBER!=0
     AND AMEND_STATUS_CODE!=34
     AND AMENDREQ IS NOT NULL
	 GROUP BY TO_CHAR(AMENDREQ,'yy"Q"q'))T2	 
	 WHERE TO_CHAR(T1.AMENDREQ,'yy"Q"q')=T2.Year_Qtr
	 


Thanks
Re: different results [message #348103 is a reply to message #348095] Mon, 15 September 2008 11:18 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aarti81,

I guess you have different conditions supplied for your inline views T1 and T2 in your queries.

Query1
Conditions supplied for T1
aarti81 wrote on Mon, 15 September 2008 21:23

WHERE (CONTRACT_TYPE = 'Project Specific'
OR CONTRACT_TYPE = 'Other')
AND AMENDMENT_NUMBER = 0
AND AMEND_STATUS_CODE NOT IN (4,
33,
34,
35,
36,
37)) T1,



Conditions Supplied for T2
aarti81 wrote on Mon, 15 September 2008 21:23

WHERE (CONTRACT_TYPE = 'Project Specific'
OR CONTRACT_TYPE = 'Other')
AND AMENDMENT_NUMBER = 0
AND AMEND_STATUS_CODE NOT IN (4,
33,
34,
35,
36,
37)
GROUP BY TO_CHAR(SELECTION_DATE,'yy"Q"q')) T2



Query2
Conditions supplied for T1
aarti81 wrote on Mon, 15 September 2008 21:23

WHERE AMENDMENT_NUMBER!=0
AND AMEND_STATUS_CODE!=34
AND AMENDREQ IS NOT NULL)T1



Conditions Supplied for T2
aarti81 wrote on Mon, 15 September 2008 21:23

WHERE AMENDMENT_NUMBER!=0
AND AMEND_STATUS_CODE!=34
AND AMENDREQ IS NOT NULL
GROUP BY TO_CHAR(AMENDREQ,'yy"Q"q'))T2



This might be one of the reasons you are not getting the same output. This is only a guess.

Regards,
Jo
Re: different results [message #348105 is a reply to message #348103] Mon, 15 September 2008 11:26 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thanks Jo
But thats not the reason, its is supposed to be like that.Because they are two sperate queries joined by UNION ALL and my only problem is the first query does show records only for the completed quarters unlike the second query which gives records for the quarter that is in progress as well.
joicejohn wrote on Mon, 15 September 2008 11:18
@aarti81,

I guess you have different conditions supplied for your inline views T1 and T2 in your queries.

Query1
Conditions supplied for T1
aarti81 wrote on Mon, 15 September 2008 21:23

WHERE (CONTRACT_TYPE = 'Project Specific'
OR CONTRACT_TYPE = 'Other')
AND AMENDMENT_NUMBER = 0
AND AMEND_STATUS_CODE NOT IN (4,
33,
34,
35,
36,
37)) T1,



Conditions Supplied for T2
aarti81 wrote on Mon, 15 September 2008 21:23

WHERE (CONTRACT_TYPE = 'Project Specific'
OR CONTRACT_TYPE = 'Other')
AND AMENDMENT_NUMBER = 0
AND AMEND_STATUS_CODE NOT IN (4,
33,
34,
35,
36,
37)
GROUP BY TO_CHAR(SELECTION_DATE,'yy"Q"q')) T2



Query2
Conditions supplied for T1
aarti81 wrote on Mon, 15 September 2008 21:23

WHERE AMENDMENT_NUMBER!=0
AND AMEND_STATUS_CODE!=34
AND AMENDREQ IS NOT NULL)T1



Conditions Supplied for T2
aarti81 wrote on Mon, 15 September 2008 21:23

WHERE AMENDMENT_NUMBER!=0
AND AMEND_STATUS_CODE!=34
AND AMENDREQ IS NOT NULL
GROUP BY TO_CHAR(AMENDREQ,'yy"Q"q'))T2



This might be one of the reasons you are not getting the same output. This is only a guess.

Regards,
Jo


Re: different results [message #348106 is a reply to message #348095] Mon, 15 September 2008 11:29 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
I forgot to add one more question.

Do the columns SELECTION_DATE and AMENDREQ hold same data in a record? Notice the 'Group By' Statements in your inline views too...

Hope these helps.

Regards,
Jo
Re: different results [message #348108 is a reply to message #348106] Mon, 15 September 2008 11:59 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Actually its like below
QUERY 1
UNION ALL
QUERY 2
The first query uses the NTPACT and SELECTION_DATE
where as the second query uses the NTPACT and AMENDREQ. Both these queries have records for 2008 Q3, but in dicovererplus (reporting tool)they must only show records for the completed quarters ie 2008Q1 and 2008Q2. But for some reason the second query is showing records for the 3rd quaretr as well, thats the problem.

Thanks
Re: different results [message #348116 is a reply to message #348108] Mon, 15 September 2008 12:41 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
I can't really help unless you post a test case here.

Can you run the following codes and post the output here:

Query1
  SELECT SELECTION_DATE, AMENDMENT_NUMBER, AMEND_STATUS_CODE, 
  AMENDREQ, TO_CHAR(AMENDREQ,'yy"Q"q'), 
  TO_CHAR(SELECTION_DATE,'yy"Q"q')
  FROM     CONTRACT_VW2
  WHERE    
      (CONTRACT_TYPE = 'Project Specific'
         OR CONTRACT_TYPE = 'Other')
       AND AMENDMENT_NUMBER = 0
       AND AMEND_STATUS_CODE NOT IN (4, 33, 34, 35, 36, 37)
  


Query2
  SELECT SELECTION_DATE,AMENDMENT_NUMBER, AMEND_STATUS_CODE, 
  AMENDREQ, TO_CHAR(AMENDREQ,'yy"Q"q'),
  TO_CHAR(SELECTION_DATE,'yy"Q"q')
  FROM     CONTRACT_VW2
  WHERE  AMENDMENT_NUMBER!=0
        AND AMEND_STATUS_CODE!=34
        AND AMENDREQ IS NOT NULL
  


Regards,
Jo
Re: different results [message #348120 is a reply to message #348116] Mon, 15 September 2008 12:59 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thanks John
Please see the attached results
  • Attachment: query1.txt
    (Size: 50.13KB, Downloaded 444 times)
Re: different results [message #348145 is a reply to message #348120] Mon, 15 September 2008 15:32 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aarti,

Consider this snippet as Query1_Snippet:

  SELECT SELECTION_DATE, AMENDMENT_NUMBER, AMEND_STATUS_CODE, 
  AMENDREQ, TO_CHAR(AMENDREQ,'yy"Q"q'), 
  TO_CHAR(SELECTION_DATE,'yy"Q"q')
  FROM     CONTRACT_VW2
  WHERE    
      (CONTRACT_TYPE = 'Project Specific'
         OR CONTRACT_TYPE = 'Other')
       AND AMENDMENT_NUMBER = 0
       AND AMEND_STATUS_CODE NOT IN (4, 33, 34, 35, 36, 37)
  


According to you,
SELECT * FROM
(SELECT SELECTION_DATE FROM Query1_Snippet)T1,
(SELECT TO_CHAR(SELECTION_DATE,'yy"Q"q') YR_DATE from Query1_Snippet
 GROUP BY TO_CHAR(SELECTION_DATE,'yy"Q"q'))T2
WHERE T1.TO_CHAR(SELECTION_DATE,'yy"Q"q') = T2.YR_DATE

should not return any records where TO_CHAR(SELECTION_DATE,'yy"Q"q') = '08Q3'.

But according to the output you have given I am getting records for 2008 Quarter 3 for Query1.

Try Running the following code:(This is similar to your Query1 with an extra condition for finding records for Q3 of 2008)
   SELECT * FROM
    (SELECT SELECTION_DATE FROM 
       ( SELECT SELECTION_DATE, AMENDMENT_NUMBER, 
          AMEND_STATUS_CODE, 
          AMENDREQ, TO_CHAR(AMENDREQ,'yy"Q"q'), 
          TO_CHAR(SELECTION_DATE,'yy"Q"q')
         FROM     CONTRACT_VW2
         WHERE    
            (CONTRACT_TYPE = 'Project Specific'
               OR CONTRACT_TYPE = 'Other')
            AND AMENDMENT_NUMBER = 0
            AND 
           AMEND_STATUS_CODE NOT IN (4, 33, 34, 35, 36, 37)
        )
      )T1,
    (SELECT TO_CHAR(SELECTION_DATE,'yy"Q"q') YR_DATE from 
       (SELECT SELECTION_DATE, AMENDMENT_NUMBER, 
           AMEND_STATUS_CODE,   AMENDREQ, 
           TO_CHAR(AMENDREQ,'yy"Q"q'), 
           TO_CHAR(SELECTION_DATE,'yy"Q"q')
        FROM     CONTRACT_VW2
        WHERE    
            (CONTRACT_TYPE = 'Project Specific'
                 OR CONTRACT_TYPE = 'Other')
        AND AMENDMENT_NUMBER = 0
        AND AMEND_STATUS_CODE NOT IN (4, 33, 34, 35, 36, 37)
       ) GROUP BY TO_CHAR(SELECTION_DATE,'yy"Q"q')
     )T2
  WHERE 
   T1.TO_CHAR(SELECTION_DATE,'yy"Q"q') = T2.YR_DATE
   AND T2.YR_DATE = '08Q3';
  


Regards,
Jo
Re: different results [message #348187 is a reply to message #348145] Mon, 15 September 2008 23:23 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
As advised,

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Post a test case with the desired and actual results.

Regards,
Jo
Previous Topic: is it the right query !
Next Topic: query how to limit records output
Goto Forum:
  


Current Time: Fri Dec 09 19:32:50 CST 2016

Total time taken to generate the page: 0.08259 seconds