Have updated the question with suggested change and new error encountered, I'm executing this code

SELECT
    b.*, 
    COUNT(CASE WHEN DOJ <= current_date 
                    AND (DOL IS NULL OR DOL >= current_date) 
                  THEN ID END) FIRST 
FROM
    (
     select ID,max(case when a.massn in('00','A1') then a.begda end) as DOJ,
     max(case when a.massn in ('A4') then a.begda end) as DOP,      
     max(case when a.massn in ('A3','A5') then a.begda end) as DOT,       
     max(case when a.massn='A6' then a.begda end) dol,
     max(case when a.massn in ('A6') then a.aedtm end) as changed_on_date,     
     max(case when a.massn in ('A6') then massn end) as massn,
     max(case when a.massn in ('A6') then massg end) as massg 
    from "Table"."T1" a 
     group by ID
  )b
  WHERE FIRST = 1

I'm getting an error:

ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string" *Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace. *Action: Correct the format string to match the literal.

Any help will be highly appreciated

CodePudding user response：

I think this is what you're after.

SQL> with
  2  test (id, doj, dol) as
  3  -- sample data
  4    (select 100, sysdate - 10, sysdate   5 from dual union all
  5     select 200, sysdate     , sysdate - 8 from dual
  6    ),
  7  -- new CTE that counts rows
  8  tcount as
  9    (select t.id, t.doj, t.dol,
 10       count(case when doj <= current_date and (dol is null or dol >= current_date) then id end) first
 11     from test t
 12     group by t.id, t.doj, t.dol
 13    )
 14  -- finally
 15  select *
 16  from tcount
 17  where first = 1;

        ID DOJ                 DOL                      FIRST
---------- ------------------- ------------------- ----------
       100 22.11.2021 19:45:43 07.12.2021 19:45:43          1

SQL>

What does it do?

• lines #1 - 5 - sample data; you already have that table and wrongly identified it by ()
• lines #6 - 10 - selecting rows from the table, along with the count function
• finally, select rows for which count function returned 1

CodePudding user response：

I figured out, my date format was wrong according to Oracle Syntax,

Below code worked for me,

TO_CHAR(TO_DATE(a.begda,'YYYYMMDD'), 'DD-MM-YYYY')