Home » SQL & PL/SQL » SQL & PL/SQL » query help (oracle 10.1.2)
query help [message #343151] Tue, 26 August 2008 08:30 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
First of all sorry to all of you for not formatting my code.Please see the below code that i have based on certain conditions.

SELECT calc_Type,
       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
FROM   (SELECT DISTINCT '  On-Call  Unit Price' 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 = 'On Call'
               AND Contract_comp_Method = 'Unit Price'
               AND Amend_Status_Code NOT IN (4,
                                             33,
                                             34,
                                             35,
                                             36,
                                             37)
               AND Amendment_Number = 0)
UNION ALL
SELECT calc_Type,
       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
FROM   (SELECT DISTINCT ' On-Call Negotiated Rate' 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 = 'On Call'
               AND Contract_comp_Method != 'Unit Price'
               AND Amend_Status_Code NOT IN (4,
                                             33,
                                             34,
                                             35,
                                             36,
                                             37)
               AND Amendment_Number = 0)
UNION ALL
SELECT calc_Type,
       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
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))
UNION ALL
SELECT calc_Type,
       Contract_Number,
       ntPact,
       Selection_Date,
       Amendreq,
       recProp,
       Amend_Status_Code,
       Amendment_Number,
       Amendreq AS Param_Date,
       To_char(Amendreq,'yy"Q"q') Quarter
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 )]


My main problem is about the three fields "DAYS=NTPACT-AMENDREQ"
Where both the NTPACT AND AMENDREQ are date fields.There are some cases where the NTPACT fields can be NULL some times as a result from the above calculation "DAYS" will be NULL.My requirement is for example if i have 20 records and out of which more than half, in this example 11 of the NTPACT fields are NOT NULL , then the "DAYS" field which will be having the NULL values for the remaining 9 records must be filled with the MAX(DAYS) value if less than half the records, in this case 9 are NOT NULL then leave the 11 DAYS fields as it is , ie NULL.
Hope my explanation is ok.Any kind of advice will be of great help.

Thank You and sorry agin for not formatting the code before.

[Updated on: Tue, 26 August 2008 08:34]

Report message to a moderator

