Home » SQL & PL/SQL » SQL & PL/SQL » query help (oracle 10.1.2)
query help [message #342172] Thu, 21 August 2008 11:03 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
I have the follwoing query. The purpose of this query is to give me the DAYS which is in this case {NTPACT-AMENDREQ} which are both date fields, some times i'm getting null as NTPACT has some NULL values, so i added CASE statement to get rid of the NULL values in the DAYS field, my question now is instead of giving MAX(DAYS) is there any way where i can get the MAX(DAYS) Within a QUARTER RANGE?
For example if i enter date range from jan-1-2008 till june-30-2008, it must give me the max days in the NULL DAYS fields within this date range. Any help is greatly appreciated.
SELECT CALC_TYPE,
case when ntpact is null then max(days) over (order by null) else days end DAYS, -- this is different
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
)


Thanks
Re: query help [message #342173 is a reply to message #342172] Thu, 21 August 2008 11:06 Go to previous messageGo to next message
BlackSwan
Messages: 25041
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

Since chose to ignore Posting Guideline, I choose to not answer.

You're on Your Own (YOYO)!
Re: query help [message #342177 is a reply to message #342173] Thu, 21 August 2008 11:16 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thanks
Sorry about that, anyways got the solution on my own Razz
anacedent wrote on Thu, 21 August 2008 11:06
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Since chose to ignore Posting Guideline, I choose to not answer.

You're on Your Own (YOYO)!

Re: query help [message #342180 is a reply to message #342177] Thu, 21 August 2008 11:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
aarti81 wrote on Thu, 21 August 2008 11:16
Thanks
Sorry about that, anyways got the solution on my own Razz
anacedent wrote on Thu, 21 August 2008 11:06
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Since chose to ignore Posting Guideline, I choose to not answer.

You're on Your Own (YOYO)!




Still I would say,
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above.


It really helps to understand well formatted code and got a quick response.Hope that next time you will follow the guidelines.

It will be good for other readers if you post your query and how you modified.


Regards,
Oli
Re: query help [message #342182 is a reply to message #342177] Thu, 21 August 2008 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sorry about that, anyways got the solution on my own

Does this mean that is useless to answer your questions? Razz

Regards
Michel
Re: query help [message #342191 is a reply to message #342182] Thu, 21 August 2008 11:42 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Would be happy if you post what /how you modified.

Trust me, this is the place where some experienced people to guide you if you are wrong and show you the correct path.
May be you got a better solution.But you dont care.Right?
Share what you have done so far it will not only help others but you also.


Well said,
He who asks a question may appear to be a fool, he who does not ask will remain a fool forever...
Re: query help [message #342193 is a reply to message #342177] Thu, 21 August 2008 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
aarti81 wrote on Thu, 21 August 2008 09:16
Thanks
Sorry about that, anyways got the solution on my own Razz


& you continue to flaunt Posting Guidelines by failing to post the solution!
Re: query help [message #342202 is a reply to message #342177] Thu, 21 August 2008 12:16 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Again ,

Please post the query you got so that it might be usefull for others ( by using Formatti ng tool )


Thumbs Up
Rajuvan.

[Updated on: Thu, 21 August 2008 12:16]

Report message to a moderator

Previous Topic: Query procedure last executed
Next Topic: Need Logic
Goto Forum:
  


Current Time: Tue Dec 06 11:58:14 CST 2016

Total time taken to generate the page: 0.11460 seconds