Paginated Report ORA-01861 Error

[Anonymous]

I have a report which has options for the last 3 years and then the respected quarter you can select from for that particular year. However, when I go and select a year and a particular quater; I get the below error message:

dsCurrQuar:

select
case
when to_char(sysdate,'MM') in('01','02','03') then 'Q1'
when to_char(sysdate,'MM') in ('04','05','06') then 'Q2'
when to_char(sysdate,'MM') in ('07','08','09') then 'Q3'
when to_char(sysdate,'MM') in ('10','11','12') then 'Q4'
end as quarter
from dual

dsDefaultYear:

select
to_char(start_date, 'yyyy') - 1 + level year
from
(select date '2020-01-01' start_date, SYSDATE end_date from dual)
connect by
level <= months_between(trunc(end_date, 'yyyy'),
trunc(start_date, 'yyyy'))/12 + 1
ORDER BY YEAR DESC

dsReportOutput:

select distinct p.personNumber, p.dsplyName,
NVL((select c.count from tableC c
where trunc(c.year, 'YYYY') = trunc(:pYear, 'YYYY')
and to_char(c.quarter) = to_char(:pQua)
and c.personNumber = p.personNumber),0) as chkaccts,
NVL((select r.count from tableR r
where trunc(r.year, 'YYYY') = trunc(:pYear2, 'YYYY')
and to_char(r.quarter) = to_char(:pQua2)
and r.personNumber = p.personNumber),0) as reods
from tableA a, tableP p, tableE e
where a.personNumber = p.personNumber
and a.personNumber = e.personNumber
and (e.inactivedate is null or e.inactivedate > sysdate - 366)

 dsReportOutput Parameters:

I didn't initially create this report, I'm migrating from Sharepoint 2010 to Sharepoint Online via our Power BI Service (Premium Per User License account). What do I need to do/change/format properly in order for this report to be ran successfully?

[Anonymous]

I was finally able to get both the dsCurrQuar and dsReportOutput to run successfully in Oracle and produce a good report, however, still the "literal does not match format string" I've confirmed each column's type and the format of each column matches as it should, nothing is casted outside of what it's originally designated as. If this runs with no issue in Oracle and my connection in Power BI Report Builder goes back to Oracle as the source, why is there still a constant issue with the "literal does no match format string"?

updated dsReportOutput:

select 
max(p.personNumber) personNumber, p.dsplyName,
NVL((select max(c.count) from tableC c
where c.year = to_char('2022')
and c.quarter = to_char(quarter)
and c.personNumber = p.personNumber),0) as chkaccts,
NVL((select max(r.count) from table r
where r.year = to_char('2022')
and r.quarter = to_char(quarter)
and r.personNumber = p.personNumber),0) as reods
from table a, table p, table e
where a.personNumber = p.personNumber
and a.personNumber = e.personNumber
and (e.inactivedate is null or e.inactivedate > sysdate - 366)
order by p.personNumber, p.dsplyName;

[v-jayw-msft]

Hi @Anonymous ,

ORA-01861 databases occur when “literal does not match format string”. A real value in Oracle is a fixed, given data point. For example, in a given list of names, you might have literals like “BRAD” or “CHERIE”, known as character literals.

Try the solutions here:

https://www.techonthenet.com/oracle/errors/ora01861.php 

Best Regards,

Jay

[Anonymous]

I've updated my query to the below and get a ORA-01843: "not a valid month" error within Oracle SQL Developer. Instead of having a secondary query to inject the parameters and having to worry about all that, I just added the case statements determining which quarter to the main query and feeding the corresponding quarter in accordingly. When I do run the case statement from dual, then I do return 'Q3' given it's August and August is Q3....so I know it's returning the right quarter for this particular month.

select distinct p.personNumber, p.dsplyName,
case
when to_char(sysdate,'mm') in('01','02','03') then 'Q1'
when to_char(sysdate,'mm') in ('04','05','06') then 'Q2'
when to_char(sysdate,'mm') in ('07','08','09') then 'Q3'
when to_char(sysdate,'mm') in ('10','11','12') then 'Q4'
end as quarter,
NVL((select c.count from tableC c
where c.year = to_date('2022', 'yyyy')
and c.quarter = to_char(quarter)
and c.personNumber = p.personNumber),0) as chkaccts,
NVL((select r.count from tableR r
where r.year = to_date('2022', 'yyyy')
and r.quarter = to_char(quarter)
and r.personNumber = p.personNumber),0) as reods
from tableA a, tableP p, tableE e
where a.personNumber = p.personNumber
and a.personNumber = e.personNumber
and (e.inactivedate is null or e.inactivedate > sysdate - 366)

*Edit: Once I put that query into Power BI Report Builder and run the report, still same error as the original *