outer join with where clause [message #384093] |
Mon, 02 February 2009 03:02  |
manoj339
Messages: 9 Registered: April 2007
|
Junior Member |
|
|
I want to modify the below query to give record with null branch.
But when I add a where clause at the end of query ,I get incorrect results.
o/p of this sql is mentioned below .
SELECT d.track_cod cod,d.br_cod
FROM
(
SELECT b.track_cod,b.br_cod
FROM VW_OD_TRACK_BRANCH b,VW_OD_REG_CNTRY_BR c
WHERE b.mandatory_fl = 'Y'
AND b.br_cod = c.br_cod
AND c.region = 'EMEA'
) a
left outer join OD_PROC_STATUS d
ON (a.track_cod = d.track_cod)
AND (a.br_cod = d.br_cod)
AND ((d.curr_proc_dt = '8-jan-2009')
OR ((d.curr_proc_dt < '8-jan-2009') AND (d.next_proc_dt > '8-jan-2009')))
track_cod br_cod
null null
STATEMENT 600
LIQUIDITY 700
STATEMENT 700
|
|
|
Re: outer join with where clause [message #384097 is a reply to message #384093] |
Mon, 02 February 2009 03:08   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Are the results you've shown the correct ones or the incorrect ones?
what are the other set of results?
In what way are they 'incorrect' - is is not what you want, or do you think you've hit a bug and the rows that are returned actually don't match the SQL you've written.
|
|
|
|
|
Re: outer join with where clause [message #384165 is a reply to message #384093] |
Mon, 02 February 2009 08:13   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
manoj339 wrote on Mon, 02 February 2009 04:02 |
(d.next_proc_dt > '8-jan-2009')))
|
When comparing strings as you are, '1-feb-2010' is less than '8-jan-2009' and '9-feb-2008' is greater than '8-jan-2009'
Please search for the TO_DATE function in Oracle and please understand and learn the difference between a DATE and a STRING.
|
|
|
|