help with query (merged) [message #356364] |
Thu, 30 October 2008 09:03  |
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   |
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   |
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.
Thanksfairgame 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   |
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   |
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   |
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   |
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   |
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   |
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 #358809 is a reply to message #358806] |
Wed, 12 November 2008 09:49   |
ThomasG
Messages: 3212 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 #358820 is a reply to message #358795] |
Wed, 12 November 2008 10:15   |
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
|
|
|
|