Using a CASE to Determine if a Column is included in the SELECT clause

microsoft.public.sqlserver.programming

19 May 2006 3:27 PM

col1blb

I have a query that returns 7 columns, the first is a date and the
other 6 are money columns. Now if the total for any of the money
columns is 0, i dont want to have that column selected. I have my
original select and a table variable to collect the data from the
select and i was trying to use the table variable to select only the
money columns that dont total out to 0 but i'm not sure if its possible
or if it is how to do it. Here is my statement so far.

Declare @WO1 Table
(
    [Collection Date] DATETIME NULL,
    [1] MONEY NULL,
    [2] MONEY NULL,
    [3] MONEY NULL,
    [4] MONEY NULL,
    [5] MONEY NULL,
    [8] MONEY NULL
)
INSERT INTO @WO1 ([Collection Date],[1],[2],[3],[4],[5],[8])
SELECT tbl_WR_WriteOffs.ColDate,
    CASE WHEN WO_reason = '1' THEN WO_Amount ELSE 0    END AS [1],
    CASE WHEN WO_reason = '2' THEN WO_Amount ELSE 0    END AS [2],
    CASE WHEN WO_reason = '3' THEN WO_Amount ELSE 0    END AS [3],
    CASE WHEN WO_reason = '4' THEN WO_Amount ELSE 0    END AS [4],
    CASE WHEN WO_reason = '5' THEN WO_Amount ELSE 0    END AS [5],
    CASE WHEN WO_reason = '8' THEN WO_Amount ELSE 0    END AS [8]
FROM tbl_WR_WriteOffs
WHERE L2_Approved_DateTime BETWEEN '01/01/2006' AND '04/30/2006' AND
tbl_WR_WriteOffs.Center = '6051'

SELECT
    [Collection Date],
    [1],
    [2],
    [3],
    [4],
    [5],
    [8]
FROM @WO1

5 records are in the result and in all 5 records columns [3] and [4]
are 0 so i dont want them selected from the table variable @WO1. Is
this possible?

19 May 2006 4:15 PM

Anith Sen

It is somewhat hard to understand your narrative based on the code snippet
you posted. Please post your table structures, sample data & expected
results, esp. for the actual tables involved. For details refer to:
www.aspfaq.com/5006

--
Anith

19 May 2006 4:27 PM

Will

First off, don't use the money datatype, it has rounding errors built
in, most people go for decimal(18,4) as an alternative.

Secondly I would guess from the way that you are trying to get your
data, that you should check that your data structure is correctly
normalised.

Thirdly in order to achieve your result (which is an odd thing to be
trying to do) you'll have to use dynamic sql, so something like (but
with ColA through to ColG):

DECLARE @SQL nvarchar(4000)
SET @SQL = 'SELECT 1'
SET @SQL = (SELECT CASE WHEN SUM(ColA) != 0 THEN ',COLA' ELSE '' END +
CASE WHEN SUM(ColB) != 0 THEN ',ColB' ELSE '' END FROM MyTable)

SET @SQL = @SQL + ' FROM MyTable WHERE Condition = 1'

exec sp_executesql @SQL

Hope that helps,

Will

col1***@ups.com wrote:
Show quote

> I have a query that returns 7 columns, the first is a date and the
> other 6 are money columns. Now if the total for any of the money
> columns is 0, i dont want to have that column selected. I have my
> original select and a table variable to collect the data from the
> select and i was trying to use the table variable to select only the
> money columns that dont total out to 0 but i'm not sure if its possible
> or if it is how to do it. Here is my statement so far.
>
> Declare @WO1 Table
> (
>     [Collection Date] DATETIME NULL,
>     [1] MONEY NULL,
>     [2] MONEY NULL,
>     [3] MONEY NULL,
>     [4] MONEY NULL,
>     [5] MONEY NULL,
>     [8] MONEY NULL
> )
> INSERT INTO @WO1 ([Collection Date],[1],[2],[3],[4],[5],[8])
> SELECT tbl_WR_WriteOffs.ColDate,
>     CASE WHEN WO_reason = '1' THEN WO_Amount ELSE 0    END AS [1],
>     CASE WHEN WO_reason = '2' THEN WO_Amount ELSE 0    END AS [2],
>     CASE WHEN WO_reason = '3' THEN WO_Amount ELSE 0    END AS [3],
>     CASE WHEN WO_reason = '4' THEN WO_Amount ELSE 0    END AS [4],
>     CASE WHEN WO_reason = '5' THEN WO_Amount ELSE 0    END AS [5],
>     CASE WHEN WO_reason = '8' THEN WO_Amount ELSE 0    END AS [8]
> FROM tbl_WR_WriteOffs
> WHERE L2_Approved_DateTime BETWEEN '01/01/2006' AND '04/30/2006' AND
> tbl_WR_WriteOffs.Center = '6051'
>
> SELECT
>     [Collection Date],
>     [1],
>     [2],
>     [3],
>     [4],
>     [5],
>     [8]
> FROM @WO1
>
> 5 records are in the result and in all 5 records columns [3] and [4]
> are 0 so i dont want them selected from the table variable @WO1. Is
> this possible?

19 May 2006 4:30 PM

Will

correction:

remove the line
SET @SQL = 'SELECT 1'

add the line SET @SQL = 'SELECT 1' + @SQL before the exec statement

19 May 2006 5:07 PM

Jim Underwood

Will,
Why are you using dynamic SQL here at all? Since you are not using
variables, and the SQL is consistent, why not just straight SQL?

Show quote

"Will" <william_p***@yahoo.co.uk> wrote in message
news:1148056061.571426.106940@g10g2000cwb.googlegroups.com...
> First off, don't use the money datatype, it has rounding errors built
> in, most people go for decimal(18,4) as an alternative.
>
> Secondly I would guess from the way that you are trying to get your
> data, that you should check that your data structure is correctly
> normalised.
>
> Thirdly in order to achieve your result (which is an odd thing to be
> trying to do) you'll have to use dynamic sql, so something like (but
> with ColA through to ColG):
>
> DECLARE @SQL nvarchar(4000)
> SET @SQL = 'SELECT 1'
> SET @SQL = (SELECT CASE WHEN SUM(ColA) != 0 THEN ',COLA' ELSE '' END +
> CASE WHEN SUM(ColB) != 0 THEN ',ColB' ELSE '' END FROM MyTable)
>
> SET @SQL = @SQL + ' FROM MyTable WHERE Condition = 1'
>
> exec sp_executesql @SQL
>
> Hope that helps,
>
> Will
>
> col1***@ups.com wrote:
> > I have a query that returns 7 columns, the first is a date and the
> > other 6 are money columns. Now if the total for any of the money
> > columns is 0, i dont want to have that column selected. I have my
> > original select and a table variable to collect the data from the
> > select and i was trying to use the table variable to select only the
> > money columns that dont total out to 0 but i'm not sure if its possible
> > or if it is how to do it. Here is my statement so far.
> >
> > Declare @WO1 Table
> > (
> > [Collection Date] DATETIME NULL,
> > [1] MONEY NULL,
> > [2] MONEY NULL,
> > [3] MONEY NULL,
> > [4] MONEY NULL,
> > [5] MONEY NULL,
> > [8] MONEY NULL
> > )
> > INSERT INTO @WO1 ([Collection Date],[1],[2],[3],[4],[5],[8])
> > SELECT tbl_WR_WriteOffs.ColDate,
> > CASE WHEN WO_reason = '1' THEN WO_Amount ELSE 0 END AS [1],
> > CASE WHEN WO_reason = '2' THEN WO_Amount ELSE 0 END AS [2],
> > CASE WHEN WO_reason = '3' THEN WO_Amount ELSE 0 END AS [3],
> > CASE WHEN WO_reason = '4' THEN WO_Amount ELSE 0 END AS [4],
> > CASE WHEN WO_reason = '5' THEN WO_Amount ELSE 0 END AS [5],
> > CASE WHEN WO_reason = '8' THEN WO_Amount ELSE 0 END AS [8]
> > FROM tbl_WR_WriteOffs
> > WHERE L2_Approved_DateTime BETWEEN '01/01/2006' AND '04/30/2006' AND
> > tbl_WR_WriteOffs.Center = '6051'
> >
> > SELECT
> > [Collection Date],
> > [1],
> > [2],
> > [3],
> > [4],
> > [5],
> > [8]
> > FROM @WO1
> >
> > 5 records are in the result and in all 5 records columns [3] and [4]
> > are 0 so i dont want them selected from the table variable @WO1. Is
> > this possible?
>

19 May 2006 5:35 PM

Jim Underwood

Never mind, you were showing the syntax for dynamic SQL, the example was
just an example.

