Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

SQL :scripting scalar variable problem (dymanic sql pivot)

I get the following error when I try to create a qlik table from a SQL dynamic pivot:  I can't seem to find a source or example for below.    I think I need to create a intermediary table before loading scalar variable but I am not sure where?

Thanks,

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Must declare the scalar variable "@query".


Below is example of code that I am using:


PivotTable:

load *;

SQL

( SELECT

                  columns1, columns2, column3...

  --  into #temptable

              from

                  sqltable source

DECLARE @cols AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT ',' + QUOTENAME(column2)

                    from #temptable

                    group by column2

                    order by column2

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,1,'')

set @query = 'SELECT columns1,' + @cols + ' from

(

select

max(T.columns1) as columns1

,isnull(T.      [column2],0)as       [column2]

,COALESCE(T.      [column3],0)as       [column3]

   from #temptable T

   group by T.column2, T.column3

   )  x

            pivot

            (

                sum(column3)

                for column2 in (' + @cols + ')

            ) p '

execute(@query);

drop table #temptable;

7 Replies
sinanozdemir
Valued Contributor III

Re: SQL :scripting scalar variable problem (dymanic sql pivot)

When you use OLE DB connection, you should use "?" instead of "@" or change your connection to ODBC.

If you ask why, because OLE DB uses positional parameters.

Thanks

Not applicable

Re: SQL :scripting scalar variable problem (dymanic sql pivot)

Thanks I will give it a try.

Sent from Outlook<http://taps.io/outlookmobile>

Not applicable

Re: SQL :scripting scalar variable problem (dymanic sql pivot)

Hi, Unfortunately that didn't work,

I changed to the @ to ? and still got same error? 

PivotTable:

load *;

SQL

( SELECT

                  columns1, columns2, column3...

  --  into #temptable

              from

                  sqltable source

DECLARE ?cols AS NVARCHAR(MAX),

    ?query  AS NVARCHAR(MAX);

select ?cols = STUFF((SELECT ',' + QUOTENAME(column2)

                    from #temptable

                    group by column2

                    order by column2

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,1,'')

set ?query = 'SELECT columns1,' + ?cols + ' from

(

select

max(T.columns1) as columns1

,isnull(T.      [column2],0)as       [column2]

,COALESCE(T.      [column3],0)as       [column3]

   from #temptable T

   group by T.column2, T.column3

   )  x

            pivot

            (

                sum(column3)

                for column2 in (' + ?cols + ')

            ) p '

execute(?query);

drop table #temptable;

sinanozdemir
Valued Contributor III

Re: SQL :scripting scalar variable problem (dymanic sql pivot)

Ok. Let's try another way.

Now, keep '@', but use ODBC not OLE DB.

BTW, is this query running fine in SQL Server?

Not applicable

Re: SQL :scripting scalar variable problem (dymanic sql pivot)

Works fine in sql;

Never used ODBC connection so I am newbie there; I am connecting via server credentials and not sure how to do that, is there good source?

sinanozdemir
Valued Contributor III

Re: SQL :scripting scalar variable problem (dymanic sql pivot)

You can set up an ODBC connection. Do a search in Windows for ODBC and I use 64 bit. Here are screenshots:

I would suggest picking "SQL Server Native Client 10.0" from the driver list.

Capture.PNG

Give it a name and find your server from the server list.

Capture2.PNG

In terms of authentication, use "Windows authentication":

Capture3.PNG

And then choose your default database:

Capture4.PNG

After all this, test your connection to see if you can connect to it successfully.

You can also do a Google search for setting up ODBC connections and I am sure there are videos on YouTube.

Hope this helps.

Not applicable

Re: SQL :scripting scalar variable problem (dymanic sql pivot)

Your very helpful; I will try this tomorrow as I used pivot without dynamic to meet a deadline;  making this works will be very useful. 

I am connecting  via Sense not qlikview so you screenshots not that useful; I will research when I  have more time later this week and respond appropriately with my results.

thanks

Community Browser