Dynamic SQL Statement

[haymarketpaul]

Hi All

QV11 SR1, SQL Server 2008

I have a SQL Server select statement like the one below in QV...

SELECT cmpname,
       Count (DISTINCT U.useremail) AS [OptIn Email]
FROM   tbl_users U
WHERE  U.cmpid IN ( 1838, 6, 1, 3, 1956, 4 )
       AND U.useroptinhbplemails = 1
GROUP  BY cmpname
ORDER BY cmpname
;

i would like to pass in various numbers to part of the WHERE clause (part in bold above)

i don't have the ability to create stored procedures on the server.

Can i dynamically construct the SQL Statement within QV so that i could read in the numbers i want from a spreadsheet or variable and then execute the SQL ?  I don't want to write the same query loads of times with different numbers in that part of the WHERE clause if i can help it.

Any ideas appreciated.

[RedSky001]

Paul,

Something like this:

Temp:

LOAD * INLINE [

    Query, Numbers

    1, 1

    1,2

    2,4

    2,44

];

Numbers:

load Query

,Concat(Numbers,',') as QueryString

Resident Temp

Group by Query;

Drop table Temp;

Counts:

LOAD Count(Query) as Y

Resident Numbers;

LET y = Peek('Y', 0) - 1;

DROP Table Counts;

// for each Query

for k = 0 to $(y)

                    LET vQueryParamter = PEEK('QueryString', $(k),'Numbers');

                              TEMP:

                              SQL SELECT cmpname,

                                     Count (DISTINCT U.useremail) AS [OptIn Email]

                              FROM   tbl_users U

                              WHERE  U.cmpid IN ( '$(vQueryParamter)' )

                                     AND U.useroptinhbplemails = 1

                              GROUP  BY cmpname

                              ORDER BY cmpname

                              ;

next

Mark

[haymarketpaul]

Thanks Mark

That almost works nicely.

If   vQueryParameter = only one number

then it works nicely, but if it equals multiple numbers (which is usually the case)

e.g.

1972,3

or

1976,1826,2004,1673

then i get nothing back?

[RedSky001 · Accepted solution]

Try dropping the quotes

so change

WHERE  U.cmpid IN ( '$(vQueryParamter)' )

to

WHERE  U.cmpid IN ( $(vQueryParamter) )

[haymarketpaul]

Perfect - Thanks mate

[vsabbise]

May I know how do I do this in Qlik Sense ? Especially would like to know how to use IN operator in Qlik Sense.

[haymarketpaul]

The IN operator in my question above is actually part of SQL rather than Qlik view/sense syntax - if that is the IN operator you are referring to then you can happily use SQL in Qlikview and Qliksense in the same way including the IN operator.  Have you got an example.

[vsabbise]

Thanks for your response. So can I use the as is sql script in the expression window ? Here is my detailed question. How to use 'IN' operator with 'variable' in Qlik Sense