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.

Hi guys,

Sorry for the reply on an old case, but the first reply from Tiago Simoes on this case works for declarations in an advanced query.

Kind regards,