CODEWITHSUNDEEP
mysql
Toma Tomov
Toma Tomov

Reputation: 1654

Can I use variable with value from some function

Is there legal way of using variable like that :

SELECT dr.id, @diff:=DATEDIFF(NOW(), DATE_ADD(dr.dt, INTERVAL dr.payment_session DAY))
FROM `dst_request` as dr
WHERE @diff >= 0 OR @diff <=5

It doesn't throw errors like that but it also doesn't return the proper result. Is it valid query ? Thank you!

Upvotes: 1

Views: 35

Answers (2)

P.Salmon
P.Salmon

Reputation: 17615

Is it a valid query yes it's syntactically correct but in the order of operations the where clause is applied before the select so @diff is probably null when the where clause is executed, you could apply the calculation in the where clause to overcome this.

SELECT dr.id, DATEDIFF(NOW(), DATE_ADD(dr.dt, INTERVAL dr.payment_session DAY)) diff
FROM `dst_request` as dr
WHERE DATEDIFF(NOW(), DATE_ADD(dr.dt, INTERVAL dr.payment_session DAY)) >= 0 
OR DATEDIFF(NOW(), DATE_ADD(dr.dt, INTERVAL dr.payment_session DAY)) <=5

https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/

Upvotes: 1

Jonathan Wilson
Jonathan Wilson

Reputation: 4295

Using a derived table:

SELECT 
    id, 
    diff
FROM (
        SELECT
            dr.id,
            DATEDIFF(NOW(), DATE_ADD(dr.dt, INTERVAL dr.payment_session DAY)) as diff
         FROM `dst_request` as dr
) as t
WHERE diff >= 0 OR diff <=5

Forgive me if there's a syntax or formatting error, as I transcribe this using my phone while in the bathtub. The concept is correct though.

Upvotes: 1

Related Questions