Where Exists does not work in script

[Anonymous]

Hi,

As stated in the subject I am trying to use a where a statement to limit the number of rows being selected in my program. I have read a good few forums on the exist statement and I have tried to adjust it accordingly however I always get the same error "ErrorMsg: Incorrect syntax near 'RowID'". I have 5 tables which I want to link, [Run Detail] is the largest table and has several million rows which I do not need, the only way to limit the number of rows of data being called is by using the RowID field. The RowID field has a association in other tables which have other fields such as dates, names, etc. which I can use to filter out the unnecessary information.

I have tried using left and inner join statements to do the same job however to do so would require making one large table which would cause poor run time and poor ram consumption. I am aiming to try and create a Star Schema data model as from my understanding it is the best model for Qlikview.

I have tried changing the SQL SELECT's to LOAD's and SELECT's but I seem to be having the same problem where Qlikview does not recognize the exists function.

Any help would be much appreciated.

Best regards

[FacObjName]:

//LOAD FacObjID,FacObjTypeID, FacObjName as line

SQL SELECT FacObjID, FacObjName, FacObjTypeID

FROM "APEX_RPT".dbo.FacObj

where FacObjName IN  ('Label 1', 'Label 2');

[ProcRcpID]:

//LOAD RowID, ProcRcpID, FacObjTypeID

SQL SELECT ProcRcpID, FacObjTypeID, "Desc", RowID

FROM "APEX_RPT".dbo.ProcRcp;

[Run]:

SQL SELECT RunID, FacObjID, BatchID, BeginRunDT, EndRunDT, "Sys_Dt", "Sys_User"

FROM "APEX_RPT".dbo.Run

where EndRunDT > dateadd(DD, -1, getdate());

[Run Parameter]:  

SQL SELECT RunParmID, Name

FROM "APEX_RPT".dbo.RunParm

where name in ('Label 1', 'Label 2');

[Run Detail]: //Run Detail has no date reference included in it and thus we're going to use the RowID to solve this as it can be linked to table with a date reference.

//LOAD RowID

sql select RunParmID, Value, RowID, RunID

FROM "APEX_RPT".dbo.RunDetail

where exists (RowID); //this is where I want the where filter to be applied

[jonathandienst · Accepted solution]

You are using Exists inside a SQL statement. The contents of the SQL statement are sent to the DBMS to be executed, but Exists is not a legal SQL statement. You will need to implement this logic differently in a SQL statement, probably a join or condition on a subquery returning the RowIDs to check; or load everything into QV and use the Exists() in a preceding load or a resident load.

SQL - something like

[Run Detail]:

SQL SELECT RunParmID, Value, RowID, RunID

FROM "APEX_RPT".dbo.RunDetail

WHERE RowID in (SELECT RowID FROM FROM "APEX_RPT".dbo.ProcRcp);

Preceding load

[Run Detail]:

LOAD *

Where Exists(RowID)

;

SQL SELECT RunParmID, Value, RowID, RunID

FROM "APEX_RPT".dbo.RunDetail;

[Anonymous]

Okay I think I understand. The main issue I've been having is that I am loading too many rows which is crashing the program. Would I be right in saying that:

[Run Detail]:

LOAD *

Where Exists(RowID)

;

SQL SELECT RunParmID, Value, RowID, RunID

FROM "APEX_RPT".dbo.RunDetail;

This code is loading all of the rows and then removing them using where exists (RowID)?

[marcus_sommer]

Yes in this case all records are pulled from the db and checked. Maybe the alternatives mentioned here are helpful for you: Re: Use a Field from qvd in a where oracle clause.

- Marcus

[Anonymous]

Great thanks for the clarification Marcus, hopefully I'll be able to come up with something.