Power BI Oracle OLEDB need a where clause to get the first day of the year-CodePudding

using Power BI to connect to Oracle database server 19c using this connection: provider=OraOLEDB.Oracle.1;data source=gencdbp.domain.pri:1521/GAEN

I need a SQL statement that limits the rows so I don't have to hardcode it.

This works:

select * from aTable where DateCreated >= TO_Date('01/01/2022', 'MM/DD/YYYY')

I have tried the Year function and TRUNC function. Both of these gives me an error: DateCreated is a Timestamp(6). These do not work:

where DateCreated >= trunc(sysdate, 'YEAR')
where trunc(DateCreated, 'YEAR') = trunc(sysdate, 'YEAR')
YEAR(DateCreated) = YEAR(sysdate)

These all give me this error: ORA-00904: "YEAR": invalid identifier Details: DataSourceKind=OleDb DataSourcePath=data source=gencdbp.domain.pri:1521/GAEN;provider=OraOLEDB.Oracle.1 Message=ORA-00904: "YEAR": invalid identifier ORA-00904: "YEAR": invalid identifier ErrorCode=-2147217900

CodePudding user response：

I don't know tool you use (Power BI), but - have a look at the following example:

Sample table that contains a column whose datatype matches the one you specified:

SQL> create table test (datecreated timestamp(6));

Table created.

Insert sample value:

SQL> insert into test values (systimestamp);

1 row created.

OK, let's now try something you said doesn't work (but raises ORA-00904):

SQL> select * from test where trunc(datecreated, 'year') = trunc(sysdate, 'year');

DATECREATED
---------------------------------------------------------------------------
11-MAY-22 09.47.30.544000 PM

It works OK for me.

Another option might be the extract function:

SQL> select * from test where extract(year from datecreated) = extract(year from sysdate);

DATECREATED
---------------------------------------------------------------------------
11-MAY-22 09.47.30.544000 PM

So, are you sure you really used code you posted? Because, that's the error which suggests that there's no column named year in that table. To simulate it, I removed single quotes for year (which is - for the to_char function - format model):

SQL> select * from test where trunc(datecreated, year) = trunc(sysdate, year);
select * from test where trunc(datecreated, year) = trunc(sysdate, year)
                                                                   *
ERROR at line 1:
ORA-00904: "YEAR": invalid identifier


SQL>

Right; here it is, ORA-00904.