Post reply

Selecting from a table where any column is named like 'ServiceNumber%'

  • May 8, 2009 at 7:18 am

    #204439

    I have a flattened out data mart table like:

    Id int

    Name varchar

    Address varchar

    ....

    ServiceNumber001Volume int

    ServiceNumber002Volume int

    ServiceNumber003Volume int

    .....

    ServiceNumbernnnVolume int

    Id like to write something like

    pseudo code:

    Select all records from myTable where any column name like 'ServiceNumber%' and any of those columns' values are greater than zero.

  • May 8, 2009 at 7:33 am

    #991062

    you'd have to use dynamic sql to build the sql; here's a working example:

    Create Table MyTable(

    Id int,

    Name varchar,

    Address varchar,

    ServiceNumber001Volume int,

    ServiceNumber002Volume int,

    ServiceNumber003Volume int )

    declare @sql varchar(max),

    @columnsToCheck varchar(max)

    SET @columnsToCheck=' WHERE 1 = 1 ' + CHAR(13)

    SELECT @columnsToCheck = @columnsToCheck + ' AND ' + syscolumns.name + ' > 0 ' + CHAR(13)

    FROM syscolumns

    where object_name(id) = 'MyTable'

    and name like 'ServiceNumber%'

    and type_name(xtype) = 'int'

    SELECT @sql = 'SELECT * from MyTable ' + @columnsToCheck

    print @sql

    --results:

    SELECT * from MyTable WHERE 1 = 1

    AND ServiceNumber001Volume > 0

    AND ServiceNumber002Volume > 0

    AND ServiceNumber003Volume > 0

    --exec(@sql)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 8, 2009 at 7:40 am

    #991072

    wow, thanks. It works like a charm!

  • May 8, 2009 at 7:50 am

    #991085

    you probably want to change it to test for OR instead of AND...so it builds like this

    SELECT * from MyTable WHERE 1 = 2 --never happens

    OR ServiceNumber001Volume > 0

    OR ServiceNumber002Volume > 0

    OR ServiceNumber003Volume > 0

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 8, 2009 at 7:54 am

    #991092

    You may need to read about Normalization

    Madhivanan

    Failing to plan is Planning to fail

  • May 8, 2009 at 8:07 am

    #991107

    Normalization is awesome! But considering this is a 'flattened' out table for a 'reporting' data mart it was deemed necessary for 'denormalize' the information for performance reasons. Thanks for the tip!

  • May 8, 2009 at 8:08 am

    #991109

    Lowell, thanks for the 'Or' thing, I changed it a bit and now I'm getting what I need.

  • May 8, 2009 at 3:47 pm

    #991501

    John (5/8/2009)

    Normalization is awesome! But considering this is a 'flattened' out table for a 'reporting' data mart it was deemed necessary for 'denormalize' the information for performance reasons. Thanks for the tip!

    Denormalize data for performance reasons???

    It's like adding load to speed a car up.

    Denormalization adds performance to copy-paste programming, nothing else.

    _____________
    Code for TallyGenerator

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply