CODEWITHSUNDEEP
sqloracle-databasedatecasebetween
Matt
Matt

Reputation: 15071

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

I have two 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 with my 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 AND h.END_DATE 
            THEN 'Head' 
            ELSE 'Line'
       END AS HOL_TYPE
FROM Order o
LEFT JOIN HOL h ON o.O_LIST = h.H_LIST

This is 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 my 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 specifically the O_DATE falls between the condition and only 1 line should be returned.

Upvotes: 1

Views: 803

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Include the date condition in the 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 AND h.END_DATE 
        THEN 'Head' 
        ELSE 'Line'
END AS HOL_TYPE as HOL_TYPE
FROM Order o
LEFT JOIN HOL h ON o.O_LIST = h.H_LIST AND o.O_DATE BETWEEN h.START_DATE AND h.END_DATE

Upvotes: 1

Related Questions