-1

I have this sql query

Select parent_order_id,product_id,hub_id,cast(1- '0.05' * MONTHS_BETWEEN(SYSDATE(), order_processing_date)as unsigned) 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  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  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'm getting error every derived table must have it's own alias I know i need to use alias but i have already used it and i don't know where to modify my query Can anyone just check the whole query and update it. It will be a great help.

Ps: I just edited my question because the previous one got solved just by writing UNSIGNED instead of int

Krystle
  • 23
  • 1
  • 5
  • I get a different error on SET o1.status = "Delivered". – P.Salmon Feb 27 '21 at 08:59
  • Actually because when i paste it in any sql beautifier , it gives error on SET o1.status = "Delivered" but when i try to execute it on sql workbench it gives me on the select statement – Krystle Feb 27 '21 at 09:01
  • I just can't figure out the right solution – Krystle Feb 27 '21 at 09:02
  • SET is not valid here and 'i'm just can't figure out the right solution' - you haven't described what you are seeking a solution to only a problem with your solution. – P.Salmon Feb 27 '21 at 09:02
  • okay so you are saying instead of SET o1.status = "Delivered" i should write o1.status = "Delivered" – Krystle Feb 27 '21 at 09:03
  • Try it and see.. – P.Salmon Feb 27 '21 at 09:04
  • I tried but still the error persists – Krystle Feb 27 '21 at 09:08
  • You use the set statement twice. You probably followed a tutorial for a different database product. – Shadow Feb 27 '21 at 09:15
  • I removed the set statement now and wrote o1.status = "Delivered" . But the error still persists – Krystle Feb 27 '21 at 09:18
  • There seem to be too many parenteses around q2. – Shadow Feb 27 '21 at 09:59
  • But all of are of use and I rechecked it. – Krystle Feb 27 '21 at 10:01
  • months_between is an oracle function and does not exist in mysql - you may be following an oracle tutorial but using mysql - that won't work there are many differences between oracle and mysql. If you want to learn oracle then it's also free , but a bit of a nuisance to install OR you may already have oracle and not tagged the question correctly. – P.Salmon Feb 27 '21 at 12:08

1 Answers1

0

You didn't provide an alias to the outer query:

Select parent_order_id,product_id,hub_id,cast(1- '0.05' * DATEDIFF(MONTH, SYSDATE(), order_processing_date)as unsigned) 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  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  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) table1
    group by 
      parent_order_id, 
      product_id, 
      hub_id, 
      order_processing_date 
    order by 
      parent_order_id desc, 
      total_sum desc;
Dwight Reynoldson
  • 950
  • 8
  • 12
  • Thanks that got solved. But the query still didn't worked, throwing this error now FUNCTION licious.MONTHS_BETWEEN does not exist. I have used moths_between to find the number of month between SYSDATE() and order_processing_date – Krystle Feb 27 '21 at 10:08
  • You can use datediff for mysql instead of months_between https://stackoverflow.com/questions/288984/the-difference-in-months-between-dates-in-mysql – Dwight Reynoldson Feb 27 '21 at 10:15
  • *You can use datediff for mysql instead of months_between* This is wrong - converting days to months is approximate. `TIMESTAMPDIFF(MONTH, ...)` needed. – Akina Feb 27 '21 at 11:19
  • Thanks for the timestampdiff() it is running perfectly now. – Krystle Feb 27 '21 at 19:05