topic Re: Executing query from Qlikview in QlikView

Executing query from Qlikview

kkkumar82 — Wed, 09 Aug 2017 09:52:27 GMT

All,

I have a below query which I am executing from SQL server, it is running fine from there but when I try to execute the same from

Qlik it is throwing error

WITH XMLNAMESPACES (

'http://www.XXname.com/ConcurSaeExtract' AS xx

);

select

CAST(EVENT_PAYLOAD.query('data(/xx:SAE-RECORD/xx:BATCH_ID)') as varchar(250)) BATCH_ID,

record_id

,creation_date

,processed_flag

,event_type

from uat_mulesoft_sql.dbo.HOLX_MULE_EVENT_Q

where 1=1

and EVENT_TYPE = 'EXPENSE_GL'

--and CREATION_DATE > GETDATE() - 1;

The main problem is with the first part "With XML Namespace" and the "Cast function"

Any ideas will help me

Re: Executing query from Qlikview

prma7799 — Wed, 09 Aug 2017 09:59:32 GMT

For cast function please read below thread

Replacement of CAST function in QlikView! | Qlik Community

how to use count and cast functions in qlikview

Re: Executing query from Qlikview

kkkumar82 — Wed, 09 Aug 2017 10:38:35 GMT

Yes, but the column EventPayload contains all these values for eg

<xx:SAE-RECORD xmlns:xx="http://www.hologic.com/ConcurSaeExtract"><xx:CONSTANT>DETAIL</xx:CONSTANT><xx:BATCH_ID>811</xx:BATCH_ID><xx:BATCH_DATE>2017-06-20</xx:BATCH_DATE><xx:SEQUENCE_NUMBER>1</xx:SEQUENCE_NUMBER><xx:EMPLOYEE_ID>001192</xx:EMPLOYEE_ID><xx:EMPLOYEE_LAST_NAME>BRUCE</xx:EMPLOYEE_LAST_NAME><xx:EMPLOYEE_FIRST_NAME>KATHY</xx:EMPLOYEE_FIRST_NAME><xx:MI/><xx:EMPLOYEE_GROUP_ID>Non-VP US</xx:EMPLOYEE_GROUP_ID><xx:EMPLOYEE_ORG_UNIT_1>01</xx:EMPLOYEE_ORG_UNIT_1><xx:EMPLOYEE_ORG_UNIT_2>200</xx:EMPLOYEE_ORG_UNIT_2><xx:EMPLOYEE_ORG_UNIT_3>2600</xx:EMPLOYEE_ORG_UNIT_3><xx:EMPLOYEE_ORG_UNIT_4>0200</xx:EMPLOYEE_ORG_UNIT_4>

So to extract the values from the columns we use that

Re: Executing query from Qlikview

kkkumar82 — Wed, 09 Aug 2017 11:08:40 GMT

Thanks for the links but they couldn't help me.

Re: Executing query from Qlikview

deepakqsv — Mon, 16 Apr 2018 23:57:59 GMT

Try SQL keyword in the start and you will be all good, I had the same issue,

Just adding the SQL keyword helpeme to execute the total block.

SQL WITH XMLNAMESPACES (

'http://www.XXname.com/ConcurSaeExtract' AS xx

);

select

CAST(EVENT_PAYLOAD.query('data(/xx:SAE-RECORD/xx:BATCH_ID)') as varchar(250)) BATCH_ID,

record_id

,creation_date

,processed_flag

,event_type

from uat_mulesoft_sql.dbo.HOLX_MULE_EVENT_Q

where 1=1

and EVENT_TYPE = 'EXPENSE_GL'

--and CREATION_DATE > GETDATE() - 1;

Re: Executing query from Qlikview

petter — Tue, 17 Apr 2018 06:16:37 GMT

I realize that this is an old post but I think it might be good for others that see this question to get some guidance on the issue....

The query that will be sent to SQL Server has to be between a SQL and a semicolon. Everything between the SQL and the ; will be sent to SQL Server via the driver (most often ODBC) that the CONNECT statement in the load script use.

So having a semicolon in the middle of the query is not going to work well.
The semicolon in the middle of the query does not seem to be part of a SQL Server T-SQL syntax. It is most certainly something that a query tool would accept as a separator between SQL statements that can be sent to SQL Server. Each SQL specification in a Qlik load script has to contain one and only one SQL statement.
The query being sent should probably look like this:

SQL

WITH XMLNAMESPACES ( 'http://www.XXname.com/ConcurSaeExtract' AS xx)
SELECT

CAST(EVENT_PAYLOAD.query('data(/xx:SAE-RECORD/xx:BATCH_ID)') as varchar(250)) BATCH_ID

,record_id

,creation_date

,processed_flag

,event_type

FROM uat_mulesoft_sql.dbo.HOLX_MULE_EVENT_Q

WHERE

1=1 AND EVENT_TYPE = 'EXPENSE_GL'

--AND CREATION_DATE > GETDATE() - 1

FOR XML PATH('xx:root')

;

The last line has been added and and it might be necessary with a different specification after FOR XML. A semicolon has been remove between the WITH..... and the SELECT. According to Microsoft documentation it shouldn't be any semicolons between a WITH and the following SELECT.