October 3, 2016 at 6:27 pm
I have a table of statuses(Open, Closed, etc.). I need to create a parameter that combines the first letter of multiple statuses, such as any status beginning with "O" or "R." How would create this?
October 4, 2016 at 1:15 am
You could use a table variable for example.
Or a comma separated list, split it out and use that as input.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 4, 2016 at 8:16 am
If you know it's only ever going to be one letter, you don't need the comma if you go that route. Personally, I'd go with the table variable.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2016 at 7:16 pm
I have a table of statuses(Open, Closed, etc.). I need to create a parameter that combines the first letter of multiple statuses, such as any status beginning with "O" or "R." How would create this?
First of all, the word "status" is what ISO 11179 and data modeling calls an attribute property. This means it has to be the "<something in particular>_status" attribute of a particular entity.
A status, by definition, is a state of being. This means it has a duration (start_timestamp, end_timestamp) to be a valid attribute.
Now you think that a concatenated string of the encodings text has some kind of meaning. Why? The whole idea of the status is that an entity has one and only one state of being in a given time. You can ask about entities whose statuses are in a set of possible codes. For example, {new, resubmitted, pending} might be the set of entities that have not come to final resolution.
Can you tell us what you are actually trying to do? You said "parameter", but surely you are not going to use this concatenated string is a parameter to a function!
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
October 10, 2016 at 8:56 pm
CELKO (10/10/2016)
I have a table of statuses(Open, Closed, etc.). I need to create a parameter that combines the first letter of multiple statuses, such as any status beginning with "O" or "R." How would create this?
First of all, the word "status" is what ISO 11179 and data modeling calls an attribute property. This means it has to be the "<something in particular>_status" attribute of a particular entity.
A status, by definition, is a state of being. This means it has a duration (start_timestamp, end_timestamp) to be a valid attribute.
Now you think that a concatenated string of the encodings text has some kind of meaning. Why? The whole idea of the status is that an entity has one and only one state of being in a given time. You can ask about entities whose statuses are in a set of possible codes. For example, {new, resubmitted, pending} might be the set of entities that have not come to final resolution.
Can you tell us what you are actually trying to do? You said "parameter", but surely you are not going to use this concatenated string is a parameter to a function!
If I were a betting man, my $ would be on a reporting proc. SSRS creates coma delimited arrays for multi-valued report parameters. In this particular scenario, you either have to with comma separated arrays r forgo multi-valued parameters...
As far as answering the OP's question... Just use a string splitting function (DelimitedSplit8k can be found here[/url])
DECLARE @MV_Parameter VARCHAR(1000) = '1,5,8,22,55,99,212,434,532,600,650,702,730,800';
SELECT
t.N
FROM
dbo.Tally t
JOIN dbo.DelimitedSplit8K(@MV_Parameter, ',') sc
ON t.N = CAST(sc.Item AS INT);
HTH,
Jason
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply