How to use sql subqueries in qlikview

[#1]

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

[jonathandienst]

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.

[#3]

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

[Peter_Cammaert]

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

[#5]

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

[jonathandienst]

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