I'm migrating reports from Report Builder 3.0 to our Sharepoint Online via Power BI Report Builder and Power BI Service. I'm running into an issue where even if I run a select * from table...I get "ORA-01861 Literal does not match format string". I've Googled and checked throughout SO, I know it's related to a date format issue, I'm just at a loss for how to either alter my table or cast it properly for Power BI to accept it how my table has it formatted. The overall report has the user(s) select from current year and the previous 2 then pick a quarter to pull back the necessary data for their particular report they want. What am I missing?
ORA-01861 Power BI Report Builder Error
Table Creation Query:
CREATE OR REPLACE FORCE EDITIONABLE VIEW "Schema"."TableR" ("PersonNumber", "YEAR", "QUARTER", "COUNT") AS
select a.personnumber,
to_char(a.activedttime,'yyyy') as year,
case
when to_char(a.activedttime,'mm') in('01','02','03') then 'Q1'
when to_char(a.activedttime,'mm') in ('04','05','06') then 'Q2'
when to_char(a.activedttime,'mm') in ('07','08','09') then 'Q3'
when to_char(a.activedttime,'mm') in ('10','11','12') then 'Q4'
end as quarter,
count(a.subjaccountnumber)
from TableA a, TableB b
where a.personnumber is not null
and a.actvtypcd = 'REOD'
and upper(columnid) = 'ACCTENTITYATTRIBVALUE'
and newvalue = 'Y'
and a.subjaccountnumber= b.acctnbr
and b.mjaccttypcd = 'CK'
and b.contractdate >= '2020-01-01'
group by a.personnumber,
to_char(a.activedttime,'yyyy'),
case
when to_char(a.activedttime,'mm') in('01','02','03') then 'Q1'
when to_char(a.activedttime,'mm') in ('04','05','06') then 'Q2'
when to_char(a.activedttime,'mm') in ('07','08','09') then 'Q3'
when to_char(a.activedttime,'mm') in ('10','11','12') then 'Q4'
end;
My Query in Oracle and results:
Select *
from TableR;
| Personnumber | Year | Quarter | Count
|--------------|--------|---------|------
| 10001 | 2022 | Q3 | 12
| 10002 | 2022 | Q3 | 7