Conversion of type to List in database

[Anonymous]

This is likely simple, but I'm not getting it.

We have a database with multiple clients on the server. So we named the schemas after each one. I read through the ability to use Query Parameters and I created 3 different Parameters, Server, Database, and Schema. Server and Database are working fine. Its getting me to the correct machine and the correct database (Prod, QA, Test) ... But when I try to use the Schema parameter on the queries, its throwing the type conversion.

So I started with this:

    TheClientName{[Name="distinctcve"]}[Data]

And I want to change it to this

    Schema{[Name="distinctcve"]}[Data]

However, Schema is not a list. How do I get it to substitute the value in Schema, for the variable name ?

Thanks

[ImkeF · Accepted solution]

Hi @Anonymous 

the Schema is a column in a table and you'd select it like so:

AdventureWorks2008R2{[Schema="HumanResources",Item="vEmployee"]}

attaching the [Data]-column like you did above will return the Data-values in list form.

So if you're using a parameter for the schema name called "MySchemaParameter", then the syntax would look as follows:

AdventureWorks2008R2{[Schema=MySchemaParameter,Item="vEmployee"]}[Data]

[artemus · Accepted solution]

1. make sure you turn on HierachySQL data selector. Highlighted is Navigate using full Hierachy
2. Do your query with one schema, then edit where the schema is with your Schema variable (don't include the quotes)

[artemus · Accepted solution]

1. make sure you turn on HierachySQL data selector. Highlighted is Navigate using full Hierachy
2. Do your query with one schema, then edit where the schema is with your Schema variable (don't include the quotes)

[ImkeF · Accepted solution]

Hi @Anonymous 

the Schema is a column in a table and you'd select it like so:

AdventureWorks2008R2{[Schema="HumanResources",Item="vEmployee"]}

attaching the [Data]-column like you did above will return the Data-values in list form.

So if you're using a parameter for the schema name called "MySchemaParameter", then the syntax would look as follows:

AdventureWorks2008R2{[Schema=MySchemaParameter,Item="vEmployee"]}[Data]

[Anonymous · Accepted solution]

Thank you - Both of your suggestions we needed to complete the picture, so let me explain.

When I tried your solution, the Schema=Variable came back and said it could not resolve. There was no "column" for Schema.

Then I tried the next solution, which was exactly what I tried before but as before, it confused the variable with the structure.

Then I realized you both had a point.  I always use the option to use the full heirarchy.

However when you do that, it eliminates Schema as a column ! So the filter did not work. When I redid the query WITHOUT the full heirarchy, it is a column and is now selectable using the option. Thank you. So your answer was right as long as you dont select by full heirarchy.

Thank you both