Re: Implicit Date conversion problem

> "Jamadagni, Rajendra" wrote:
>
> Well,
>
> Let me point out one thing .... following is the original query ...
>
> -- this returns no rows ...
> select ea1.ep_number, ea1.est_dt, veas.est_dt
> from (select a.ep_number, ea.est_dt
> from v_episode_avail_summary_break a, episode_airings ea
> where a.ep_number = ea.ep_number
> and ea.est_dt between '20-Dec-02' and '01-Jan-03'
> group by a.ep_number, ea.est_dt) veas,
> episode_airings ea1
> where veas.ep_number = ea1.ep_number
> and ea1.est_dt between '20-Dec-02' and '01-Jan-03'
>
> -- This DOES work ... returns all the right rows
> select ea1.ep_number, ea1.est_dt, veas.est_dt
> from (select a.ep_number, ea.est_dt
> from v_episode_avail_summary_break a, episode_airings ea
> where a.ep_number = ea.ep_number
> and ea.est_dt between '20-Dec-02' and '01-Jan-03'
> group by a.ep_number, ea.est_dt) veas,
> episode_airings ea1
> where veas.ep_number = ea1.ep_number
> -- and ea1.est_dt between '20-Dec-02' and '01-Jan-03'
>
> Point to consider is that the "commented out where clause" is the same
> as the one used in the from clause query ... then why does it fail?
>
> Tom: according to Oracle, when comparing a varchar2 to a date column,
> the string does get converted to a date ...
>
> The reason i am asking this I have to explain the developers on why
> this isn't working. They agree that explicit datatype conversion is a
> good idea but their managers want to know why ??
>
> (yeah ... sure go ahead pity me ...)
> Raj

Raj,

   I think I've got it. In the in-line view the implicit conversion going on is a to_date() applied to the string constants (since it works). Outside it must be a to_char() applied to the date column. Why it is so escapes my understanding but it may have something to do with getting the column directly from a well known table in the first case, and from a built-on-the-fly view in the second one.   Talking about the developers and their managers, perhaps you could hint that having a GROUP BY without any aggregate function is probably their way to program SELECT DISTINCT, and that since they are using only a single column from (which by the way happens to also be in episode_airings, since it's used for the join) is a bit weird (unless you have simplified the query for testing purposes?). I am sure that the query would be better directly working on the tables 'below' v_episode_avail_summary_break.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).