Show quote

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23j7Eja2eGHA.3456@TK2MSFTNGP05.phx.gbl...
> Will,
> Why are you using dynamic SQL here at all? Since you are not using
> variables, and the SQL is consistent, why not just straight SQL?
>
>
> "Will" <william_p***@yahoo.co.uk> wrote in message
> news:1148056061.571426.106940@g10g2000cwb.googlegroups.com...
> > First off, don't use the money datatype, it has rounding errors built
> > in, most people go for decimal(18,4) as an alternative.
> >
> > Secondly I would guess from the way that you are trying to get your
> > data, that you should check that your data structure is correctly
> > normalised.
> >
> > Thirdly in order to achieve your result (which is an odd thing to be
> > trying to do) you'll have to use dynamic sql, so something like (but
> > with ColA through to ColG):
> >
> > DECLARE @SQL nvarchar(4000)
> > SET @SQL = 'SELECT 1'
> > SET @SQL = (SELECT CASE WHEN SUM(ColA) != 0 THEN ',COLA' ELSE '' END +
> > CASE WHEN SUM(ColB) != 0 THEN ',ColB' ELSE '' END FROM MyTable)
> >
> > SET @SQL = @SQL + ' FROM MyTable WHERE Condition = 1'
> >
> > exec sp_executesql @SQL
> >
> > Hope that helps,
> >
> > Will
> >
> > col1***@ups.com wrote:
> > > I have a query that returns 7 columns, the first is a date and the
> > > other 6 are money columns. Now if the total for any of the money
> > > columns is 0, i dont want to have that column selected. I have my
> > > original select and a table variable to collect the data from the
> > > select and i was trying to use the table variable to select only the
> > > money columns that dont total out to 0 but i'm not sure if its
possible
> > > or if it is how to do it. Here is my statement so far.
> > >
> > > Declare @WO1 Table
> > > (
> > > [Collection Date] DATETIME NULL,
> > > [1] MONEY NULL,
> > > [2] MONEY NULL,
> > > [3] MONEY NULL,
> > > [4] MONEY NULL,
> > > [5] MONEY NULL,
> > > [8] MONEY NULL
> > > )
> > > INSERT INTO @WO1 ([Collection Date],[1],[2],[3],[4],[5],[8])
> > > SELECT tbl_WR_WriteOffs.ColDate,
> > > CASE WHEN WO_reason = '1' THEN WO_Amount ELSE 0 END AS [1],
> > > CASE WHEN WO_reason = '2' THEN WO_Amount ELSE 0 END AS [2],
> > > CASE WHEN WO_reason = '3' THEN WO_Amount ELSE 0 END AS [3],
> > > CASE WHEN WO_reason = '4' THEN WO_Amount ELSE 0 END AS [4],
> > > CASE WHEN WO_reason = '5' THEN WO_Amount ELSE 0 END AS [5],
> > > CASE WHEN WO_reason = '8' THEN WO_Amount ELSE 0 END AS [8]
> > > FROM tbl_WR_WriteOffs
> > > WHERE L2_Approved_DateTime BETWEEN '01/01/2006' AND '04/30/2006' AND
> > > tbl_WR_WriteOffs.Center = '6051'
> > >
> > > SELECT
> > > [Collection Date],
> > > [1],
> > > [2],
> > > [3],
> > > [4],
> > > [5],
> > > [8]
> > > FROM @WO1
> > >
> > > 5 records are in the result and in all 5 records columns [3] and [4]
> > > are 0 so i dont want them selected from the table variable @WO1. Is
> > > this possible?
> >
>
>

19 May 2006 5:37 PM

Will

As I understand his requirement (which is probably a mis-understanding)
he doesn't want the column in his result list if the value is 0.
Perhaps I'm missing something obvious, but how would you not use
dynamic sql?

19 May 2006 5:52 PM

Jim Underwood

Yes, I realized that after my post. I missed the fact that you were
concatenating the results of a SQL statement to your @SQL variable. I
thought the lack of quotes was a typo, rather than an intended tool for
getting the desired column list.

I would probably have split it up into separate steps so that caffeine
deprived individuals like myself can follow it on a Friday afternoon.

As you are interpreting the requirements, I believe you are correct that
dynamic SQL needs to be used.

Show quote

"Will" <william_p***@yahoo.co.uk> wrote in message
news:1148060248.539437.162110@y43g2000cwc.googlegroups.com...
> As I understand his requirement (which is probably a mis-understanding)
> he doesn't want the column in his result list if the value is 0.
> Perhaps I'm missing something obvious, but how would you not use
> dynamic sql?
>