September 18, 2003 at 8:33 am
I am looking for a way to simplify the following query-
Clients table has an integer column named ClientAttribBits that stores a bit-mask used to define the different client types set for that Client (one to many). The ClientAttribBits column within the Clients Table relates to the ClientAttribs table which has two columns, ClientAttribBit an integer that maps to the bits set in the ClientAttribBits column in the Clients Table and a Description column – varchar that is the descriptive name.
Currently, in order to return a list of Clients and the descriptive names of all the ClientAttribBits set, I first create a temporary in memory table to store the Client information that I need and then use a cursor to iterate that table and for each client record perform the following query to return all of the attributes as a single description.
Declare @AllAttribDesc varchar(1024)
Declare @tempTable table(
Col1 As..
Col2 As..
etc
)
--Get the clients that match the chosen selection criteria and store in the temp table
INSERT INTO @tempTable (Col1, Col2, …)
(SELECT Col1, Col2…FROM Clients WHERE…)
.
.
--Then the cursor iteration through @tempTable to retrieve the attribute descriptions
SELECT @AllAttribDesc =IsNull (@AllAttribDesc, '') + Description + ', ' FROM ClientAttribs WHERE ClientAttribBit & @ClientAttribBits = ClientAttribBit
I then update the temporary table with the value of @AllAttribDesc and then do a final SELECT from the temporary table for my result set.
This works fine, but it sure seems inefficient.
Is there a way, using a sub-query or some method other than a cursor, return the concatenated descriptions for the Client Attributes within the original SELECT?
Edited by - tim opry on 09/18/2003 08:33:38 AM
Edited by - tim opry on 09/18/2003 08:35:54 AM
September 18, 2003 at 8:41 am
Have a look at this, it should give you an idea:
http://www.sqlservercentral.com/faq/viewfaqanswer.asp?faqid=206
If you are using SQL 2000 you can create a User Defined Function to return this data.
Jeremy
September 18, 2003 at 11:29 am
You're almost there. If you're using SQL Server 20000, simply recast your method as a UDF:
CREATE FUNCTION dbo.f_ListAttribs(@attribs int) RETURNS varchar(1024) AS
BEGIN
DECLARE @list varchar(1024)
SELECT @list = ISNULL(@list + ', ','') + Description
FROM ClientAttribs
WHERE ClientAttribBit & @attribs = ClientAttribBit
RETURN @list
END
Then just:
SELECT ClientID, ClientName, dbo.f_ListAttribs(ClientAttribBits)
FROM Clients
Do you realize that using an integer column like this violates first normal form? SQL Server has the bit datatype, which wouldn't take any more space but would be more accessible and allow simpler coding...
--Jonathan
--Jonathan
September 19, 2003 at 8:40 am
Thanks for the feedback, I will give those suggestions a shot.
As for the use of an Integer as a bitmask - I am aware this is not conventional in SQL, and I do use bit columns for similar uses. However, in this particular instance, the client types are not known when the application went into use. This is something they wanted to 'make up' and add to over time. As such, I would either have to create multiple bit columns for potential use with placeholder type names, or add them to the database and change the dependent sProcs as that was done.
This method allows expansion and flexibility without schema changes.
I do appreciate the feedback and if there is another way to do it that provides the same type of flexibility, I would appreciate any and all suggestons.
September 19, 2003 at 12:32 pm
---------------------------------------
DECLARE @State varchar(225)
SELECT @State = isnull(@State + ',', '') + State
FROM (SELECT DISTINCT State FROM authors) s
SELECT @State
-----------------------------------
Wow...this is really cool...no need for a loop or anything to create a csv out of the values in a column.
I'm a little confused about how this worked; I always thought that returning more than one row to a variable (non-table) would cause an error.
Is it because of the derived table?
Signature is NULL
September 19, 2003 at 2:04 pm
Quote:
quote:
---------------------------------------
DECLARE @State varchar(225)
SELECT @State = isnull(@State + ',', '') + State
FROM (SELECT DISTINCT State FROM authors) s
SELECT @State
-----------------------------------
Wow...this is really cool...no need for a loop or anything to create a csv out of the values in a column.
I'm a little confused about how this worked; I always thought that returning more than one row to a variable (non-table) would cause an error.
Is it because of the derived table?
I was pretty amazed too when I first learned that this is possible. But it has nothing to do with the derived table. The IsNull() is not the important part either -- that just prevents having an unwanted trailing delimiter. What is happening is that the variable assignment is being repeated for every row, and the "Set @var = @var + column" expression causes each row's value to be concatenated. In T-SQL whenever a scalar variable assignment occurs for multiple rows, the assignment actually does get repeated for each row, and in the normal case of "Set @var = column" the variable will contain the value from the last row encountered.
September 19, 2003 at 2:10 pm
Yeah, I've been playing with this and realized it had nothing to do with setting the variable to itself or the derived table.
Maybe I'm thinking about SQL 7.0, but I remember this not being possible. Could be I just never figured out how to do it right.
This makes building arrays really simple!
--Create Testing Table
declare @testing Table (FirstName char(25), LastName char(25))
Insert @testing Values ('Calvin', 'Lawson')
Insert @testing Values ('Donald', 'Duck')
Insert @testing Values ('Scooby', 'Doo')
DECLARE @Array varchar(225)
SELECT @Array = isnull(@Array, '') + FirstName + LastName
From @testing
Select @Array
Signature is NULL
September 19, 2003 at 2:46 pm
Where is the output going?
If you are using SQL 7 then you are pretty much as close as you will get, if SQL 2000 then UDF will generally be a better solution. However depending on where this data is going to you may be in overkill mode on your business logic on the SQL server. I would prefer to know what you do with it before I suggest a particular path.
September 19, 2003 at 6:09 pm
I am using SQL 2000 and the UDF implementation suggested worked fine.
The output is for reporting.
September 20, 2003 at 1:54 pm
quote:
Thanks for the feedback, I will give those suggestions a shot.As for the use of an Integer as a bitmask - I am aware this is not conventional in SQL, and I do use bit columns for similar uses. However, in this particular instance, the client types are not known when the application went into use. This is something they wanted to 'make up' and add to over time. As such, I would either have to create multiple bit columns for potential use with placeholder type names, or add them to the database and change the dependent sProcs as that was done.
This method allows expansion and flexibility without schema changes.
I do appreciate the feedback and if there is another way to do it that provides the same type of flexibility, I would appreciate any and all suggestons.
This design will cause many more problems than it will solve. Your coding may need to have many more hacks like the one you need now, and the information will be opaque without your hard-coded routines. The design is not atomic and it is not in 1NF, and you are limited to 32 binary attributes.
If the attributes are inherently binary, one way to do this is to create a table for each attribute, e.g.:
CREATE TABLE ActiveClients(
ClientID int FOREIGN KEY REFERENCES Clients,
PRIMARY KEY (ClientID))
This schema is atomic, obviously extensible, and makes needs like your current one a trivial matter of CASEs and joins. I don't like it, though, as it's close to violating the Information Rule, and it will still require code updates (as will your bitmap) when new attributes are added. And adding another attribute requires changing the schema.
I prefer to think of this as a classic normalization problem: you've got an array of attributes in the table, so you need to use normalize these into a many-to-many relation.
CREATE TABLE Attributes(
AttribID tinyint IDENTITY PRIMARY KEY,
Descr varchar(30) NOT NULL DEFAULT '')
CREATE TABLE ClientAttributes(
ClientID int FOREIGN KEY REFERENCES Clients,
AttribID tinyint FOREIGN KEY REFERENCES Attributes,
PRIMARY KEY (ClientID,AttribID))
This design is more easily extended, limits the number of joins, and puts the information (attribute type) back in a table rather than a table name. No schema change is required when adding another attribute. It also allows you to have attributes that are not merely binary by simply adding another column to the associative table. Your current need would still require a way to pivot the result set; you could use the hack I posted or, preferably, do this using the front end responsible for the report.
--Jonathan
--Jonathan
September 20, 2003 at 3:17 pm
Your second suggestion with the two tables is a good one I had not considered. I will definitely convert to this as soon as possible (as well as use this method in future projects). The use of bitmasks is an old habit learned using 8086 assembly - obviously, not the best choice for SQL.
Thank you for taking the time to outline this for me as well as the assistance on the UDF.
Regards,
Tim
Edited by - tim opry on 09/20/2003 3:19:53 PM
September 22, 2003 at 8:07 am
I am showing my ignorance here, but could somone tell me what the 's' is after the 2nd select statement in the below code
Thanks
Larry Gordon
FROM (SELECT DISTINCT State FROM authors) s<--- This S
--- Original Code --------
DECLARE @State varchar(225)
SELECT @State = isnull(@State + ',', '') + State
FROM (SELECT DISTINCT State FROM authors) s
SELECT @State
September 22, 2003 at 9:03 am
quote:
I am showing my ignorance here, but could somone tell me what the 's' is after the 2nd select statement in the below codeThanks
Larry Gordon
FROM (SELECT DISTINCT State FROM authors) s<--- This S
--- Original Code --------
DECLARE @State varchar(225)
SELECT @State = isnull(@State + ',', '') + State
FROM (SELECT DISTINCT State FROM authors) s
SELECT @State
That's the alias of the derived table. You can use a SELECT statement as you would a table name, but you must refer to it by an alias. This statement is equivalent to just:
SELECT @State = ISNULL(@State + ',', '') + State
FROM Authors
GROUP BY State
--Jonathan
--Jonathan
September 22, 2003 at 12:22 pm
Jonathan
Thanks for your explanation! I understand now.
Just a note though, It would appear to me, that there is a potential problem with both of the below code snippets. If State is null in the middle of your table in the examples, your results will not be correct. You either need to put an ORDER BY to bring the nulls to the top or deal with a null State in the table (which is different from the variable @State). Just my humble opinion.
Thanks again
Larry Gordon
----- 1st Code Snippet ------------
DECLARE @State varchar(225)
SELECT @State = isnull(@State + ',', '') + State
FROM (SELECT DISTINCT State FROM authors) s
SELECT @State
----- 2nd Code Snippet ------------
DECLARE @State varchar(225)
SELECT @State = ISNULL(@State + ',', '') + State
FROM Authors
GROUP BY State
SELECT @State
September 22, 2003 at 12:50 pm
quote:
It would appear to me, that there is a potential problem with both of the below code snippets. If State is null in the middle of your table in the examples, your results will not be correct. You either need to put an ORDER BY to bring the nulls to the top or deal with a null State in the table (which is different from the variable @State). Just my humble opinion.
Well, actually, no. Because of the use of DISTINCT or GROUP BY, the NULL values will always be returned first if you do not explictly order the statement DESC, and therefore will be excluded by the ISNULL function.
This is a good point if the OP's data contains NULLs, though. (I wouldn't allow NULLs in a description column, myself.)
The correct solution if the column has null values that you wish to exclude and you are explicitly ordering is to exclude them from the result set:
DECLARE @State varchar(225)
SELECT @State = isnull(@State + ',', '') + State
FROM Authors
WHERE State IS NOT NULL
GROUP BY State
ORDER BY State DESC
SELECT @State
Or, if null is relevant information:
DECLARE @State varchar(8000)
SELECT @State = isnull(@State + ',', '') + ISNULL(State,'--')
FROM Authors
GROUP BY State
ORDER BY State DESC
SELECT @State
--Jonathan
--Jonathan
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply