CASE statement with CTE and need fetch the date as per data retrieved-CodePudding

I need to automate (current day count)-(previous day count) if the deference >0 then pass else fail. but here the data in the table have only week days, so I'm unable to use current date-previous day count. so I had write a code like this but I'm unable to fetch the data. it shows error message like ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause: *Action: Error at Line: 28 Column: 56. please help on this one to proceed further.

and one more concern if we fetch the data from yesterday we just get the date as using current_date-1. same way if we fetch the data from 3days back also it shows only previous day as per my code. So how can we pass dynamically to fetch the date as per the day.


with today as ( select 
                distinct source_desc, 
                src_dim_id, 
                src_update_datetime, 
                Dw_insert_datetime, 
                src_insert_datetime,
                current_date-1 as P_datetime, 
                current_date as T_datetime ,
                rec_count 
                from dw_dart.tab_total_last_updated
                where to_char(Dw_insert_datetime, 'mm-dd-yy')=to_char(current_date, 'mm-dd-yy')and source_desc in
                ('UES KMI','UES NPS','NAVHUB(Membership)')
                ),-------------this going to fetch current day data
                
  yesterday as (select src_dim_id, 
                    category, 
                    source_desc, 
                    src_update_datetime, 
                    Dw_insert_datetime,
                    src_insert_datetime,
                    current_date-1 as P_datetime, 
                    current_date as T_datetime ,
                    rec_count 
                    from dw_dart.tab_total_last_updated
                  where  
                    case when to_char(current_date, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH')='MONDAY   '                then  
                to_char(Dw_insert_datetime, 'mm-dd-yy')= to_char(current_date-3, 'mm-dd-yy') else
                to_char(Dw_insert_datetime, 'mm-dd-yy')= to_char(current_date-1, 'mm-dd-yy') end 
                     ),----------this code going to fetch If today is Monday then fetch 3days(Friday back data) or previous day data. 
            
    diff as (select t.src_dim_id, 
                    'Daily_changes' as frequency, 
                    t.source_desc, 
                    t.src_update_datetime, 
                    t.Dw_insert_datetime,
                    t.src_insert_datetime,
                    t.P_datetime, 
                    t.T_datetime ,
                    t.rec_count as t_rec_count, 
                    y.Dw_insert_datetime as Y_DW_insert_datetime, 
                    y.rec_count as p_rec_count from today t 
                    left join yesterday y on y.source_desc=t.source_desc ), ----            select * from diff

   final_query1 as (select src_dim_id, 
                    category, 
                    source_desc, 
                    src_update_datetime, 
                    Dw_insert_datetime, 
                    src_insert_datetime,
                    P_datetime, 
                    T_datetime,
                    p_rec_count,
                    t_rec_count,
                    t_rec_count-p_rec_count as difference 
                    from diff),--this one give us difference between today's count and previous day count
                    
--                select * from final_query1
                
   final_query2 as (select src_dim_id, 
                    'Daily' as changes, 
                    source_desc, 
                    src_update_datetime, 
                    Dw_insert_datetime, 
                    src_insert_datetime,
                    P_datetime, 
                    T_datetime ,
                    p_rec_count,
                    t_rec_count,
                    difference, 
                    case when difference > 0 then 'pass' else 'fail' end as status from final_query1)
select * from final_query2

`````````````````````````````````````````````````

CodePudding user response：

Query around line #28 is

SELECT ...
FROM ...
WHERE  CASE WHEN to_char(current_date, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH')='MONDAY   '                THEN
        TO_CHAR(Dw_insert_datetime, 'mm-dd-yy')= to_char(current_date-3, 'mm-dd-yy') ELSE
        TO_CHAR(Dw_insert_datetime, 'mm-dd-yy')= to_char(current_date-1, 'mm-dd-yy') END

That's obviously wrong; should've been

WHERE TO_CHAR (Dw_insert_datetime, 'mm-dd-yy') =
   CASE
      WHEN TO_CHAR (CURRENT_DATE, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') =
           'MONDAY   '
      THEN
         TO_CHAR (CURRENT_DATE - 3, 'mm-dd-yy')
      ELSE
         TO_CHAR (CURRENT_DATE - 1, 'mm-dd-yy')
   END

CodePudding user response：

The error message points you to line 28. You are missing a condition there. This is what you have:

where 
  case when to_char(current_date, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH')='MONDAY   '
    then to_char(Dw_insert_datetime, 'mm-dd-yy')= to_char(current_date-3, 'mm-dd-yy')
    else to_char(Dw_insert_datetime, 'mm-dd-yy')= to_char(current_date-1, 'mm-dd-yy')
  end

You are trying to make the case expression result in a boolean value. This would run just fine in standard SQL. The problem with this: Oracle is not standard compliant here, because there is no boolean data type in Oracle SQL, unfortunately.

Anyway, a CASE expression is used to establish a boolean evaluation, e.g. in the SELECT or ORDER BY clause. The WHERE clause already is a boolean evaluation, so CASE is not needed there hence, as you can use AND and OR instead.

where 
(
  (to_char(current_date, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'MONDAY   '
   and
   to_char(Dw_insert_datetime, 'mm-dd-yy') = to_char(current_date-3, 'mm-dd-yy')
  )
 or    
  (to_char(current_date, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') <> 'MONDAY   '
   and
   to_char(Dw_insert_datetime, 'mm-dd-yy') = to_char(current_date-1, 'mm-dd-yy')
  )
)

If you want to use CASE for readability, I suggest

where to_char(Dw_insert_datetime, 'mm-dd-yy') =
  case when to_char(current_date, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'MONDAY   '
    then to_char(current_date-3, 'mm-dd-yy')
    else to_char(current_date-1, 'mm-dd-yy')
  end

On a side note: CURRENT_DATE is the datetime of the client. It is not used often. Most often we want SYSDATE, the datetime of the database instead.

Another side note: Rather than comparing formatted strings, it is more common to simply truncate the datetime (i.e. set the time part to 00:00:00) by using TRUNC, e.g. TRUNC(dw_insert_datetime) = TRUNC(SYSDATE). When possible it is recommended, though, not to invoke functions on your tables' columns, so it is more likely that an index can be used: dw_insert_datetime >= TRUNC(SYSDATE) AND dw_insert_datetime < TRUNC(SYSDATE) INTERVAL '1' DAY.