topic Re: How to use sql subqueries in qlikview in QlikView

How to use sql subqueries in qlikview

Thu, 02 Jun 2016 09:43:11 GMT

Hello all,

I have a sql subselect query in my script.. I am getting an error when i try to run in qlikview but it gives me a correct result in SQL.

can we use sql subqueries in qlikview? or how to run this script to get the result

My task is to count the number of errors in a time period with one Tag say Tag A

and the time period for the above one has to get from another tag

so, i have two select statements.

if i am not clear in my explanation please ask me

please see the sql script below, that i am trying to run in qlikview

SELECT COUNT(*)

FROM (SELECT ROW_NUMBER() OVER(ORDER BY TA.DATETIME) AS ROW,

TA.DATETIME D1,

TB.DATETIME D2

FROM (SELECT ROW_NUMBER() OVER(ORDER BY DATETIME) AS ROW,

DATETIME,

VALUE

FROM HISTORY

WHERE TAGNAME = 'TagA'

AND DATETIME >= '2016-05-21 06:00:00'

AND DATETIME <= CURRENT_TIMESTAMP

AND VALUE IN (0,2)

) AS TA

INNER JOIN

(SELECT ROW_NUMBER() OVER(ORDER BY DATETIME) AS ROW,

DATETIME,

VALUE

FROM HISTORY

WHERE TAGNAME = 'TagA'

AND DATETIME >= '2016-05-21 06:00:00'

AND DATETIME <= CURRENT_TIMESTAMP

AND VALUE IN (0,2)

) AS TB

ON TA.ROW + 1 = TB.ROW

WHERE TA.VALUE = 2

) U

INNER JOIN

(SELECT DATETIME

FROM DBO.HISTORY

WHERE TAGNAME = 'TagB'

AND DATETIME >= '2016-05-21 06:00:00'

AND DATETIME <= CURRENT_TIMESTAMP

AND VALUE != 0

) G

ON G.DATETIME BETWEEN U.D1 AND U.D2

Thanks in advance

Ganesh

Re: How to use sql subqueries in qlikview

jonathandienst — Thu, 02 Jun 2016 09:47:41 GMT

Qlikview passes everything between the SQL keyword and the next semi-colon directly to the server. So you should be able to do this:

MyTable:

SQL <... insert arbitrarily complex SQL expression here ...>

;

The only caveat is that the SQL expression cannot contain any semi-colons.

Re: How to use sql subqueries in qlikview

Thu, 02 Jun 2016 10:05:26 GMT

Hi, Thanks for the reply... But I am getting the error as ODBC read failed when i try to run this query

Re: How to use sql subqueries in qlikview

Peter_Cammaert — Thu, 02 Jun 2016 11:07:26 GMT

Do you get any results if you cut your complex query to pieces and feed them one by one to the DB? Which part is failing? Does it work any better if you add the SQL prefix to this query in your load script?

Peter

Re: How to use sql subqueries in qlikview

Thu, 02 Jun 2016 14:43:35 GMT

Hi peter, very much thank you for helping me as you do always. I tried to keep the small pieces of code as you said.

i am able to run and get the data onto my qlikview, when i keep this piece of code

SELECT ROW_NUMBER() OVER(ORDER BY TA.DATETIME) AS ROW,

TA.DATETIME D1,

TB.DATETIME D2

FROM (SELECT ROW_NUMBER() OVER(ORDER BY DATETIME) AS ROW,

DATETIME,

VALUE

FROM HISTORY

WHERE TAGNAME = 'TagA'

AND DATETIME >= '2016-05-21 06:00:00'

AND DATETIME <= CURRENT_TIMESTAMP

AND VALUE IN (0,2)

) AS TA

INNER JOIN

(SELECT ROW_NUMBER() OVER(ORDER BY DATETIME) AS ROW,

DATETIME,

VALUE

FROM HISTORY

WHERE TAGNAME = 'TagA'

AND DATETIME >= '2016-05-21 06:00:00'

AND DATETIME <= CURRENT_TIMESTAMP

AND VALUE IN (0,2)

) AS TB

ON TA.ROW + 1 = TB.ROW

WHERE TA.VALUE = 2;

if you observer the code, i removed first select statement that is

select count(*) from line

and the last inner join part of the code.

when i try to run the above code by adding the first select statement to above code that is

SELECT COUNT(*)

FROM (

SELECT ROW_NUMBER() OVER(ORDER BY TA.DATETIME) AS ROW,

TA.DATETIME D1,

TB.DATETIME D2

FROM (SELECT ROW_NUMBER() OVER(ORDER BY DATETIME) AS ROW,

DATETIME,

VALUE

FROM HISTORY

WHERE TAGNAME = 'TagA'

AND DATETIME >= '2016-05-21 06:00:00'

AND DATETIME <= CURRENT_TIMESTAMP

AND VALUE IN (0,2)

) AS TA

INNER JOIN

(SELECT ROW_NUMBER() OVER(ORDER BY DATETIME) AS ROW,

DATETIME,

VALUE

FROM HISTORY

WHERE TAGNAME = 'TagA'

AND DATETIME >= '2016-05-21 06:00:00'

AND DATETIME <= CURRENT_TIMESTAMP

AND VALUE IN (0,2)

) AS TB

ON TA.ROW + 1 = TB.ROW

WHERE TA.VALUE = 2);

than i am getting a very weird error as

me and my colleague seen the code to find that paranthesis ' ) '

as that error is a syntax error we are unable to find out that syntax error,

if you understand my above explanation clearly.

what i am trying to do is, as my two pieces of code is working properly, i ran the code seperately and loaded into a table, and i want to perform a inner join or mapping on the two tables

if you could help me with this, if i explained properly, that would be a great help again

Thank you

Ganesh

Re: How to use sql subqueries in qlikview

jonathandienst — Fri, 03 Jun 2016 09:56:12 GMT

You don't need to add a SELECT statement, you need to add a SQL statement, as I showed in my earlier post.