Issue with database table name with spaces in map dbxquery search

[manunairadavakk]

Hi Splunk experts,

Please help on the below issue.
When i am running a query directly with dbxquery, the table name with spaces(View Employee Helpdesk) and column name with spaces is not creating any issues. I am getting results as expected.

| dbxquery query="select \"Employee Number\",\"Manager\"
FROM
BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\"=('EMP1')" connection="EMP-PR1"

Whereas, when i am using the same table and cloumn name in map search with dbxquery it is not being recognised and throwing error.

.........
| fields EMPID
| map search="| dbxquery query=\"select \"Employee Number\"
FROM BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" IN ($EMPID$)\"
connection="EMP-PR1""

Error being thrown:
[map]: org.netezza.error.NzSQLException: ERROR: 'select FROM BIA_BA_EUL.View ANALYZE' error ^ found "FROM" (at char 9) expecting an identifier found a keyword

[thomasroulet]

Ok, escape the backslashes in the query

    | fields EMPID
    | map search="| dbxquery query=\"select \\\"Employer Name\\\" PR,  \\\"Employee Number\\\" EMPID FROM BIA_BA_EUL.\\\"View Employee Helpdesk\\\" WHERE \\\"Employee Number\\\" in ($EMPID$)\" connection=\"EMP-PR\"" 

[manunairadavakk]

@thomasroulet

This will not work because netezza does not recognise backticks, it recognises only double quotes.But somehow in map search it is not being recognised while it is being recognised in ordinary dbxquery.
Getting error as below:

[map]: org.netezza.error.NzSQLException: ERROR: 'select Employee Number FROM BIA_BA_EUL.View Employee Helpdesk WHERE Employee Number IN ('EMP1','EMP2') ANALYZE' error ^ found "`" (at char 24) expecting a keyword

[thomasroulet]

Do not surround the field name or table name with double quotes but with backticks

   | fields EMPID
   | map search="| dbxquery query=\"select `Employer Name` PR, `Employee Number` EMPID FROM BIA_BA_EUL.`View Employee Helpdesk` WHERE `Employee Number` in ($EMPID$)\" connection=\"EMP-PR\"" 

[Richfez]

I'm still thinking how to resolve this, but if it helps anyone (or helps you), I think the problem is that when you wrap that in map, you are effectively removing one layer of escaping.

| map search="| dbxquery query=\"select \"Employee Number\"
FROM BIA_BA_EUL.\"View Employee Helpdesk\" WHERE \"Employee Number\" IN ($EMPID$)\"

turns into

dbxquery query="select "Employee Number"
FROM BIA_BA_EUL."View Employee Helpdesk" WHERE "Employee Number" IN ($EMPID$)"

Is there a reason you can't use dbxlookup instead of map+dbxquery? It should not require the double-escaping shenanigans, and bonus it should be far faster too!