Re: fun debugging stuff on a friday

From: Cliff Nadler <cnadler_at_gmail.com>
Date: Fri, 10 Apr 2009 10:34:35 -0500
Message-ID: <148f79bc0904100834j4ef0bcf4od5fa589c94cb8d09_at_mail.gmail.com>

Also,, when comparing the DATE datatype, it includes a time component, and they have to match at well.

The '30-OCT-07' is really '30-OCT-07 00:00:00', but the EFFECTIVE_DATE field (if it's a DATE type) can include any time during that day.

Using TRUNC() on the column can reset the time to always be 00:00:00, making it easier to compare.

Cliff

On Fri, Apr 10, 2009 at 9:50 AM, Bradd Piontek <piontekdd_at_gmail.com> wrote:

> What datatype in EFFECTIVE_DATE for the table SECURITY_DETAILS?
> I'm guessing it is a VARCHAR2, which would explain why you get no rows back
> in the first query.
>
> Comparing VARCHAR2 to DATE can be problematic if you don't explicitly
> convert one of the datatypes to the other.
>
> Bradd Piontek
> "Next to doing a good job yourself,
> the greatest joy is in having someone
> else do a first-class job under your
> direction."
> -- William Feather
>
>
> On Fri, Apr 10, 2009 at 9:47 AM, <TESTAJ3_at_nationwide.com> wrote:
>
>>
>> I get this code below, first glance says, this should work, Oracle is so
>> much like C, gives you a gun, ammo, takes off the safety and has the trigger
>> most of the way pulled while pointing the gun at your foot and assists you
>> in pulling the trigger the rest of the way.
>>
>>
>>
>>
>> declare
>> current_date date;
>> ctr number;
>>
>> begin
>>
>> current_date:='30-OCT-07';
>>
>>
>> SELECT count(distinct SECURITY_ALIAS)
>> into ctr
>> FROM DATAMARTDBO.SECURITY_DETAILS
>> WHERE EFFECTIVE_DATE = current_date;
>>
>> dbms_output.put_line('CTR1:'||ctr);
>>
>>
>>
>> SELECT count(distinct SECURITY_ALIAS)
>> into ctr
>> FROM DATAMARTDBO.SECURITY_DETAILS
>> WHERE EFFECTIVE_DATE = '30-OCT-07';
>>
>> dbms_output.put_line('CTR2:'||ctr);
>>
>> end;
>> /
>>
>> CTR1: 0
>> CTR2: 35178
>>
>>
>> _______________________________________
>> Joe Testa, Oracle Certified Professional
>> Senior Consultant
>> Data Engineering and Administration
>> Nationwide Investments
>>
>> (Work) 614-677-1668
>> (Cell) 614-312-6715
>>
>> Interested in helping out your marriage?
>> Ask me about "Weekend to Remember"
>> Dec 11-13, 2009 here in Columbus.
>>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 10 2009 - 10:34:35 CDT