nqserver log - syntax errors

Question

Hi All -

I was looking through our nqserver log recently, and noticed that there were quite a few "syntax" errors in it. I can see that the all seem to be for the same thing. Here is an example:

[2017-10-17T09:18:58.322-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 005MvrrE6v99Tc85vjl3iX0007TX000Ro1,0:1:1:13] [tid: 1deb6700]  [nQSError: 27002] Near <)>: Syntax error [nQSError: 26012] . [[^M
file: server/Metadata/NQSParser/Src/NQSPScanner.cpp; line: 2321^M

**********  Task: 1. Running for (mls): 0 **********
Description: Execute logical request
User: {user_id}
SessionID: 1161428992
ProcedureID: 113
SQL Hash: 0x989ee0fc
SQL::{call SAGetSharedRequestKey('SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name")')}
^M
]]

I'm trying to find out exactly what this means, and if we should be concerned with it. The consultants who performed this work are no longer here, but I can retrieve what is necessary to help troubleshoot.

Thanks in advance ...

Regards,

Charles

OBIA 11.1.1.10.1 / OBIEE 11.1.1.9.3

Charles M · Answer

Hi Gianni,

You are correct. This is coming from our dashboards. We have 2 custom dashboards that I can see, which are using this prompt.

The first one(all 4 tabs) defaults to an "all column" value. Like this:

The second one (4 of 5 tabs) default to that second value in the list. Like this:

The last tab is different. It does not have the "all columns" value, but still defaults to the second value in the list. Like this:

Regards,

Charles

Gianni Ceresa · Answer

Hi, in your log you have the following:

SQL: SET VARIABLE QUERY_SRC_CD='DashboardPrompt';SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name")

Looks like the query generating the issue from a dashboard prompt object. One possible option is that you have a dashboard prompt which try to set a default value to the minimum of "Payables Invoicing Business Unit Name", which could match with the first value of the prompt (assuming it's the name of the BU). It's possible they did a mistake in the syntax of the "default value" LSQL and that's the source of the issue. Of course you don't really see it because the prompt still work and probably set the default value to the first one "by default" even if the query entered generate an error.

Charles M · Answer

Hi Sherry, Thank for the reply. Some good info there. I ran an analysis with that subject area object, but didn't get any error from the front end: Here is the 'logical SQL' I found for it (using Fusion Applications: How to set up and access the nqquery.log to troubleshoot a single OTBI or OBIA query (Doc ID 1469168.1)): SELECT 0 s_0, "Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name" s_1 FROM "Financials - AP Transactions" ORDER BY 1, 2 ASC NULLS LAST FETCH FIRST 500001 ROWS ONLY I can see the errors in the nqserver log, as follows (some other entries there ... I'm not sure if they are completely relevant here, but posting all for context): [2017-10-17T13:56:18.933-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: bb3727d68120b54d:270c056:15f2ac45751:-8000-0000000000001752,0:1:5:1] [tid: 57ca4700] [nQSError: 17014] Could not connect to Oracle database. [[^M file: server/Query/Execution/DbGateway/Oci10g/../Oci8/Src/SQXDGOci8.cpp; line: 373^M Properties: description=<<1057772>> DbGateway Exchange: Oracle BI Application Configuration.Oracle BI Application Configuration Connection Pool; producerID=139664722025384; requestID=3011182596; sessionID=3011182592; userName={USER}; ^M [nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach.^M ********** Task: 1. Running for (mls): 404 ********** Description: DB Connect DSN: CM_DSN; userName=CM_USER ********** Task: 2. Running for (mls): 404 ********** Description: <<1057736>>DbGateway Prepare DSN:Oracle BI Application Configuration Connection Pool;userName:CM_USER SQL: select D1.c3 as c1, '' as c2, D1.c2 as c3, 'column' as c4, D1.c1 as c5 from (select distinct T2746832.MULTI_CURRENCY_SUPPORT_FLG as c1, T2746833.CURR_DISPLAY_NAME as c2, T2746832.CURR_PREFERENCE_KEY as c3 from C_BIA_CURR_PREF_OFRNG_REL T2746830 /* Fact_C_BIA_CURR_PREF_OFRNG_REL */ , C_BIA_CURRENCY_PREFERENCE T2746832 /* Fact_C_BIA_CURRENCY_PREFERENCE */ , C_BIA_CURRENCY_PREFERENCE_TL T2746833 /* Fact_C_BIA_CURRENCY_PREFERENCE_TL */ where ( T2746830.CURR_PREFERENCE_KEY = T2746832.CURR_PREFERENCE_KEY and T2746830.CURR_PREFERENCE_KEY = T2746833.CURR_PREFERENCE_KEY and T2746833.LANGUAGE_CODE = 'US' ) ) D1 order by c3 ********** Task: 3. Running for (mls): 404 ********** Description: Producer::ExecuteOnce() Activity type:Producer::ExecuteOnce() Repository Name::Star Subject Area Name::Core User Name::{user} Logical Hash of SQL:: 0x44bfb7d0 SQL:: SELECT "Dynamic Currency Preference"."Preference List"."Currency Preference Code", '', "Dynamic Currency Preference"."Preference List"."Currency Preference Name", 'column',"Dynamic Currency Preference"."Preference List"."Currency Support Flag" FROM "Dynamic Currency Preference"."Preference List" order by "Dynamic Currency Preference"."Preference List"."Currency Preference Name" ASC ^M ]]^M [2017-10-17T13:56:18.937-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: bb3727d68120b54d:270c056:15f2ac45751:-8000-0000000000001752,0:1:12] [tid: 3cb88700] [nQSError: 27002] Near <)>: Syntax error [nQSError: 26012] . [[^M file: server/Metadata/NQSParser/Src/NQSPScanner.cpp; line: 2321^M ********** Task: 1. Running for (mls): 0 ********** Description: Execute logical request User: {user} SessionID: 3011182592 ProcedureID: 113 SQL Hash: 0x989ee0fc SQL::{call SAGetSharedRequestKey('SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name")')} ^M ]]^M [2017-10-17T13:56:18.938-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: bb3727d68120b54d:270c056:15f2ac45751:-8000-0000000000001752,0:1:16:3] [tid: 3cb88700] [nQSError: 27002] Near <)>: Syntax error [nQSError: 26012] . [[^M file: server/Metadata/NQSParser/Src/NQSPScanner.cpp; line: 2321^M ********** Task: 1. Running for (mls): 0 ********** Description: Prepare Logical Request User: {user} SessionID: 3011182592 ProcedureID: -1 SQL: SET VARIABLE QUERY_SRC_CD='DashboardPrompt';SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name") Regards, Charles