Oracle SQL - CASE statement returning multiple rows when not logically required too -


i have 2 tables:

order

o_no  o_date      o_co  o_type o_list 1653  07/07/2015  12    p      8845 

hol

hol_no  start_date    end_date   h_list 3       29/01/2014    30/06/2014 8845 9       01/10/2014    30/09/2017 8845 

so current query:

select o.o_no, o.o_date, o.o_co, o.o_type, h.start_date, h.end_date,         case when o.o_date between h.start_date , h.end_date              'head'              else 'line'        end hol_type order o left join hol h on o.o_list = h.h_list 

this outputting:

o_no  o_date      o_co  o_type start_date  end_date   hol_type 1653  07/07/2015  12    p      29/01/2014  30/06/2014 line 1653  07/07/2015  12    p      01/10/2014  30/09/2017 head 

but expected output is:

o_no  o_date      o_co  o_type start_date  end_date   hol_type 1653  07/07/2015  12    p      01/10/2014  30/09/2017 head 

because o_date falls between condition , 1 line should returned.

include date condition in join.

select o.o_no, o.o_date, o.o_co, o.o_type, h.start_date, h.end_date,  case when o.o_date between h.start_date , h.end_date          'head'          else 'line' end hol_type hol_type order o left join hol h on o.o_list = h.h_list , o.o_date between h.start_date , h.end_date 

Comments

Popular posts from this blog

gridview - Yii2 DataPorivider $totalSum for a column -

java - Suppress Jboss version details from HTTP error response -

Sass watch command compiles .scss files before full sftp upload -