Re: fun debugging stuff on a friday

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@xxxxxxxxx> 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@xxxxxxxxxxxxxx> 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.
>>
>
>

Other related posts: