Re: SQL differences between 8i and 9i

From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Thu, 12 Jun 2003 12:39:36 GMT
Message-ID: <cw_Fa.1209962$F1.143157_at_sccrnsc04>


is valuedate a date field?(and if it is a string field with a date in it then double ugh) If so don't compare dates and strings. Dates are dates and strings are strings. If you are relying on such comparison (dates vs strings) then you are depending upon some default date format specification. (which is probably why it does not work) Instead use to_date on the strings eg
to_date('20060507','yyyymmdd')
> VALUEDATE >= to_date('20000613','yyyymmdd') AND VALUEDATE <=
to_date('20060507','yyyymmdd'),
...
Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Tom Geyzen" <tomgeyzen_at_hotmail.com> wrote in message
news:2c8b4611.0306120056.21808339_at_posting.google.com...

> Hi,
>
> I've got an application running on Oracle 9i which used nested queries
> with aggregate functions. I now have to make the app run on Oracle 8.
>
> This kind of query doesn't run anymore :
>
> SELECT SUM(Count) AS GRAND_Total,
> (SELECT SUM(Count) AS Total_OK FROM TABLE1 WHERE STATUS = 'OK' AND
> VALUEDATE >= '20000613' AND VALUEDATE <= '20060507'),
> (SELECT SUM(Count) AS Total_SKIP FROM TABLE1 WHERE STATUS = 'SKIP' AND
> VALUEDATE >= '20000613' AND VALUEDATE <= '20060507'),
> (SELECT SUM(Count) AS Total_HIT FROM TABLE1 WHERE STATUS <> 'OK' AND
> STATUS <> 'SKIP' AND VALUEDATE >= '20000613' AND VALUEDATE <=
> '20060507')
> FROM TABLE1 WHERE VALUEDATE >= '20000613' AND VALUEDATE <=
> '20060507'GROUP BY 1
>
>
> It runs fine on 9i...
>
> Any advice on how to convert this to work under 8i would be
> appreciated ...
>
> tia,
>
> Tom
Received on Thu Jun 12 2003 - 14:39:36 CEST

Original text of this message