Getting a query result taken from the same data but with temporary var

I got a simple thing to do. Well, maybe not, but someone somewhere surely can help me out : P

I got a simple data structure that contains

expedition date
delivery date
transaction type

I would need to create a query which could order the rows by a date specific to the transaction type.

(ie : using the expedition date for transaction of type “selling”, and delivery date for transaction of type “purchasing”)

I was wondering if there was a more efficient way to do this than by fetching 2 times the same data with different clause where(while adding a column used to order them(tempDate)) and then using another select to encompass these 2 queries to which I would add the order clause on the tempDate.

–> the initial fetching I would do 2 times works on many tables(many, many, many joins)

Basically my current solution is :

Select * from 

(

  Select ...

  date_exp as dateTemp;

  from ...

  where conditions* And dateRelatedCondition

  UNION

  Select ...

  date_livraison as dateTemp;

  from ...

  Where conditions* And NOT(dateRelatedCondition)

) as comboSelect

Order By MIN(comboSelect.dateTemp)
OVER(PARTITION BY(REF_product)), 
(REF_product), 
comboSelect.dateTemp asc;

* ->Those conditions are the same in both inner Select query

Thank you for your time.

Answer

Without the UNION:
dateRelatedCondition should be removed from WHERE and put to the SELECT like:

CASE WHEN dateRelatedCondition THEN date_exp ELSE date_livraison END as dateTemp

Without the subquery:
in ORDER BY you need the same expression in the window function:

Order By MIN(CASE WHEN dateRelatedCondition THEN date_exp ELSE date_livraison END)  
OVER(PARTITION BY(REF_product)), 
(REF_product),
dateTemp asc

Advertisement

Answer