Home » SQL & PL/SQL » SQL & PL/SQL » outer join with where clause (Oracle 9.2.0.8 ,Sun solaris 5.8)
outer join with where clause [message #384093] Mon, 02 February 2009 03:02 Go to next message
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 Go to previous messageGo to next message
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 #384099 is a reply to message #384093] Mon, 02 February 2009 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Wed, 13 August 2008 13:31
For your next question, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


Post DDL for your tables.
'8-jan-2009' is a string not a date.

Regards
Michel

Re: outer join with where clause [message #384100 is a reply to message #384097] Mon, 02 February 2009 03:10 Go to previous messageGo to next message
manoj339
Messages: 9
Registered: April 2007
Junior Member
The results are without the where clause.
The results are correct without where clause.
I want to get the row which is having null branch code.(first row in this case)

Re: outer join with where clause [message #384165 is a reply to message #384093] Mon, 02 February 2009 08:13 Go to previous messageGo to next message
joy_division
Messages: 4643
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.
Re: outer join with where clause [message #384171 is a reply to message #384100] Mon, 02 February 2009 08:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And what is the where clause that you are adding?

Previous Topic: Bulk insert count limit
Next Topic: Help on an Update statement for select records
Goto Forum:
  


Current Time: Thu Dec 08 10:26:45 CST 2016

Total time taken to generate the page: 0.06392 seconds