SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding Values With Numerous Columns


Finding Values With Numerous Columns

Author
Message
Douglas Osborne-456728
Douglas Osborne-456728
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 327
Hey,

This didn't work for me until I added a % at the end of the first dynamic SQL statement:

SELECT @sql = CASE
WHEN @sql IS NULL THEN 'Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + -- Add below
' %'' '
WHEN @sql IS NOT NULL THEN @sql + 'UNION ALL Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' '

Doug
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1252 Visits: 920
Douglas Osborne (12/3/2008)
Hey,

This didn't work for me until I added a % at the end of the first dynamic SQL statement:

SELECT @sql = CASE
WHEN @sql IS NULL THEN 'Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + -- Add below
' %'' '
WHEN @sql IS NOT NULL THEN @sql + 'UNION ALL Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' '

Doug


Adding the % directly in the dynamic sql statement forces it to always look for anything that starts with the value passed in. The way I use it is to put the % in the value passed in, just like using like directly in a select statement. That way I can have it search for the beginning or ending or any combination.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Douglas Osborne-456728
Douglas Osborne-456728
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 327
Tim,

Why use LIKE if you are not adding % - shouldn't you just say = instead then?

Doug
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1252 Visits: 920
Douglas Osborne (12/3/2008)
Tim,

Why use LIKE if you are not adding % - shouldn't you just say = instead then?

Doug


For flexibility, I wanted to be able to use a wildcard such as % or _, or not, depending on what I was searching for. Sometimes I had an exact value to search for and sometimes I was looking for a substring.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
JJ B
JJ B
Mr or Mrs. 500
Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)

Group: General Forum Members
Points: 533 Visits: 2859
I appreciate this article and discussion.

I have to do ad-hoc report queries off of a Sybase database created by an other agency (let's call them the State). I have zero input on database design, practically no documentation, and only have read access to the data tables.

I cringe every time my users ask for queries that require finding what "program" a client is in. Why? Because the State created a set of 8 columns all containing "program" data. Users can enter 3 characters into each column to indicate the set of programs the client has. A client can have anywhere from zero up to 8 programs. There is no order to the columns and more than 8 possible programs, though the client will never have more than 8 programs at once.

What this leaves me with is this: When the users ask, "How many clients have program ABC?" Then I have to search all 8 columns. And of course, it is never as easy as that. They usually have queries like, "How many clients have any of the following programs, ABC, XFG, T3B, ... but not GTH or XYZ."

So, why did the State set up the Sybase database that way? Don't they know anything about database design? I'm not privy to any of their conversations or programming problems. So, I don't know. But most of their database (while not a schema I would choose) is fairly workable and mostly normalized (though they did do that annoying phone thing talked about in the article). Why then set up denormalized data for something as vital as a client's program?

My thoughts:
1) The program data is part of a set of data that has to mirror/be merged with a legacy mainframe database. I have no doubt that the mainframe system is set up with the same 8 columns.

2) But that doesn't explain why they wouldn't use programming to normalize the data in the database I access. All I can think of here is that they weren't thinking about querying the data and they were mostly concerned with saving time on programming the part that merges data between the mainframe and the normal database.

I'll tell you, if it was me, I would have done the extra programming so that the data was normalized in the Sybase database. It is a terrible pain working with their data as-is!

But as the article points out, when you don't have a choice, it is nice to have techniques to handle the situation. I just thought people might appreciate another real world example of this kind of problem. Cheers.
Phil Factor
Phil Factor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2078 Visits: 2971
I just thought people might appreciate another real world example of this kind of problem

We do, we do. It is strange how quickly one forgets this sort of horror. it is nice to be reminded. Thanks JJ B, thanks timothy.


Best wishes,

Phil Factor
Simple Talk
Jason Akin
Jason Akin
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 87
I just noticed something else with my "In" syntax. If you put the IN clause into a JOIN, you can use the LIKE against the original source table for a set based approach instead of concatenating.

CREATE TABLE tblAccount (
pkAccountId int,
Acctnum varchar(20),
fkOverDraftId1 int,
fkOverDraftId2 int)

SELECT main.Acctnum, qry.Acctnum
FROM tblAccount main
INNER JOIN tblAccount qry ON qry.pkAccountid IN (main.fkOverDraftId1, main.fkOverDraftId2)
WHERE qry.AcctNum LIKE '%123%'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search