Unable to write oracle/SQL Query log transions data without subquery

Question

I have 2 different transtions in the same table how can i join them into one line without subquerys and then joining them because i'm trying to combine multiple transtions cases if i create subquery for each transtions case and join them its causing performance issue. really appricate your help

CUREENT VIEW

ORDER         ACTION    DATE_TIME_CREATED
D8ZH4RJSB-689   PICK    8/1/2018 0:35
D8ZH4RJSB-689   SHIP    8/1/2018 12:03

Requiered Output

ORDER          PICK_DATE_TIME_CREATED    SHIP_DATE_TIME_CREATED
D8ZH4RJSB-689     8/1/2018 0:35            8/1/2018 12:03

This is my current code

SELECT "ORDER", --,"ACTION",ship_complete_time as date_time_created
CASE WHEN "ACTION" = 'SHIP'
THEN ship_complete_time
END AS SHIP_COM_TIME,
CASE WHEN "ACTION" = 'PICK'
THEN ship_complete_time
END AS pick_time  
FROM TRANSATIONS
WHERE "ORDER" = 'D8ZH4RJSB-689'

current output

ORDER           SHIP_COM_TIME              PICK_TIME
D8ZH4RJSB-689        (null)              2018-08-01 00:35
D8ZH4RJSB-689   2018-08-01 12:03          (null)

Is this oracle, or MySQL? These are 2 different database engines. — Blue
– Blue, Commented Aug 2, 2018 at 21:05
Are there cases when TRANSACTIONS contains data for only one of 2 actions, for any specific ORDER value? If so, which one would that be? How would you expect the result to look like then? Would it be an empty result (rows omitted), or just a NULL value in the appropriate column? — Hilarion
– Hilarion, Commented Aug 2, 2018 at 22:26
If an order is missing a case then it would show null value. — Karthik Elangovan
– Karthik Elangovan, Commented Aug 3, 2018 at 2:27

Gordon Linoff · Accepted Answer · 2018-08-02 21:06:46Z

3

Use aggregation:

SELECT "ORDER", --,"ACTION",ship_complete_time as date_time_created
       MAX(CASE WHEN "ACTION" = 'SHIP' THEN ship_complete_time END) AS SHIP_COM_TIME,
       MAX(CASE WHEN "ACTION" = 'PICK' THEN ship_complete_time END) AS pick_time  
FROM TRANSATIONS
WHERE "ORDER" = 'D8ZH4RJSB-689'
GROUP BY "ORDER";

answered Aug 2, 2018 at 21:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vinnie Pescado · Accepted Answer · 2018-08-03 04:15:17Z

0

If you know that there will be a row for picking and shipping you can do the following:

SELECT P.ORDER "ORDER", P.DATE_TIME_CREATED "PICK_DATE_TIME_CREATED", S.DATE_TIME_CREATED "SHIP_DATE_TIME_CREATED"
  FROM TRANSACTIONS P, TRANSACTIONS S
 WHERE P.ORDER = S.ORDER
   AND P.ACTION = 'PICK'
   AND S.ACTION = 'SHIP'
   AND P.ORDER = 'D8ZH4RJSB-689';

Use outer joins if you can't be sure that both rows will exist for a given order. For performance you might also want to add an index on the columns ORDER, ACTION.

edited Aug 3, 2018 at 4:15

answered Aug 3, 2018 at 4:08

Vinnie Pescado

916 bronze badges

1 Comment

Vasyl Moskalov Over a year ago

And you got empty rowset.

Collectives™ on Stack Overflow

Unable to write oracle/SQL Query log transions data without subquery

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related