question

neha2322 avatar image
neha2322 asked

convert redshift query to sql query

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;


Can you please check it or write a new query for it . Thanks in advance


mysqlsqlserveramazon-redshift
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.