Home » SQL & PL/SQL » SQL & PL/SQL » help with query (merged)
help with query (merged) [message #356364] Thu, 30 October 2008 09:03 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
I need the following fields as the output.
CONTRACT_TYPE,LETTING_STATUS,AMENDMENT_NUMBER,LETTING_DATE.
My problem is based on the requirements given by the user:
CONTRACT_TYPE is of 3 kinds:'On Call', 'Project Specific' and 'other'.
The user wants all the contract_types BUT he has the following conditions:
WHEN  CONTRACT_TYPE='Project Specific'
				   THEN NTPACT IS NOT NULL
				   THEN LETTING_STATUS!='C'
				   THEN AMENDMENT_NUMBER=0				  			   
				   or	
				   contract_type='Project Specific'
				    THEN NTPACT IS NOT NULL
				   THEN LETTING_DATE>sysdate-365
				   THEN LETTING_STATUS='C'
				   THEN AMENDMENT_NUMBER=0

Please any idea as to how to approach this will be greatly appreciated.

Thanks
Re: help with query [message #356369 is a reply to message #356364] Thu, 30 October 2008 09:28 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
Why two different sets for the same CONTRACT_TYPE??

Can't it be like this

WHEN  CONTRACT_TYPE='Project Specific'
				   THEN NTPACT IS NOT NULL
				   AND LETTING_STATUS!='C'
				   AND AMENDMENT_NUMBER=0


Re: help with query [message #356370 is a reply to message #356369] Thu, 30 October 2008 09:32 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Its not the same one set is when the letting status is not equal to 'C' and the other when the letting status is equal to 'C' then the letting date must be >sysdate-365.
Thanks
fairgame wrote on Thu, 30 October 2008 09:28
Why two different sets for the same CONTRACT_TYPE??

Can't it be like this

WHEN  CONTRACT_TYPE='Project Specific'
				   THEN NTPACT IS NOT NULL
				   AND LETTING_STATUS!='C'
				   AND AMENDMENT_NUMBER=0




Re: help with query [message #356374 is a reply to message #356364] Thu, 30 October 2008 09:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about you explaining your requirement in plain simple english along with some create table, insert statements and expected output. To be honest I am not able to understand what you are trying to achieve ?

Regards

Raj
Re: help with query [message #356380 is a reply to message #356374] Thu, 30 October 2008 09:52 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Raj
I'm running a query against an oracle db using the code. The field contract_type consists of 3 different kinds of contratc_types : oncall,project specific,other. The required output is it should display all the contratc types but when the contract type is project specific it must satisfy the following conditions :

CONTRACT_TYPE='Project Specific'
				   AND NTPACT IS NOT NULL
				   AND LETTING_STATUS!='C'
				   AND AMENDMENT_NUMBER=0				  			   
				   or	
				   contract_type='Project Specific'
				   AND NTPACT IS NOT NULL
				   AND LETTING_DATE>sysdate-365
				   AND LETTING_STATUS='C'
				   AND AMENDMENT_NUMBER=0
				  
				   )


Thanks
Re: help with query [message #356396 is a reply to message #356380] Thu, 30 October 2008 11:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Is it not simply
where
contract_type = 'oncall' or contract_type = 'other' or
(
  CONTRACT_TYPE='Project Specific'
  AND NTPACT IS NOT NULL
  AND AMENDMENT_NUMBER=0
  AND ( (letting_status != 'C') or 
        (letting_status = 'C' and letting_Date > sysdate - 365)
      )
)


Regards

Raj
Re: help with query [message #356441 is a reply to message #356396] Thu, 30 October 2008 13:34 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thank You RajaRam that was of great help but when i tried using the 'lettingdate<sysdate-365' in the CASE statement its giving me a missing expression hour. Any idea why?
S.Rajaram wrote on Thu, 30 October 2008 11:06
Is it not simply
where
contract_type = 'oncall' or contract_type = 'other' or
(
  CONTRACT_TYPE='Project Specific'
  AND NTPACT IS NOT NULL
  AND AMENDMENT_NUMBER=0
  AND ( (letting_status != 'C') or 
        (letting_status = 'C' and letting_Date > sysdate - 365)
      )
)


Regards

Raj

Re: help with query [message #356587 is a reply to message #356441] Fri, 31 October 2008 05:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't know how many times we need to say this. Copy & paste the actual error from your sql*plus session.

Regards

Raj
sql help please [message #358795 is a reply to message #356364] Wed, 12 November 2008 09:07 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All when i try to execute the below query i'm getting the following error and i'm not sure what it was.
Error:ORA:00905 :missing keyword

SELECT calc_type, contract_number, contract_type, des_number, contract_descr,
       renewable, firm_name, selection_date, letting_date,
       final_evaluation_date, amend_status_code, letting_status,
       office_district_descr, ntpact, contdist, action_amount,
       amendment_number, number_years_contract, expiration,
       final_evaluation_status
  FROM (SELECT DISTINCT ' Active' calc_type, contract_number, contract_type,
                        des_number, contract_descr, renewable, firm_name,
                        selection_date, letting_date, final_evaluation_date,
                        amend_status_code, letting_status,
                        office_district_descr, ntpact, contdist,
                        action_amount, amendment_number,
                        number_years_contract, expiration,
                        final_evaluation_status
                   FROM contract_vw2
                  WHERE     ntpact IS NOT NULL
                        AND amend_status_code NOT IN (33, 34, 35, 36, 37, 39)),
						(select case when contract_type='Project Specific' THEN LETTING_STATUS[B]!='C' ELSE NULL [/B]END
						 CASE WHEN CONTRACT_TYPE'=Project Specific' THEN (LETTING_STATUS='C' AND LETTING_DATE > (SYSDATE-365)) ELSE NULL END
                        FROM CONTRACT_VW2
						 )
Re: sql help please [message #358805 is a reply to message #358795] Wed, 12 November 2008 09:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
What happened to this ?

http://www.orafaq.com/forum/t/127617/94420/

Regards

Raj
Re: sql help please [message #358806 is a reply to message #358805] Wed, 12 November 2008 09:43 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
???????? Sorry didnt understand what you were trying to say.
S.Rajaram wrote on Wed, 12 November 2008 09:37
What happened to this ?

http://www.orafaq.com/forum/t/127617/94420/

Regards

Raj

Re: sql help please [message #358809 is a reply to message #358806] Wed, 12 November 2008 09:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Huh?

case 
  when contract_type='Project Specific' 
    THEN LETTING_STATUS != 'C' 
   ELSE NULL


What do you think this should do?

Set LETTING_STATUS to something random, just not to 'C' ?
Re: sql help please [message #358816 is a reply to message #358806] Wed, 12 November 2008 10:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Apologies. It should be this one.

http://www.orafaq.com/forum/m/356441/94420/#msg_356441

I thought we discussed the same issue on the above link. If that being the case why did you choose to open a new topic.

Regards

Raj
Re: sql help please [message #358820 is a reply to message #358795] Wed, 12 November 2008 10:15 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aarti81,

In addition to Thomas's comment about
case 
  when contract_type='Project Specific' 
    THEN LETTING_STATUS != 'C' 
   ELSE NULL


Your CASE Statement is wrong.
The CASE Statement doesn't support a conditional statement in the THEN part.
SQL> WITH test_tab AS
  2       (SELECT 'A' col_1, 1 col_b
  3          FROM DUAL)
  4  SELECT CASE
  5            WHEN col_1 = 'A'
  6               THEN col_b = 100
  7            ELSE NULL
  8         END case_1, col_1
  9    FROM test_tab;
             THEN col_b = 100
                        *
ERROR at line 6:
ORA-00905: missing keyword

SQL> WITH test_tab AS
  2       (SELECT 'A' col_1, 1 col_b
  3          FROM DUAL)
  4  SELECT CASE
  5            WHEN col_1 = 'A'
  6               THEN  col_b > 100
  7            ELSE NULL
  8         END case_1, col_1
  9    FROM test_tab;
             THEN  col_b > 100
                         *
ERROR at line 6:
ORA-00905: missing keyword

SQL> WITH test_tab AS
  2       (SELECT 'A' col_1, 1 col_b
  3          FROM DUAL)
  4  SELECT CASE
  5            WHEN col_1 = 'A'
  6               THEN  100
  7            ELSE NULL
  8         END case_1, col_1
  9    FROM test_tab;

    CASE_1 C
---------- -
       100 A

1 row selected.


Moreover, you should add a comma to differenciate the two case statements in your query:
select case 
when contract_type='Project Specific' 
THEN LETTING_STATUS[B]!='C' ELSE NULL [/B]END
CASE WHEN CONTRACT_TYPE'=Project Specific' THEN (LETTING_STATUS='C' AND 
LETTING_DATE > (SYSDATE-365)) ELSE NULL END
         FROM CONTRACT_VW2


Hope this helps.

Regards,
Jo
Re: sql help please [message #358856 is a reply to message #358820] Wed, 12 November 2008 12:52 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
It seems this person has been working on the same query since at least June without learning proper SQL syntax.
Previous Topic: Using Fields Again in the Same Script
Next Topic: Checklist post upgrade from Oracle 9i
Goto Forum:
  


Current Time: Sat Dec 10 01:14:12 CST 2016

Total time taken to generate the page: 0.10691 seconds