Home » SQL & PL/SQL » SQL & PL/SQL » SQL help (oracle 10.1.2)
SQL help [message #342980] Mon, 25 August 2008 13:28 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
I have the following query. Please help me , i have 3 fields
DAYS NTPACT AMENDREQ
DAYS = NTPACT-AMENDREQ
both NTPACT AND AMENDREQ are date fields.The problem is in some cases NTPACT can be NULL which will have DAYS AS null becuase DAYS=NTPACT- AMENDREQ.My requirement is to see how many records OF NTPACT are NULL. If more than half of the total records of the NTPACT are NOT NULL then the DAYS field must be filled by the MAX value in the DAYS field else leave the DAYS field as it is. Any kind of help will be great.


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)


thanks
Re: SQL help [message #342982 is a reply to message #342980] Mon, 25 August 2008 13:37 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
99 posts and you are still incapable of, or unwilling to post correct (as per the forum guidelines). Why?

[Edit]
I have just read through a few of your previous posts. Why do you insist on behaving in this way? You have been asked multiple time to format your post, you refuse to do this.
You were asked to post the solution to the problem that you ended up 'solving yourself' (I'm guessing some poor sap on another site wrote the sql for you) you refuse to post that.
Several times, people have gone to the effort to supply you an answer and you do not even have the courtesy to acknowledge their reply and inform them (and others) as to whether your question was answered.
Good luck with getting an answer.

[Updated on: Mon, 25 August 2008 13:50]

Report message to a moderator

Re: SQL help [message #342984 is a reply to message #342980] Mon, 25 August 2008 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You choose to not follow Posting Guidelines.
We choose to not provide assistance.

You're On Your Own (YOYO)!
Re: SQL help [message #342987 is a reply to message #342982] Mon, 25 August 2008 14:37 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
I'm really very sorry but where should i format and how do i post the results, sorry once again and will format from now onwards.

Thank you

pablolee wrote on Mon, 25 August 2008 13:37
99 posts and you are still incapable of, or unwilling to post correct (as per the forum guidelines). Why?

[Edit]
I have just read through a few of your previous posts. Why do you insist on behaving in this way? You have been asked multiple time to format your post, you refuse to do this.
You were asked to post the solution to the problem that you ended up 'solving yourself' (I'm guessing some poor sap on another site wrote the sql for you) you refuse to post that.
Several times, people have gone to the effort to supply you an answer and you do not even have the courtesy to acknowledge their reply and inform them (and others) as to whether your question was answered.
Good luck with getting an answer.

Re: SQL help [message #342988 is a reply to message #342984] Mon, 25 August 2008 14:39 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
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 )


anacedent wrote on Mon, 25 August 2008 13:56
You choose to not follow Posting Guidelines.
We choose to not provide assistance.

You're On Your Own (YOYO)!

[Updated on: Mon, 25 August 2008 14:39]

Report message to a moderator

Re: SQL help [message #342992 is a reply to message #342988] Mon, 25 August 2008 15:19 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
http://www.orafaq.com/forum/t/88153/0/ as has been given many times.
Previous Topic: SQL Query Issue
Next Topic: previously word
Goto Forum:
  


Current Time: Sat Dec 10 01:15:52 CST 2016

Total time taken to generate the page: 0.10618 seconds