Re: query help [message #343158 is a reply to message #343151] Tue, 26 August 2008 08:40 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for formating the code. Is it possible for you to provide the test case?

Regards,
Oli

[Updated on: Tue, 26 August 2008 08:47]

Report message to a moderator

Re: query help [message #343171 is a reply to message #343158] Tue, 26 August 2008 09:19 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Please see the attachement.
Re: query help [message #343175 is a reply to message #343171] Tue, 26 August 2008 09:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What attachment?
Re: query help [message #343177 is a reply to message #343158] Tue, 26 August 2008 09:31 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
The query is about four different groups:
On call unit price
on call negotiated rate
project specific/other
amendments
The first three are based on the selection date and the NTP.The last one is based on the NTP and AMENDREQ. Irrespective of the SELECTION_DATE AND THE AMENDREQ, the main role is the NTP since;
DAYS=NTPACT=SELECTION_DATE
DAYS=NTPACT-AMENDREQ
So whenever the NTPACT IS NULL DAYS WILL BE NULL as well.
The requirement is for example out of 20 records if the NTPACT in NOT NULL in more than half the records, in this case 11 then the DAYS field for the corresponding 9 NULL fields must be replaced by the MAX(DAYS)of the remaining 11 fields and if the NTPACT records are NOT NULL for less than half in this case 9, leave the DAYS fields for the remaining 11 as NULL.

Thanks
Re: query help [message #343178 is a reply to message #343177] Tue, 26 August 2008 09:40 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Was asked to provide the DDL for the tables you are unsing in this query.Also ,the insert sample script if possible.
Its well understood that you might be busy working on the problem but it really helps to make other understand.

- We dont know the structure data.How it is being oraganised.
- The contraints you are using


something like below
/* DDL for test */ 
create table test (t varchar2(1))

/* DML for test,INSERT SQL */ 
insert into test values('A')
insert into test values ('B')
..






Reggards,
Oli
Re: query help [message #343181 is a reply to message #343178] Tue, 26 August 2008 09:49 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Sorry for not explaining the question. Actually i'm using this query to create a report using the discoverer plus and this query runs directlty across a live database and i'm using the VIEW provided by the DBA and please correct me if i'm wrong, i think its a logical thing and not a data thing as i'm looking what kind of logic to implement it.Please correct me if i'm wrong.

Thanks
Re: query help [message #343219 is a reply to message #343181] Tue, 26 August 2008 12:05 Go to previous messageGo to next message
prtz
Messages: 11
Registered: January 2008
Junior Member
Quote:
...please correct me if i'm wrong, i think its a logical thing and not a data thing as i'm looking what kind of logic to implement it.


While I do agree that this is a logical "thing", I disagree, vehemently, that this is NOT a data "thing".

What the other posters are telling you is that it's difficult to get the general picture of what you are trying to accomplish by just gawking at a 117 line SQL statement - a fact you can attest to yourself if you've been lurking around such forums for some time.

Many a time, it's just a silly mistake in a huge script, which can be pointed out easily. But your case is not the same. We would like to reproduce/replicate your resultset in our systems, and that is why we are asking for the create table/insert data scripts.

- Why do we want the datatypes ?
Because the query may succeed or fail or show different results depending on the datatype.

- Why do we want to see the result ?
Because it gives us something to start with. When we know what we have in front of us, it's easier to change it to get the desired output. (And really, working with smaller models before going on to something bigger, is not so uncommon in other fields of endeavor, if you think about it.)

- What can you do about it ?
For a start, you can spare us and yourself a great deal of complexity if you just concentrate on the *exact* sample you can work on (your testcase) and provide us.

Think we can do away with most of the columns ? Then send just the ones that matter in your testcase.

Think we can ignore all those "union all"s and consider your query as just one simple SELECT statement ? Then mention that in your testcase.

Think you can just send us a simple table with, say, 10 records, and a SELECT statement and tell us -

"this is what I see here,
but I want to see this here,
how do I go about it ?"

If yes, then do it, for that's the best chance you have of getting your question answered.

Quote:
...Please correct me if i'm wrong.


Sure, anytime.

prtz

[Updated on: Tue, 26 August 2008 12:06]

Report message to a moderator

Re: query help [message #343302 is a reply to message #343219] Tue, 26 August 2008 20:34 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hi Aarti81

I agree with the response posts on content level- believe me I wish I saw your explanation before going thru that entire SELECT and not after!

But I need to work on brevity in my own communications as well Smile ,
so let me give this a shot

Anyway I think I understand your goal :

(1) if the Days calculation field is null for less than half of the records then you want to populate that
field with the MAX(DAYS) value from the other records.

(2) If the Days calculation field is null for more than (or equal to?) half of the records then leave the field as is.

(3) It is the Ntpact date field that can contain nulls

How about getting those counts upfront in some sub selects (or in a preliminary WITH clause), then where you do the calculation
do a DECODE on NtPact to determine if it is null or not.
-If it is not null then do the calculation as is
-If it is null then use the max days field from the new result set

I'm not going to resift through your statement for all the table names, so lets simplify to the bare bone basics with this example:

T_TBL
----------
T_ID INTEGER,
T_Date1 DATE, --this is the field that can be null
T_Date2 DATE,

We'll work with this Select:

SELECT T_ID,
       TRUNC(T_Date1 - T_Date2) Days
From T_TBL;

Returns:

T_ID    Days
-----   ------
1        {NULL}
2        20
3         5


So based on your rules, you want to replace the {NULL} in record 1 with the max days of 20.

Here's the sub-select method:

SELECT T.T_ID,
       DECODE(MN.MORE_NULLS,1,
              TRUNC(T.T_Date1 - T.T_Date2),--if more null recs than 
                                           --non-null, then leave
                                           --the calculation as is
              MD.MAXDAYS) AS DAYS --other wise use the MAX days field
FROM T_TBL T,
 
(SELECT MAX(TRUNC(T_DATE1 - T_DATE2)) as MAXDAYS
FROM  T_TBL;
) T_MD,  
 
(SELECT 1 AS MORE_NULLS  FROM
      (SELECT COUNT(*) as NCNT
       FROM  T_TBL 
       WHERE T_DATE1 is NULL;
        ) N, 
      (SELECT COUNT(*) as NNCNT
       FROM  T_TBL 
       WHERE T_DATE1 is NOT NULL;
       ) NN
WHERE N.NCNT > NN.NNCNT;
) MN



I apologize for being a bit weary here from a very long
day; the above will work but it can be approached more
efficiently. This was off the top of my head as I am not
at work with Oracle access. I may have fat-fingered some syntax, but the concept should be clear.

Hope this helps.
Regards,
Harry




Re: query help [message #343574 is a reply to message #343302] Wed, 27 August 2008 09:57 Go to previous message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thank you all , but i came up with some thing that works real fine .

SELECT CALC_TYPE,
       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
       ) T1, 
	   (SELECT 
       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 FROM CONTRACT_VW2) T2

	
harrysmall3 wrote on Tue, 26 August 2008 20:34
Hi Aarti81

I agree with the response posts on content level- believe me I wish I saw your explanation before going thru that entire SELECT and not after!

But I need to work on brevity in my own communications as well Smile ,
so let me give this a shot

Anyway I think I understand your goal :

(1) if the Days calculation field is null for less than half of the records then you want to populate that
field with the MAX(DAYS) value from the other records.

(2) If the Days calculation field is null for more than (or equal to?) half of the records then leave the field as is.

(3) It is the Ntpact date field that can contain nulls

How about getting those counts upfront in some sub selects (or in a preliminary WITH clause), then where you do the calculation
do a DECODE on NtPact to determine if it is null or not.
-If it is not null then do the calculation as is
-If it is null then use the max days field from the new result set

I'm not going to resift through your statement for all the table names, so lets simplify to the bare bone basics with this example:

T_TBL
----------
T_ID INTEGER,
T_Date1 DATE, --this is the field that can be null
T_Date2 DATE,

We'll work with this Select:

SELECT T_ID,
       TRUNC(T_Date1 - T_Date2) Days
From T_TBL;

Returns:

T_ID    Days
-----   ------
1        {NULL}
2        20
3         5


So based on your rules, you want to replace the {NULL} in record 1 with the max days of 20.

Here's the sub-select method:

SELECT T.T_ID,
       DECODE(MN.MORE_NULLS,1,
              TRUNC(T.T_Date1 - T.T_Date2),--if more null recs than 
                                           --non-null, then leave
                                           --the calculation as is
              MD.MAXDAYS) AS DAYS --other wise use the MAX days field
FROM T_TBL T,
 
(SELECT MAX(TRUNC(T_DATE1 - T_DATE2)) as MAXDAYS
FROM  T_TBL;
) T_MD,  
 
(SELECT 1 AS MORE_NULLS  FROM
      (SELECT COUNT(*) as NCNT
       FROM  T_TBL 
       WHERE T_DATE1 is NULL;
        ) N, 
      (SELECT COUNT(*) as NNCNT
       FROM  T_TBL 
       WHERE T_DATE1 is NOT NULL;
       ) NN
WHERE N.NCNT > NN.NNCNT;
) MN



I apologize for being a bit weary here from a very long
day; the above will work but it can be approached more
efficiently. This was off the top of my head as I am not
at work with Oracle access. I may have fat-fingered some syntax, but the concept should be clear.

Hope this helps.
Regards,
Harry






Previous Topic: INSERT TIME
Next Topic: help whit RECORD.
Goto Forum:
  


Current Time: Fri Dec 09 13:38:57 CST 2016

Total time taken to generate the page: 0.06392 seconds