Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Timothy A Wiseman » Finding Values With Numerous Columns
Add to briefcase
27 posts, Page 3 of 3
«««123

Finding Values With Numerous Columns Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Douglas Osborne-456728
Posted Wednesday, December 03, 2008 8:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, 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
Post #612923
timothyawiseman
Posted Wednesday, December 03, 2008 10:01 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900
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/
Post #612997
Douglas Osborne-456728
Posted Wednesday, December 03, 2008 12:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Tim,

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

Doug
Post #613092
timothyawiseman
Posted Wednesday, December 03, 2008 1:08 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900
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/
Post #613119
JJ B
Posted Thursday, December 04, 2008 9:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:03 PM
Points: 255, Visits: 2,405
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.
Post #613878
Phil Factor
Posted Thursday, December 04, 2008 10:26 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:00 AM
Points: 533, Visits: 2,285
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
Post #613909
Jason Akin
Posted Thursday, December 04, 2008 12:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 9:13 AM
Points: 24, Visits: 70
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%'
Post #613994
« Prev Topic | Next Topic »

Add to briefcase
27 posts, Page 3 of 3
«««123

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.