Forum Discussion
Conversion of type to List in database
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
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]
- make sure you turn on HierachySQL data selector. Highlighted is Navigate using full Hierachy
- Do your query with one schema, then edit where the schema is with your Schema variable (don't include the quotes)
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
3 Replies
- ImkeF
Community Champion
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]
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
artemusMicrosoft Employee
- make sure you turn on HierachySQL data selector. Highlighted is Navigate using full Hierachy
- Do your query with one schema, then edit where the schema is with your Schema variable (don't include the quotes)