> "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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
