Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

Received Response
11
Views
5
Comments
3125369
3125369 Rank 1 - Community Starter
in Oracle Business Intelligence Applications

Hello

I am new at OBI. I am creating a report and I am getting the next error

Odbc driver returned an error (SQLExecDirectW).

  Error Details

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. Please have your System Administrator look at the log for more details on this error. (HY000)

Please have your System Administrator look at the log for more details on this error.

This happens once I used a formula in two columns.I have columns A and B, they are very similar, almost the same except for one value ("Submission Dates"."Offer Accepted Date" against  "Submission Dates"."Hire Start Date").

Column A

(SUM(SUM(CASE WHEN "Requisition Status - Current"."Current Status" = 'Filled' THEN (TIMESTAMPDIFF(SQL_TSI_DAY,(CASE WHEN "Requisition Identification"."Req. Identifier" IN ('1004932','1004933'','1100495') THEN TO_DATETIME('2010-01-01','yyyy-mm-dd') ELSE "Requisition Dates"."Req. Creation Date" END), "Submission Dates"."Offer Accepted Date")+1)-"Requisition Aging"."Time On Hold Since Creation" END) BY "Requisition Identification"."Req. Identifier"))/(CASE WHEN "Submission CSW Status - Current"."Current Status Name" = 'Cleared for Hire/Proceed to OnBoarding'THEN COUNT("Submission General Info"."Submission Identifier") END)

Column B

(SUM(SUM(CASE WHEN "Requisition Status - Current"."Current Status" = 'Filled' THEN (TIMESTAMPDIFF(SQL_TSI_DAY,(CASE WHEN "Requisition Identification"."Req. Identifier" IN ('1004932','1004933'','1100495') THEN TO_DATETIME('2010-01-01','yyyy-mm-dd') ELSE "Requisition Dates"."Req. Creation Date" END), "Submission Dates"."Hire Start Date")+1)-"Requisition Aging"."Time On Hold Since Creation" END) BY "Requisition Identification"."Req. Identifier"))/(CASE WHEN "Submission CSW Status - Current"."Current Status Name" = 'Cleared for Hire/Proceed to OnBoarding'THEN COUNT("Submission General Info"."Submission Identifier") END)

If I run the report with those formulas the report runs, but without results. If I change the last part of the formula after the /  and put this :

(SUM(COUNT(CASE WHEN "Submission CSW Status - Current"."Current Status Name" = 'Cleared for Hire/Proceed to OnBoarding' THEN "Submission General Info"."Submission Identifier" END) BY "Submission General Info"."Submission Identifier" )) it runs and I get results, but only If a put that in one column. If I want to put that in column A and B at the same time it will give me the error you saw at the begging.

Answers

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    You need to look at

    TIMESTAMPDIFF(SQL_TSI_DAY,

    (CASE WHEN "Requisition Identification"."Req. Identifier" IN ('1004932','1004933'','1100495') THEN

    TO_DATETIME('2010-01-01','yyyy-mm-dd') ELSE "Requisition Dates"."Req. Creation Date" END), "Submission Dates"."Offer Accepted Date")+1)

    validate this first

  • 3125369
    3125369 Rank 1 - Community Starter

    I validate that and It works, alone. Actually what it is wear is that If I put the formula of column A in both columns (A and B), it shows me the same error. Which is weird because I am applying the same formula in the the two columns.

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Whats the return type from this expression? looks like date !!

    DateType/NumberType !!

    (SUM(SUM(CASE WHEN "Requisition Status - Current"."Current Status" = 'Filled' THEN (TIMESTAMPDIFF(SQL_TSI_DAY,(CASE WHEN "Requisition Identification"."Req. Identifier" IN ('1004932','1004933'','1100495') THEN TO_DATETIME('2010-01-01','yyyy-mm-dd') ELSE "Requisition Dates"."Req. Creation Date" END), "Submission Dates"."Offer Accepted Date")+1)-"Requisition Aging"."Time On Hold Since Creation" END) BY "Requisition Identification"."Req. Identifier"))

  • 3125369
    3125369 Rank 1 - Community Starter

    The return is a normal number. Like 240 or 235.

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    hmm i dont think so

    can you explain by splitting the function?