query solve

14 views

Skip to first unread message

Neha Kumari

unread,

Feb 25, 2021, 11:41:00 AM2/25/21

to Google Cloud SQL discuss

I'm using this redshift query now which is working perfectly fine. But due to some reasons I need to convert it into mysql. It gives parent_order_id, product_id and hub_id.

'select parent_order_id,product_id,hub_id,(1 - 0.05 * MONTHS_BETWEEN(LOCALTIMESTAMP, order_processing_date)::INT ) AS total_sum ' 'FROM (select q1.parent_order_id,q1.product_id,q1.hub_id,q1.order_processing_date from (select o1.parent_order_id,o2.product_id,' 'o1.hub_id,o1.order_processing_date from pipeline.consumer_omsv1_orders as o1 join consumer_omsv1_order_items as o2 ' 'on o1.order_id=o2.order_id WHERE DATE(o1.order_processing_date) BETWEEN DATE(CURRENT_TIMESTAMP - interval \'90 day\') ' 'AND DATE(CURRENT_TIMESTAMP) AND o1.status = \'Delivered\' ) as q1 join ( (select o1.parent_order_id from pipeline.consumer_omsv1_orders' ' as o1 join consumer_omsv1_order_items as o2 on o1.order_id=o2.order_id WHERE DATE(o1.order_processing_date) ' 'BETWEEN DATE(CURRENT_TIMESTAMP - interval \'90 day\') AND DATE(CURRENT_TIMESTAMP) AND o1.status = \'Delivered\' ' 'group by parent_order_id having count(parent_order_id)>1) ) as q2 on q1.parent_order_id=q2.parent_order_id) ' 'group by parent_order_id,product_id,hub_id,order_processing_date order by parent_order_id desc,total_sum desc'

I tried converting this and below is my query but it is not working.

Select parent_order_id,product_id,hub_id , (1 - '0.05' * MONTHS_BETWEEN(SYSDATE(), order_processing_date) ) AS total_sum FROM (select q1.parent_order_id,q1.product_id,q1.hub_id,q1.order_processing_date from (select o1.parent_order_id,o2.product_id, o1.hub_id,o1.order_processing_date from licious.orders as o1 join licious.order_items as o2 on o1.order_id=o2.order_id WHERE DATE(o1.order_processing_date) BETWEEN DATE(DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 90 day) AND DATE(CURRENT_TIMESTAMP()) AND SET o1.status = "Delivered" ) as q1 join ( (select o1.parent_order_id from licious.orders as o1 join licious.order_items as o2 on o1.order_id=o2.order_id WHERE DATE(o1.order_processing_date) BETWEEN DATE(DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 90 day) AND DATE(CURRENT_TIMESTAMP()) AND SET o1.status = "Delivered" group by parent_order_id having count(parent_order_id > 1) ) as q2 on q1.parent_order_id=q2.parent_order_id) group by parent_order_id,product_id,hub_id,order_processing_date order by parent_order_id desc,total_sum desc;