I am trying to execute the below query in MYSQL, The expectation is as below

1. If no record exists in table, return current timestamp
2. if record exists, compare the values of start date and end date and return appropriate values.

However, for the first execution, where there are no records, the below query is not returning the current timestamp value. What could be wrong here?

SELECT 
CASE WHEN start_dt < end_dt THEN end_dt 
WHEN start_dt >= end_dt THEN start_dt 
ELSE CURRENT_TIMESTAMP() END AS start_dt 
FROM test_master_table WHERE 
schema_nm='test_db' and table_nm='test_table';

CodePudding user response：

The else in the case doesn't magically make a row appear: No rows means no rows.

To always get a row, use (most of) your query as a subquery and apply coalesce:

select coalesce(
  (SELECT 
    CASE
      WHEN start_dt < end_dt THEN end_dt 
      ELSE start_dt 
    END
  FROM test_master_table
  WHERE schema_nm='test_db'
  AND table_nm='test_table'),
  CURRENT_TIMESTAMP()) AS start_dt

CodePudding user response：

You can also left join your query from an ad hoc table with a single row:

select coalesce(greatest(start_dt,end_dt),now()) start_dt
from (select 'dummy row') dummy_row
left join test_master_table on schema_nm='test_db' and table_nm='test_table'