Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi, Thanks for the reply... But I am getting the error as ODBC read failed when i try to run this query
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
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
You don't need to add a SELECT statement, you need to add a SQL statement, as I showed in my earlier post.