Oracle CASE statement help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Oracle CASE statement help

Author

Topic

nimo
Starting Member

2 Posts

Posted - 2009-04-21 : 12:59:40

I am not seeing what my problem is on the following query, i hope one of you can spot it, or provide other advise.

Thanks

select drug_dmnsn.ndc_cd,
phrmcy_clm_fact.last_srvc_dt,
phrmcy_clndr_time_dmnsn.clndr_dt,
CASE AWP_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.crnt_awp_efctv_dt THEN drug_dmnsn.crnt_apw_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_1_efctv_dt AND < drug_dmnsn.crnt_awp_efctv_dt THEN drug_dmnsn.awp_1_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_2_efctv_dt AND < drug_dmnsn.awp_1_efctv_dt THEN drug_dmnsn.awp_2_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_3_efctv_dt AND < drug_dmnsn.awp_2_efctv_dt THEN drug_dmnsn.awp_3_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_4_efctv_dt AND < drug_dmnsn.awp_3_efctv_dt THEN drug_dmnsn.awp_4_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_5_efctv_dt AND < drug_dmnsn.awp_4_efctv_dt THEN drug_dmnsn.awp_5_amt
ELSE 'unknown'
END as AWP
from drug_dmnsn,
phrmcy_clm_fact,
phrmcy_clndr_time_dmnsn
where drug_dmnsn.drug_key = phrmcy_clm_fact.drug_key AND
phrmcy_clm_fact.first_srvc_time_key = phrmcy_clndr_time_dmnsn.clndr_time_key AND
phrmcy_clm_fact = to_date('2009-03-01','YYYY-MM-DD') AND
drug_dmnsn.ndc_cd = 00591038701;

Thanks!

nimo
Starting Member

2 Posts

Posted - 2009-04-21 : 13:23:31

Here is the message i am getting.

Error at Command Line:5 Column:47
Error report:
SQL Error: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-21 : 13:23:39

But this is a sql server forum :(

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-21 : 13:28:23

Regardless, try changint the case to

CASE
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.crnt_awp_efctv_dt THEN drug_dmnsn.crnt_apw_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_1_efctv_dt AND < drug_dmnsn.crnt_awp_efctv_dt THEN drug_dmnsn.awp_1_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_2_efctv_dt AND < drug_dmnsn.awp_1_efctv_dt THEN drug_dmnsn.awp_2_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_3_efctv_dt AND < drug_dmnsn.awp_2_efctv_dt THEN drug_dmnsn.awp_3_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_4_efctv_dt AND < drug_dmnsn.awp_3_efctv_dt THEN drug_dmnsn.awp_4_amt
WHEN phrmcy_clndr_time_dmnsn.clndr_dt > drug_dmnsn.awp_5_efctv_dt AND < drug_dmnsn.awp_4_efctv_dt THEN drug_dmnsn.awp_5_amt
ELSE 'unknown'
END as AWP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources