Case statement Else part Not Working Properly .

Question

Hi ,

we had requirement to write the query up to Price change automatically based on the Start time and End Time .Mysql

I have written query but its else part not get called .

we have Price like Price ,Price2,Price3,Price4

select
case
when S.SwitchPrice ='Price2' then I.Price2
when S.SwitchPrice ='Price3' then I.Price3
when S.SwitchPrice ='Price4' then I.Price4
else I.Price
end AS Price
FROM da5.SwitchPrice as S inner join da5.Inventory as I on S.Inventoryid=I.Inventoryid
where (TIME( CONVERT_TZ(NOW(), @@session.time_zone, '+8:00')) > TIME(S.Start_Tm)
AND TIME( CONVERT_TZ(NOW(), @@session.time_zone, '+8:00')) < TIME(S.End_Tm)) ;

the above query i tried but working partially . if the time condition fails ..its show empty .rather than its point default Price .

Can any one help on this.

Thanks in advance ,

Karthik.K

Pankaj Namekar · Answer

Can you try below solution SELECT IFNULL(( SELECT CASE WHEN S.SwitchPrice = 'Price2' THEN I.Price2 WHEN S.SwitchPrice = 'Price3' THEN I.Price3 WHEN S.SwitchPrice = 'Price4' THEN I.Price4 ELSE I.Price END FROM da5.SwitchPrice AS S WHERE S.Inventoryid = I.Inventoryid AND TIME(CONVERT_TZ(NOW(), @@session.time_zone, '+8:00')) > TIME(S.Start_Tm) AND TIME(CONVERT_TZ(NOW(), @@session.time_zone, '+8:00')) ), I.Price) AS Price FROM da5.Inventory AS I

Karthik K · Answer

@Pankaj Namekar , Awesome!! and Great Thanks lot .you have saved my time .its working as like i expected.

Prasad Raveendran · Answer

SELECT CASE WHEN S.SwitchPrice = 'Price2' THEN I.Price2 WHEN S.SwitchPrice = 'Price3' THEN I.Price3 WHEN S.SwitchPrice = 'Price4' THEN I.Price4 ELSE I.Price END AS Price FROM da5.Inventory AS I LEFT JOIN da5.SwitchPrice AS S ON S.Inventoryid = I.Inventoryid AND TIME(CONVERT_TZ(NOW(), @@session.time_zone, '+8:00')) > TIME(S.Start_Tm) AND TIME(CONVERT_TZ(NOW(), @@session.time_zone, '+8:00')) try this. I hope this should work. Key changes and explanations: Changed INNER JOIN to LEFT JOIN: INNER JOIN requires all conditions to match, so when time condition fails, you get no rows LEFT JOIN keeps all rows from Inventory table regardless of time condition match Moved time conditions to JOIN clause: This way, when time conditions fail, you'll still get the Inventory row with NULL SwitchPrice values When SwitchPrice is NULL (time condition fails), CASE will fall back to ELSE (I.Price)

Karthik K · Answer

@Daniel Wright , Only when the time added , that query not return default value ..so without time condition its works

Daniel Wright · Answer

It seems like the issue you're facing with your query lies in the way the `else` part of your `CASE` statement is behaving when the time condition fails. In your SQL query, you have set up a `CASE` statement to select the appropriate price based on certain conditions. If none of the specified conditions are met, it should default to `I.Price`. However, as you mentioned, when the time condition fails, it returns empty instead of the default price. To address this, you may want to consider the following points: 1. Check the Data : Ensure that the data in your `SwitchPrice` table and `Inventory` table is accurate and that the `Inventoryid` is correctly mapped between the two tables for all scenarios. 2. Time Zone Consideration : Double-check that the time zone conversion and comparison logic is correctly implemented. Any discrepancy here could lead to unexpected results, especially when comparing times. 3. Debugging : You can troubleshoot by breaking down the query to see where exactly it fails. You could run the query without the `WHERE` clause to see if the `CASE` statement is working as expected and then gradually introduce the time conditions back in to pinpoint the issue. 4. Default Value Handling : If everything seems correct but the default price is not being selected, you might want to explicitly check whether `S.SwitchPrice` has unexpected values that are not being caught in your `CASE` statement structure. By verifying these aspects and potentially debugging step by step, you should be able to identify why your `else` part is not working correctly and ensure that the default price is returned when the time condition fails. If you encounter any specific error messages or unexpected behaviors during this process, that could provide further insights into the root cause of the issue. Let me know if you need further assistance or if you have any specific details to share for a more targeted approach!