Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

timestamp problem

844748Mar 9 2011

Hai All,

Recently we went to upgrade sieble analytics 7.8 to obiee 10.1.3... .now we are faceing problem with timestamp function. in sieble it is timestamp,the sql query is going to round function and in obiee timestampdiff,the sql query is going to trunc function.now the reslts giving mismatch. plz find sql query.

obiee query

select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13,
D1.c14 as c14,
D1.c15 as c15,
D1.c16 as c16,
D1.c17 as c17,
D1.c18 as c18,
D1.c19 as c19,
D1.c20 as c20
from
(select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13,
D1.c14 as c14,
D1.c15 as c15,
D1.c16 as c16,
D1.c17 as c17,
D1.c18 as c18,
D1.c19 as c19,
D1.c20 as c20
from
(select T96010.SR_NUM as c1,
T385052.X_CONCAT_FULL_NAME as c2,
T12484.FULL_NAME as c3,
T96010.X_ATTRIB_40 as c4,
T96010.X_COMM_METHOD_CD as c5,
T96010.SUB_STATUS as c6,
T96010.AREA as c7,
T96010.SUB_AREA as c8,
T96010.X_ATTRIB_06 as c9,
T96010.X_ATTRIB_04 as c10,
case T96010.STATUS when 'Open' then ( ( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - ( (T96010.OPEN_DT) ) ) end as c11,
TRUNC(T96010.OPEN_DT) as c12,
case when case T96010.STATUS when 'Open' then (( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - ( (T96010.OPEN_DT) ) ) end < 4 then 'A: 0-3 Days' when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end <= 7 and case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end >= 4 then 'B: 4-7 Days' when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end <= 15 and case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end > 7 then 'C: 8-15 Days' else 'D: > 15 Days' end as c13,
T96010.X_DESC_TEXT as c14,
T96010.X_ATTRIB_47 as c15,
count(distinct case when T96010.TYPE_CD = 'Complaint' then T12478.SR_WID end ) as c16,
T12518.X_SLS_REGION as c17,
T12518.ORG_NAME as c18,
T12484.INTEGRATION_ID as c19,
T96010.ROW_WID as c20,
ROW_NUMBER() OVER (PARTITION BY T12484.INTEGRATION_ID, T12484.FULL_NAME, T12518.ORG_NAME, T12518.X_SLS_REGION, T96010.ROW_WID, T96010.AREA, T96010.SR_NUM, T96010.SUB_AREA, T96010.SUB_STATUS, T96010.X_ATTRIB_40, T96010.X_ATTRIB_47, T96010.X_COMM_METHOD_CD, T96010.X_ATTRIB_04, T96010.X_ATTRIB_06, T96010.X_DESC_TEXT, T385052.X_CONCAT_FULL_NAME, case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end , case when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end < 4 then 'A: 0-3 Days' when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end <= 7 and case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end >= 4 then 'B: 4-7 Days' when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end <= 15 and case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end > 7 then 'C: 8-15 Days' else 'D: > 15 Days' end ORDER BY T12484.INTEGRATION_ID ASC, T12484.FULL_NAME ASC, T12518.ORG_NAME ASC, T12518.X_SLS_REGION ASC, T96010.ROW_WID ASC, T96010.AREA ASC, T96010.SR_NUM ASC, T96010.SUB_AREA ASC, T96010.SUB_STATUS ASC, T96010.X_ATTRIB_40 ASC, T96010.X_ATTRIB_47 ASC, T96010.X_COMM_METHOD_CD ASC, T96010.X_ATTRIB_04 ASC, T96010.X_ATTRIB_06 ASC, T96010.X_DESC_TEXT ASC, T385052.X_CONCAT_FULL_NAME ASC, case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end ASC, case when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end < 4 then 'A: 0-3 Days' when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end <= 7 and case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end >= 4 then 'B: 4-7 Days' when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end <= 15 and case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end > 7 then 'C: 8-15 Days' else 'D: > 15 Days' end ASC) as c21
from
W_PERSON_D T385052 /* Complaint Contact(W_PERSON_D) */ ,
W_INT_ORG_D T37817,
W_ORG_D T12518 /* Billed_Account (W_ORG_D) */ ,
W_EMPLOYEE_D T12484,
W_SRVREQ_F T12478,
WC_SRVREQ_COMPLAINT_D T96010
where ( T12478.CONTACT_WID = T385052.ROW_WID and T12478.EMPLOYEE_WID = T12484.ROW_WID and T12478.PR_VIS_ORG_WID = T37817.ROW_WID
and T12478.SR_WID = T96010.ROW_WID and T12478.SRV_PROV_OU_WID = T12518.ROW_WID and T12484.EMP_FLG = 'Y' and T37817.BU_NAME = 'TMPC'
and T37817.X_MARKET_CLASS = 'National' and T96010.STATUS = 'Open' and T96010.X_COMM_METHOD_CD = 'PSF Feedback'
and -1 < T96010.ROW_WID and 3 < case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end and T96010.AREA not like '%Sales%' )
group by T12484.INTEGRATION_ID, T12484.FULL_NAME, T12518.ORG_NAME, T12518.X_SLS_REGION, T96010.ROW_WID, T96010.AREA, T96010.SR_NUM, T96010.SUB_AREA, T96010.SUB_STATUS, T96010.X_ATTRIB_40, T96010.X_ATTRIB_47, T96010.X_COMM_METHOD_CD, T96010.X_ATTRIB_04, T96010.X_ATTRIB_06, T96010.X_DESC_TEXT, T385052.X_CONCAT_FULL_NAME, case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end , case when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end < 4 then 'A: 0-3 Days' when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end <= 7 and case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end >= 4 then 'B: 4-7 Days' when case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end <= 15 and case T96010.STATUS when 'Open' then ( TRUNC( cast(TO_DATE('2011-03-08' , 'YYYY-MM-DD') as DATE) ) - TRUNC( TRUNC(T96010.OPEN_DT) ) ) end > 7 then 'C: 8-15 Days' else 'D: > 15 Days' end , TRUNC(T96010.OPEN_DT)
) D1
where ( D1.c21 = 1 )
) D1
order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c17, c18

siebel analytics qurey

select distinct D1.c2 as c1,
D1.c3 as c2,
D1.c4 as c3,
D1.c5 as c4,
D1.c6 as c5,
D1.c7 as c6,
D1.c8 as c7,
D1.c9 as c8,
D1.c10 as c9,
D1.c11 as c10,
D1.c12 as c11,
D1.c13 as c12,
D1.c14 as c13,
D1.c15 as c14,
D1.c16 as c15,
D1.c1 as c16,
D1.c17 as c17,
D1.c18 as c18,
D3.c4 as c19,
D2.c21 as c20
from
(select count(distinct case when T96010.TYPE_CD = 'Complaint' then T12478.SR_WID end ) as c1,
T96010.SR_NUM as c2,
T385052.X_CONCAT_FULL_NAME as c3,
T12484.FULL_NAME as c4,
T96010.X_ATTRIB_40 as c5,
T96010.X_COMM_METHOD_CD as c6,
T96010.SUB_STATUS as c7,
T96010.AREA as c8,
T96010.SUB_AREA as c9,
T96010.X_ATTRIB_06 as c10,
T96010.X_ATTRIB_04 as c11,
case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end as c12,
TO_DATE(TO_CHAR(T96010.OPEN_DT, 'YYYY-MM-DD'), 'YYYY-MM-DD') as c13,
case when case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end < 4 then 'A: 0-3 Days' when case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end <= 7 and case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end >= 4 then 'B: 4-7 Days' when case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end <= 15 and case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end > 7 then 'C: 8-15 Days' else 'D: > 15 Days' end as c14,
T96010.X_DESC_TEXT as c15,
T96010.X_ATTRIB_47 as c16,
T12518.X_SLS_REGION as c17,
T12518.ORG_NAME as c18,
T12484.INTEGRATION_ID as c19,
T96010.ROW_WID as c20
from
W_PERSON_D T385052 /* Complaint Contact(W_PERSON_D) */ ,
W_INT_ORG_D T37817,
W_ORG_D T12518 /* Billed_Account (W_ORG_D) */ ,
W_EMPLOYEE_D T12484,
W_SRVREQ_F T12478,
WC_SRVREQ_COMPLAINT_D T96010
where ( -1 < T96010.ROW_WID and T12484.EMP_FLG = 'Y' and 3 < case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end and T96010.STATUS = 'Open' and T96010.X_COMM_METHOD_CD = 'PSF Feedback' and T37817.BU_NAME = 'TMPC' and T37817.X_MARKET_CLASS = 'National' and T12478.PR_VIS_ORG_WID = T37817.ROW_WID and T12478.CONTACT_WID = T385052.ROW_WID and T12478.SRV_PROV_OU_WID = T12518.ROW_WID and T12478.EMPLOYEE_WID = T12484.ROW_WID and T12478.SR_WID = T96010.ROW_WID )
group by T12484.INTEGRATION_ID, T12484.FULL_NAME, T12518.ORG_NAME, T12518.X_SLS_REGION, T96010.ROW_WID, T96010.AREA, T96010.SR_NUM, T96010.SUB_AREA, T96010.SUB_STATUS, T96010.X_ATTRIB_40, T96010.X_ATTRIB_47, T96010.X_COMM_METHOD_CD, T96010.X_ATTRIB_04, T96010.X_ATTRIB_06, T96010.X_DESC_TEXT, T385052.X_CONCAT_FULL_NAME, case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end , case when case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end < 4 then 'A: 0-3 Days' when case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end <= 7 and case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end >= 4 then 'B: 4-7 Days' when case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end <= 15 and case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end > 7 then 'C: 8-15 Days' else 'D: > 15 Days' end , TO_DATE(TO_CHAR(T96010.OPEN_DT, 'YYYY-MM-DD'), 'YYYY-MM-DD')
) D1,

(select count(distinct case when T96010.TYPE_CD = 'Complaint' then T12478.SR_WID end ) as c21,
T96010.ROW_WID as c22,
T385052.X_CONCAT_FULL_NAME as c23
from
W_PERSON_D T385052 /* Complaint Contact(W_PERSON_D) */ ,
W_INT_ORG_D T37817,
W_EMPLOYEE_D T12484,
W_SRVREQ_F T12478,
WC_SRVREQ_COMPLAINT_D T96010
where ( -1 < T96010.ROW_WID and T12484.EMP_FLG = 'Y' and 3 < case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end
and T96010.STATUS = 'Open' and T96010.X_COMM_METHOD_CD = 'PSF Feedback' and T37817.BU_NAME = 'TMPC' and T37817.X_MARKET_CLASS = 'National'
and T12478.PR_VIS_ORG_WID = T37817.ROW_WID and T12478.CONTACT_WID = T385052.ROW_WID and T12478.EMPLOYEE_WID = T12484.ROW_WID
and T12478.SR_WID = T96010.ROW_WID )
group by T96010.ROW_WID, T385052.X_CONCAT_FULL_NAME
) D2,

(select count(distinct case when T96010.TYPE_CD = 'Complaint' then T12478.SR_WID end ) as c4
from
W_INT_ORG_D T37817,
W_EMPLOYEE_D T12484,
W_SRVREQ_F T12478,
WC_SRVREQ_COMPLAINT_D T96010
where ( -1 < T96010.ROW_WID and T12484.EMP_FLG = 'Y' and 3 < case T96010.STATUS when 'Open' then ROUND( TO_DATE('2011-03-08' , 'YYYY-MM-DD') - T96010.OPEN_DT ) end
and T96010.STATUS = 'Open' and T96010.X_COMM_METHOD_CD = 'PSF Feedback' and T37817.BU_NAME = 'TMPC' and T37817.X_MARKET_CLASS = 'National'
and T12478.PR_VIS_ORG_WID = T37817.ROW_WID and T12478.EMPLOYEE_WID = T12484.ROW_WID and T12478.SR_WID = T96010.ROW_WID )
) D3
where ( D1.c20 = D2.c22 and (D1.c3 = D2.c23 or (D1.c3 is null and D2.c23 is null)) )
order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c13, c14, c15, c17, c18

Regards,

Ram

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 6 2011

Added on Mar 9 2011

#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee

0 comments

155 views