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
manoj12
Messages: 207
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 messageGo to next message
manoj12
Messages: 207
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 #682350 is a reply to message #682348] Sun, 18 October 2020 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67549
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 12 November 2019 07:39

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

BlackSwan wrote on Fri, 06 February 2015 17:03

You should post follow up so others can benefit from your problem resolution.
You should post follow up because Posting Guidelines say you should be a Good Neighbor.
You should post follow up to thank those who assisted you in resolving your issue.
Re: OBIEE DDR Query 12c [message #682351 is a reply to message #682348] Sun, 18 October 2020 06:55 Go to previous messageGo to next message
John Watson
Messages: 8428
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
trunc(systimestamp)
Re: OBIEE DDR Query 12c [message #682394 is a reply to message #682351] Wed, 21 October 2020 13:37 Go to previous message
manoj12
Messages: 207
Registered: March 2008
Location: India
Senior Member
Hello Sir,

Thank you very much.

The issue is resolved after converting the below date to TRUNC(SYSTIMESTAMP).

Regards,
Previous Topic: how to recepit the last page number on bi*publisher template
Next Topic: Dynamic title in OAC Reports
Goto Forum:
  


Current Time: Mon Nov 30 14:41:20 CST 2020