Home » Fusion Middleware & Colab Suite » Business Intelligence » OBIEE DDR Query 12c (obiee 12c)
OBIEE DDR Query 12c [message #682348] |
Sat, 17 October 2020 19:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 Registered: March 2008 Location: India
|
Senior Member |
|
|
Hello Sir,
The below is my OBIEE DDR Query as shown below
(SELECT /*+ PARALLEL(16) */
DISTINCT
T636094.REGION AS "Region Reporting",
T636094.SERVICE_AREA AS "Service Area",
T636094.SITE AS "Site",
T636094.DEVICE_POSITION AS "Device Position",
T636094.ASSET_TYPE_DESCRIPTION AS "Asset Type Description",
T636094.WORK_ORDER AS "Work Order",
T636094.WORK_ORDER_DESCRIPTION AS "Work Orer Description",
T636094.LONG_DESCRIPTION AS "Long Description",
T636094.WORK_TYPE_CODE AS "Work Type Code",
T636094.ACTIVITY_CODE AS "Activity Code",
T636094.CALC_PRIORITY_RISK AS "Risk Assessment Group",
T636094.PRIORITY_RISK AS "Risk Assessment",
T636094.PRIORITY_JUSTIFICATION AS "Priority Justification",
T636094.WORK_ORDER_PRIORTY AS "Work Order Priority",
T636094.WORK_ORDER_STATUS AS "Work Order Status",
TO_DATE(T636094.ACTUAL_DT_REPORT_DT,'DD/MM/RRRR') AS "WO Reported Date",
TO_DATE(T636094.ACTUAL_DT_TAR_ST_DT,'DD/MM/RRRR') AS "WO Target Start Date",
TO_DATE(T636094.ACTUAL_DT_TAR_FIN_DT,'DD/MM/RRRR') AS "WO Target Finish Date",
TO_DATE(T636094.ACTUAL_DT_SCHD_START_DT,'DD/MM/RRRR') AS "WO Scheduled Start Date",
TO_DATE(T636094.ACTUAL_DT_SCHD_FIN_DT,'DD/MM/RRRR') AS "WO Scheduled Finish Date",
TO_DATE(T636094.ACTUAL_START_DATE,'DD/MM/RRRR') AS "WO Actual Start Date",
TO_DATE(T636094.ACTUAL_FINISH_DATE,'DD/MM/RRRR') AS "WO Actual Finish Date",
T636094.OUTAGE_REQUIRED AS "Outage Required",
T636094.ASSET_NUMBER AS "Asset Number"
FROM REP_WORP_WO_DETAIL_V T636094
WHERE (
(T636094.VALID_TO >= @{ASATDATE-1}{(TO_DATE (TO_CHAR (TRUNC (SYSDATE), 'YYYY-MM-DD'),'YYYY-MM-DD')-1)}
AND T636094.WORK_TYPE_CODE IN (@{p_WORKTYPE.isAllColumnValues?NULL:p_WORKTYPE}['@']{"T636094"."WORK_TYPE_CODE"})
AND T636094.WORK_ORDER_STATUS IN (@{p_STATUS.isAllColumnValues?NULL:p_STATUS}['@']{"T636094"."WORK_ORDER_STATUS"})
AND T636094.OUTAGE_REQUIRED IN (@{OUTAGE.isAllColumnValues?NULL:OUTAGE}['@']{"T636094"."OUTAGE_REQUIRED"})
AND T636094.REGION IN (@{p_REGION.isAllColumnValues?NULL:p_REGION}['@']{"T636094"."REGION"})
AND T636094.SITE IN (@{p_dd_Site.isAllColumnValues?NULL:p_dd_Site}['@']{"T636094"."SITE"})
AND T636094.CALC_PRIORITY_RISK IN (@{p_dd_PriorityRiskGroup.isAllColumnValues?NULL:p_dd_PriorityRiskGroup}['@']{"T636094"."CALC_PRIORITY_RISK"})
AND T636094.SERVICE_AREA IN (@{p_SERVICEAREA.isAllColumnValues?NULL:p_SERVICEAREA}['@']{"T636094"."SERVICE_AREA"})
AND T636094.ACTIVITY_CODE IN (@{ACT_TYPE.isAllColumnValues?NULL:ACT_TYPE}['@']{"T636094"."ACTIVITY_CODE"})
AND T636094.WORK_ORDER_STATUS_COMP_DT IN (@{p_dd_StatusGroup.isAllColumnValues?NULL:p_dd_StatusGroup}['@']{"T636094"."WORK_ORDER_STATUS_COMP_DT"})
AND T636094.LATEST_FLAG = 'Y'
AND T636094.VALID_FROM <= @{ASATDATE-1}{(TO_DATE(TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD'),'YYYY-MM-DD')-1)})))
The VALID_FROM and VALID_TO are the timestamp columns.
Here ASATDATE is a presentation prompts. There is a dashboard prompt 'ASATDATE' which is getting pass through the above DDR Query. I have changed the the VALID_FROM AND VALID_TO filter to
@{ASATDATE}{(TO_DATE(TO_CHAR (TRUNC (SYSDATE), 'YYYY-MM-DD'),'YYYY-MM-DD')) so it filters for back date date also.
But currently its erroring out as shown below.
But I am getting inconsitent datatypes: expected timestamp got number at OCI Error.
Appreciate your help in resolving this error.
Regards
|
|
|
Re: OBIEE DDR Query 12c [message #682349 is a reply to message #682348] |
Sat, 17 October 2020 19:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 Registered: March 2008 Location: India
|
Senior Member |
|
|
Hello Team,
After changing the date format I am getting error as shown below
Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.(HY000)
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS.(HY000)
State: HY000. Code: 43093. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement.(HY000)
State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 932, message: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBERat OCI call OCIStmtExecute.(HY000)
State: HY000. Code: 17010. [nQSError: 17010] SQL statement preparation failed. (HY000)
SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "data warehouse" (SELECT /*+ PARALLEL(16) */DISTINCTT636094.REGION AS "Region Reporting",T636094.SERVICE_AREA AS "Service Area",T636094.SITE AS "Site",T636094.DEVICE_POSITION AS "Device Position",T636094.ASSET_TYPE_DESCRIPTION AS "Asset Type Description",T636094.WORK_ORDER AS "Work Order",T636094.WORK_ORDER_DESCRIPTION AS "Work Orer Description",T636094.LONG_DESCRIPTION AS "Long Description",T636094.WORK_TYPE_CODE AS "Work Type Code",T636094.ACTIVITY_CODE AS "Activity Code",T636094.CALC_PRIORITY_RISK AS "Risk Assessment Group",T636094.PRIORITY_RISK AS "Risk Assessment",T636094.PRIORITY_JUSTIFICATION AS "Priority Justification",T636094.WORK_ORDER_PRIORTY AS "Work Order Priority",T636094.WORK_ORDER_STATUS AS "Work Order Status",TO_DATE(T636094.ACTUAL_DT_REPORT_DT,''DD/MM/RRRR'') AS "WO Reported Date",TO_DATE(T636094.ACTUAL_DT_TAR_ST_DT,''DD/MM/RRRR'') AS "WO Target Start Date",TO_DATE(T636094.ACTUAL_DT_TAR_FIN_DT,''DD/MM/RRRR'') AS "WO Target Finish Date",TO_DATE(T636094.ACTUAL_DT_SCHD_START_DT,''DD/MM/RRRR'') AS "WO Scheduled Start Date",TO_DATE(T636094.ACTUAL_DT_SCHD_FIN_DT,''DD/MM/RRRR'') AS "WO Scheduled Finish Date", TO_DATE(T636094.ACTUAL_START_DATE,''DD/MM/RRRR'') AS "WO Actual Start Date", TO_DATE(T636094.ACTUAL_FINISH_DATE,''DD/MM/RRRR'') AS "WO Actual Finish Date",T636094.OUTAGE_REQUIRED AS "Outage Required",T636094.ASSET_NUMBER AS "Asset Number"FROM REP_WORP_WO_DETAIL_V T636094WHERE ( (T636094.VALID_TO >= 01/01/2020AND T636094.WORK_TYPE_CODE IN (''PDM'')AND T636094.WORK_ORDER_STATUS IN (''APPR'',''NEW'',''PLANNED'',''VALID'',''WAPR'',''WVALID'',''WAPPR'')AND T636094.OUTAGE_REQUIRED IN ("T636094"."OUTAGE_REQUIRED")AND T636094.REGION IN ("T636094"."REGION")AND T636094.SITE IN ("T636094"."SITE")AND T636094.CALC_PRIORITY_RISK IN ("T636094"."CALC_PRIORITY_RISK")AND T636094.SERVICE_AREA IN ("T636094"."SERVICE_AREA")AND T636094.ACTIVITY_CODE IN ("T636094"."ACTIVITY_CODE")AND T636094.WORK_ORDER_STATUS_COMP_DT IN (''Active'')AND T636094.LATEST_FLAG = ''Y'' AND T636094.VALID_FROM <= 01/01/2020)))', 'PRECISION_SCALE')}
Appreciate your help in the solution for this error.
|
|
|
|
Re: OBIEE DDR Query 12c [message #682351 is a reply to message #682348] |
Sun, 18 October 2020 06:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Why are you doing this:TO_DATE(TO_CHAR (TRUNC (SYSDATE), 'YYYY-MM-DD'),'YYYY-MM-DD') Stripping off the hours-minutes-seconds, sure - but what is the point in converting the result to a string and then back to a date?
Furthermore, if VALID_FROM is a timestamp, surely all you need is
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:13:30 CDT 2024
|