Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

obiee 12c producing error message I can't find an answer for

Closed
428
Views
9
Comments
Big_Z
Big_Z Rank 2 - Community Beginner
edited Apr 28, 2025 5:55PM in Oracle Analytics Cloud and Server

OAMP2OPY: KNYNDVB7

I run my analysis and get this error.  I can find the first have but not the second half.  It seems to have something to do with a patch we applied in our DEV environment.  The same Analysis works find in TEST and PROD.

Error
View Display Error

Error generating view. Error getting cursor in GenerateHead

  Error Details

Error Codes: OAMP2OPY:KNYNDVB7

Invalid SQL Identifier: ( SELECT year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date") s_1, Count(distinct "UI - OP Segment Subject Area"."OP Segment Information"."SSN" BY year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date")) s_2, sum("UI - OP Segment Subject Area"."OP Segment Accounts Receivable Information"."Total Accounts Receivable Amount" BY year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date")) s_3, sum("UI - OP Segment Subject Area"."OP Segment Balance Due Information"."Total Owed to OED Amount" BY year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date")) s_4, sum("UI - OP Segment Subject Area"."OP Segment Balance Due Information"."Total Owed to OED Amount" by year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date"))/CAST(sum("UI - OP Segment Subject Area"."OP Segment Accounts Receivable Information"."Total Accounts Receivable Amount" BY year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date")) as double)*100 s_5 FROM "UI - OP Segment Subject Area" WHERE (("OP Segment Information"."Decision Date" <> date '1900-01-01') AND ("OP Segment Codes and Flags"."OP Type Code" <> 'B') AND ("OP Segment Codes and Flags"."OP Status Description" = 'ACTIVE') AND ("OP Segment Codes and Flags"."OP Cause Code" IN ('4', '5')) AND (year("OP Segment Information"."Finality Date") >= 1998)) )SubjectArea1 INNER JOIN ( SELECT year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date") s_1, Count(distinct "UI - OP Segment Subject Area"."OP Segment Information"."SSN" BY year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date")) s_2, REPORT_SUM(Count(distinct "UI - OP Segment Subject Area"."OP Segment Information"."SSN" BY year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date")) BY ) s_3, REPORT_SUM(sum("UI - OP Segment Subject Area"."OP Segment Transaction Information"."Amount of Payment Received"+"UI - OP Segment Subject Area"."OP Segment Transaction Information"."Amount of Principal Offset" BY year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date")) BY ) s_4, sum("UI - OP Segment Subject Area"."OP Segment Transaction Information"."Amount of Payment Received"+"UI - OP Segment Subject Area"."OP Segment Transaction Information"."Amount of Principal Offset" BY year("UI - OP Segment Subject Area"."OP Segment Information"."Finality Date")) s_5 FROM "UI - OP Segment Subject Area" WHERE (("OP Segment Information"."Decision Date" <> date '1900-01-01') AND ("OP Segment Codes and Flags"."OP Type Code" <> 'B') AND ("OP Segment Codes and Flags"."OP Status Description" = 'ACTIVE') AND ("OP Segment Codes and Flags"."OP Cause Code" IN ('4', '5')) AND (year("OP Segment Information"."Finality Date") >= 1998)) ) SubjectArea2 ON SubjectArea1.s_1 = SubjectArea2.s_1.

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    "Invalid SQL Identifier" sounds like you're referencing something inexistant. Are both environments running the same RPD?

    And how did this LSQL come to be?

  • Joel
    Joel Rank 8 - Analytics Strategist

    What happens if you pull the physical SQL generated by the BI Server out of the NQquery.log file and run the SQL directly on your database?

  • Saresh-Oracle
    Saresh-Oracle Rank 5 - Community Champion

    Hi ,

    Which version of OBIEE are you using?

    Please upload the opatch lsinventory details from Dev, Test and Production environment.

    Thanks,

    Sareesh

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    I'm curious. Over on MOSC asking for more detailed information gets posts deleted and here we're now asking for opatch lsinventory for all environments?

    It would be nice to get a clear picture of the rules which are being applied.

  • Saresh-Oracle
    Saresh-Oracle Rank 5 - Community Champion

    Hi,

    This is to compare the patches applied on test dev and Prod.

    Thanks,

    Sareesh

  • Big_Z
    Big_Z Rank 2 - Community Beginner

    This is the what I get when I run a working Analysis in 11g that was upgraded to 12c. These are written on the advanced tab so we can do multiple joins that are not in the RPD. I've been working on this for months with Oracle support and we are getting nowhere.

  • Big_Z
    Big_Z Rank 2 - Community Beginner

    "Invalid SQL Identifier"

    It loses the outer most query. Below is the code on the advanced tab. It seems to not be able to find the outer select in 12c, works fine in 11g.

    SELECT count(distinct SubjectArea1.SSN) saw_0 FROM (SELECT

          "Claim Information"."SSN" SSN,

          "Claim Information"."BYE" BYE,

          "Claim Codes and Flags"."Alternate Base Year Code" ABY,

          "Claim Information"."Claim Date" ClaimDate,

          "Claim Information"."Claim Date Quarter" ClaimQtr,

          "Claim Information"."Claim Date Year" ClaimYear


       FROM "UI - Claim Subject Area"


       WHERE

          ("Claim Information"."BYE" >= (

             Case when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) >= 4

             AND week(current_date) <> 1

             AND week(current_date) BETWEEN 2 AND 10 then cast(year(current_date) as char (4))||'0'||cast((week(current_date)-1) as char (1))

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) >= 4

             AND week(current_date) <> 1

             AND week(current_date) >= 11 then cast(year(current_date) as char (4))||cast((week(current_date)-1) as char (2))

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) >= 4

             AND week(current_date) = 1 then cast((year(current_date)-1) as char (4))||cast('53' as char (2))

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) < 4

             AND week(current_date) > 9 then cast(year(current_date) as char (4))||cast(week(current_date) as char (2))     

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) < 4

             AND week(current_date) < 10 then cast(year(current_date) as char (4))||'0'||cast(week(current_date) as char (1))   

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) < 4

             AND week(current_date) = 1 then cast(year(current_date) as char (4))||'52'      

             END))

          AND ("Claim Information"."BYE" < '666666')

          AND ("Claim Codes and Flags"."Claim Status Code" <> 'C')




    ) SubjectArea1 INNER JOIN (SELECT

          "Wage Customer Information"."SSN" SSN,

          "Wage Information"."Quarter End Date" Qtr


       FROM "Wage Subject Area"


       WHERE

          ("Wage Employer Codes and Flags"."Employer Type Code" IN ('06','08','26','28','38','48'))

          AND ("Wage Codes and Flags"."Wages on Mainframe Flag" = 'Y')


    ) SubjectArea2 ON SubjectArea1.SSN = SubjectArea2.SSN LEFT OUTER JOIN (SELECT

          "Claim Information"."SSN" SSN,

          "Claim Information"."BYE" BYE,

          "Line Flag Codes and Flags"."Line Flag Code" LineFlag,

          "Line Flag Codes and Flags"."Line Flag Description" LFDesc



       FROM "UI - Claim Subject Area"


       WHERE

          ("Claim Information"."BYE" >= (

             Case when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) >= 4

             AND week(current_date) <> 1

             AND week(current_date) BETWEEN 2 AND 10 then cast(year(current_date) as char (4))||'0'||cast((week(current_date)-1) as char (1))

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) >= 4

             AND week(current_date) <> 1

             AND week(current_date) >= 11 then cast(year(current_date) as char (4))||cast((week(current_date)-1) as char (2))

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) >= 4

             AND week(current_date) = 1 then cast((year(current_date)-1) as char (4))||cast('53' as char (2))

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) < 4

             AND week(current_date) > 9 then cast(year(current_date) as char (4))||cast(week(current_date) as char (2))     

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) < 4

             AND week(current_date) < 10 then cast(year(current_date) as char (4))||'0'||cast(week(current_date) as char (1))   

             when dayofweek(to_datetime(('01/01/'||cast(year(current_date) as char (4))), 'dd/mm/yyyy')) < 4

             AND week(current_date) = 1 then cast(year(current_date) as char (4))||'52'      

             END))   

          AND (("Line Flag Codes and Flags"."Line Flag Code" = '09')

          OR ("Line Flag Codes and Flags"."Line Flag Code" LIKE '%09%'))   



    )SubjectArea3 ON SubjectArea1.SSN = SubjectArea3.SSN AND SubjectArea1.BYE = SubjectArea3.BYE WHERE (SubjectArea3.LineFlag IS NULL

          OR SubjectArea3.LFDesc IS NULL) AND(timestampdiff(sql_tsi_month, SubjectArea2.Qtr,

             (CASE WHEN SubjectArea1.ClaimQtr='1'   

                THEN TO_DATETIME('03/31/' ||CAST (SubjectArea1.ClaimYear AS CHAR), 'mm/dd/yyyy')  

                WHEN SubjectArea1.ClaimQtr='2'  

                THEN TO_DATETIME('06/30/' ||CAST (SubjectArea1.ClaimYear AS CHAR), 'mm/dd/yyyy')     

                WHEN SubjectArea1.ClaimQtr='3' 

                THEN TO_DATETIME('09/30/' ||CAST (SubjectArea1.ClaimYear AS CHAR), 'mm/dd/yyyy')

                WHEN SubjectArea1.ClaimQtr='4' 

                THEN TO_DATETIME('12/31/' ||CAST (SubjectArea1.ClaimYear AS CHAR), 'mm/dd/yyyy')    

                END))

          BETWEEN

             (CASE WHEN (SubjectArea1.ABY)='Y'THEN 3

                WHEN (SubjectArea1.ABY)<>'Y' THEN 6

                END)

             AND (CASE WHEN (SubjectArea1.ABY)='Y' THEN 12

                   WHEN (SubjectArea1.ABY)<>'Y' THEN 15

                   END))

  • User_RPCJ4
    User_RPCJ4 Rank 1 - Community Starter

    Hi Guys,

    I am having the same issue. This is the SQL query that I've tried to run and got the below error :

    select SA1.LOC LOC from (SELECT "GL entry Attributes"."LOCATIONNAME" as LOC FROM "gl:GL Entry") SA1


    Error Codes: OAMP2OPY:KNYNDVB7

    Invalid SQL Identifier: (SELECT "GL entry Attributes"."LOCATIONNAME" as ADI FROM "gl:GL Entry") SA1.


    I want to mention that if I am running only the subquery it works fine:

    SELECT "GL entry Attributes"."LOCATIONNAME" as LOC FROM "gl:GL Entry"


    obiee version: 12.2.1.4.0

    Any ideas ?

    Thanks

  • User_7IS6P
    User_7IS6P Rank 1 - Community Starter

    is this issue got resolved? I am also getting the same error. Please let me know

This discussion has been closed.