I need to run an ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY' command before I can execute another Oracle query successfully. This query uses an Oracle function that returns a table:
SELECT
IM_MEASURES_PER_DAY.METRIC_DATE,
IM_MEASURES_PER_DAY.STATUS_OPEN,
IM_MEASURES_PER_DAY.STATUS_OVERDUE,
IM_MEASURES_PER_DAY.CLOSED,
IM_MEASURES_PER_DAY.OPENEN,
IM_MEASURES_PER_DAY.CUMMULATIVE_CLOSED
FROM
(
SELECT CAST(TO_DATE(METRIC_PERIOD,'YYYYMMDD') AS DATE) AS METRIC_DATE,
ST_OPEN AS STATUS_OPEN,
ST_OVERDUE AS STATUS_OVERDUE,
CLOSED AS CLOSED,
OPENED AS OPENEN,
CUM_CLOSED AS CUMMULATIVE_CLOSED
FROM TABLE
(
d_get_gra4_DAY /* GRA4: DAY level */
(IP_SDATE => '01-01-2019'
,IP_FDATE => '07-30-2019'
,IP_ENTITY => IM_USERVCHARTAB('ALL')
,IP_SUBENTITY => IM_USERVCHARTAB('ALL')
,IP_LOCATION_IMPACTED => IM_USERVCHARTAB('ALL')
,IP_LOCATION_ORIGINATED => IM_USERVCHARTAB('ALL')
,IP_PRIORITY => IM_USERVCHARTAB('ALL')
,IP_DATA_LABEL => IM_USERVCHARTAB('ALL')
,IP_RAISER_NAME => IM_USERVCHARTAB('ALL')
,IP_OWNER_DEPARTMENT => IM_USERVCHARTAB('ALL')
,IP_OWNER_NAME => IM_USERVCHARTAB('ALL')
,IP_COORDINATOR_NAME => IM_USERVCHARTAB('ALL')
,IP_OVERDUE => IM_USERVCHARTAB('ALL')
,IP_DQ_TASKFORCE => IM_USERVCHARTAB('ALL')
)
)
) IM_MEASURES_PER_DAY
In the From ODBC dialog, Advanced Options, SQL Statement it seems not possible to enter multiple SQL statements. I tried to separate them with a semi-colon; tried to embed the statements in a BEGIN-END block, but all I get is errors.
Is there a way in Power Bi to initialize an Oracle or ODBC connection with an ALTER SESSION statement?
I know this can be achieved on the database level, but I need it on a session level.
Rgds,
-johan
