Home » SQL & PL/SQL » SQL & PL/SQL » Compute Case Statement (DB 8.1.7)
Compute Case Statement [message #393951] Wed, 25 March 2009 03:08 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I wish to compute my statement as below:

I have three date in my entire SQL statement and I wish to achieve condition as below:

If Start_Date <> Null Then Start_Date
Else if End_Date <> Null Then End_Date + et_days (Is an extension days)
Else if Revised_Date <> Null Then Revised_Date

From the three dates, only one date is return as "Final_Date" and it is less_date Sysdate. My SQL statement is as below:

select p.end_date,(p.start_date + p.et_days) as mydate,

(CASE
WHEN p.start_date is not null THEN p.start_date
WHEN p.end_date is not null THEN (p.end_date + p.et_days)

WHEN p.revised_date is not null THEN p.revised



END ) finaldate

from MY_REGISTRATION p ,

where

(CASE
WHEN p.start_date is not null THEN p.start_date
WHEN p.end_date is not null THEN (p.end_date + p.et_days)

WHEN p.revised_date is not null THEN p.revised



END ) finaldate > sysdate;

Once execute, I have problem in :

ORA-00932: inconsistent datatypes - get this message if I add the p.et_days

ORA-00920: invalid relational operator - get this error mesage if I add the case statement after where clause

pls advise.

Ying











Re: Compute Case Statement [message #393962 is a reply to message #393951] Wed, 25 March 2009 03:47 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Ying, you have several posts now on this forum. Surely it is time do us all the courtesy of following the posting guidelines? Post a test case, show us exactly what you did by using a formatted and tagged copy and paste from SQL*Plus. Tell us exactly what version of Oracle you are working with.

In addition, have a look at the COALESCE function in the documentation. It is a simpler way to achieve what you are trying to do.
Re: Compute Case Statement [message #393974 is a reply to message #393951] Wed, 25 March 2009 04:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What are the data types of Start_Date, End_Date and Revised_Date.

If they're al dates, I find it works just fine:
create table test_155 (col_1 date, col_2 date, col_3 date);

insert into test_155 values (sysdate,null,null);
insert into test_155 values (null,sysdate+1,null);
insert into test_155 values (null,null,sysdate+2);

select case when col_1 is not null then col_1
            when col_2 is not null then col_2 + 3
            when col_3 is not null then col_3
            end
from   test_155;
Re: Compute Case Statement [message #393983 is a reply to message #393962] Wed, 25 March 2009 04:59 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Sorry for not 'Fomatted" my sql. I did state my DB version in the previous post - 8.1.7.

The coalesce is not supported in 8i.

Hi JRowbottom, yup..they all are date type. but I was failed to "add" no of days in the case statement after the "THEN"

Ying
Re: Compute Case Statement [message #393988 is a reply to message #393983] Wed, 25 March 2009 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The coalesce is not supported in 8i.

You can fake COALESCE with nested NVL.

Quote:
but I was failed to "add" no of days in the case statement after the "THEN"

This is not an Oracle error.
Use SQL*Plus and copy and paste your session.
And format it.

Regards
Michel
Re: Compute Case Statement [message #394011 is a reply to message #393988] Wed, 25 March 2009 06:15 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Just to update that I have sucessfully compute my statement as below:
select p.end_date

from MY_REGISTRATION p, 

where 

sysdate < (CASE
WHEN p.start_date is not null THEN p.start_date
WHEN p.end_date is not null THEN p.end_date 
WHEN p.revised_date is not null THEN p.revised
END ) 

But once I add the 50 (days)

select p.end_date

from MY_REGISTRATION p, 

where 

sysdate < (CASE
WHEN p.start_date is not null THEN p.start_date
WHEN p.end_date is not null THEN p.end_date + 50
WHEN p.revised_date is not null THEN p.revised
END ) 


I'm using TOAD for SQL execution:

I encounter error message as below:

ORA-00932: inconsistent datatypes

Ying.

Re: Compute Case Statement [message #394012 is a reply to message #394011] Wed, 25 March 2009 06:17 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Post the output of
DESC MY_REGISTRATION 
Re: Compute Case Statement [message #394020 is a reply to message #394011] Wed, 25 March 2009 06:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If I run this test:
--create table test_155 (col_1 date, col_2 date, col_3 date);
create table test_155 (col_1 varchar2(20), col_2 varchar2(20), col_3 varchar2(20));

insert into test_155 values (sysdate,null,null);
insert into test_155 values (null,sysdate+1,null);
insert into test_155 values (null,null,sysdate+2);

select case when col_1 is not null then col_1
            when col_2 is not null then col_2 + 3
            when col_3 is not null then col_3
            end
from   test_155;

Then I get your problem.

Do Not Store DATE data in VARCHAR2 columns, and do not rely on implicit type conversions to and from dates - it will cause you problems.

If I replace my query with this:
select case when col_1 is not null then to_date(col_1,'dd-mon-yyyy hh24:mi:ss')
            when col_2 is not null then to_date(col_2,'dd-mon-yyyy hh24:mi:ss') + 3
            when col_3 is not null then to_date(col_3,'dd-mon-yyyy hh24:mi:ss')
            end
from   test_155;
then it works.
If this is the problem you're having, you'll need to change the date format to match that of the data stored in your columns.
Re: Compute Case Statement [message #396306 is a reply to message #394020] Mon, 06 April 2009 01:54 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
HI JRowbottom,

This is really a good example.

I have created test_155 as below:
create test_155 (col_1 date, col_2 date, col_3 date)
;
The date column are date datatypes, not vachar, but when run the query as below:

select case when col_1 is not null then col_1
            when col_2 is not null then col_2 + 3
            when col_3 is not null then col_3
            end
from   test_155;


Get the same error also.

Another testing I carried out is

select case when col_1 is not null then col_1
            when col_2 is not null then sysdate
            when col_3 is not null then col_3
            end
from   test_155;


Also same problem.


I really wonder why.
Ying.
Re: Compute Case Statement [message #396332 is a reply to message #396306] Mon, 06 April 2009 02:44 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please prove this by posting a copy and paste from a SQL*Plus session of what you did, rather than a partial mock up of what you say you did.
Re: Compute Case Statement [message #396334 is a reply to message #393951] Mon, 06 April 2009 02:49 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Here it is:


SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 6 15:44:39 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reser


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> set serveroutput on
SQL> select case when col_1 is not null then col_1
  2              when col_2 is not null then col_2 + 3
  3              when col_3 is not null then col_3
  4              end
  5  from   test_155;
            when col_2 is not null then col_2 + 3
                                              *
ERROR at line 2:
ORA-00932: inconsistent datatypes

SQL> 


Ying
Re: Compute Case Statement [message #396337 is a reply to message #396334] Mon, 06 April 2009 03:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Well, based on the fact that I had to create my own test case (as you still refuse to do that) and ran the same SQL, it worked absolutely fine for me (just as it did for JRowbottom) although, granted, I am on a different version to you, I would still have to say that it is a picnic error i.e. Problem In Chair Not In Computer, at least, until you supply the requested information.
As I asked previously, please post a copy and paste of a full test case and post the DESC of your MY_REGISTRATION table along with a copy and paste from a sql*plus session of what you did (and not just a little bit of what you did)
Re: Compute Case Statement [message #396398 is a reply to message #393983] Mon, 06 April 2009 07:54 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ying wrote on Wed, 25 March 2009 05:59
Sorry for not 'Fomatted" my sql. I did state my DB version in the previous post - 8.1.7.



Oh, of course we all know where your previous post is.

One thing I notice is that you have a 9.2.0.1 SQL*Plus going against an 8.1.7 database. Both have been desupported.
Re: Compute Case Statement [message #396650 is a reply to message #396398] Tue, 07 April 2009 07:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's possible it's a bug in CASE in 8.1.7, but as it's utterly unsupported, thats fairly irrelevant.

1) Get yourself onto 10g ASAP

2) Can you please do what we ask, and post the output of the whole test case run, including the CREATE TABLE statement.
We have seen too many cases of things not being quite the way they are described to take any assertions at face value.

3) If you only have 3 fields to use, you could try nested decodes:
select decode(col_1
             ,null,decode(col_2
                         ,null,decode(col_3
                                     ,null,null,col_3)
                         ,col_2+3)
             ,col_1)
from test_155;
Re: Compute Case Statement [message #396735 is a reply to message #393951] Tue, 07 April 2009 21:36 Go to previous message
ying
Messages: 143
Registered: May 2002
Senior Member
Thanks JRowbottom,
Problem solved with your decode statement.
Ying
Previous Topic: null values
Next Topic: How can I get multiple rows returned from procedure
Goto Forum:
  


Current Time: Sat Dec 03 05:39:59 CST 2016

Total time taken to generate the page: 0.08689 seconds