Declaring and using SQL variables in an Advanced Query

Declaring and using SQL variables in an Advanced Query


I'm trying to declare and use a SQL variable into an advanced query. The problem is, when testing it, I get the following error: «Invalid SQL - Unknown MyString parameter in Query».

Any ideas? Thanks,
Pedro Gonçalves
Hi Pedro,

I think you can overcome that by:
1 - Declaring an input parameter to the advanced query e.g. SQLVariable1
2 - Setting the "Expand Inline" property to "Yes"
3 - Passing a static string with that variable "@mySQLVariable"

Hope this helps,

Tiago Simões
Hi Tiago. That did work. Thanks!
I am trying to do something like this:

Advanced query with Imput parameter : CountryNames with value "@CountryNames"
and CountryCodeListForAdvQuery  with value "( 'CA','US' )", both inline.

If I just do 
SELECT {COUNTRY}.[CountryName]
WHERE {COUNTRY}.[CountryCode] IN @CountryCodeListForAdvQuery 
It works and I get 2 countries. 

But this one doesn't return any rows. 

DECLARE @CountryNames varchar (200)
SET @CountryNames = ''
SELECT @CountryNames = 
    coalesce (case when @CountryNames = ''
                   then {COUNTRY}.[CountryName]
                   else @CountryNames + ',' + {COUNTRY}.[CountryName]
WHERE {COUNTRY}.[CountryCode] IN @CountryCodeListForAdvQuery 

Am I missing something ?
Hi ksah,

I'd say you can't declare @CountryNames inside the Advanced Query.
What are you trying to achieve here? Is it a comma-separated list of country names?
If so look at the following page:
Thanks Andre, Yes indeed it was comma separated country  names. I posted the comment on this post because there was already some discussion about using SQL variable inside advacned query. If you look at the Tiago Simões comment, he have mentioned a way to use SQL variable in advanced query. 
Hi ksha,

I understand.

Were you able to create the query to ouput the comma-separated list of country names?
I haven't tried the link that you have sent until now. For now I am using 

SELECT {COUNTRY}.[CountryName] FROM {COUNTRY} WHERE {COUNTRY}.[CountryCode] IN @CountryCodeListForAdvQuery

with the loop.
So I have tried your link and it worked.Thanks. So We can not declare inside sql query ? Is this correct ? 
Not that I know of. If you declare @Var then you would need an input to that query called Var otherwise the code won't compile.