Suppress output for one column...SOLVED

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Suppress output for one column...SOLVED

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-14 : 13:38:29

Is there a way to supress output on one column in a SP, using data from the same row?

Like This:
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active
FROM dbo.tblResidents

But which returns null for some of the columns if DOT is not null?
DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning.
I tried several variations of the following, but I can't figure it out

CREATE PROCEDURE [dbo].[spTesting] AS
BEGIN
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility
FROM dbo.tblResidents A
WHERE DOT IS NULL
UNION
SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility
FROM dbo.tblResidents I
END
GO

----------------
-Stephen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-14 : 13:59:15

you mean like this: ?


select	[<col1>]
	,case when [<col1>] = 'supress' then null else [<col2>] end as [<supressableColumn>]
from    ...

Be One with the Optimizer
TG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-14 : 13:59:50

You need to use CASE for each conditional column. So a conditional column like DisReason in your SELECT is replaced with

CASE WHEN DOT IS NULL THEN '' ELSE DisReason END AS DisReason

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-14 : 14:05:14

I forgot to mention that when use this technique you should explicitly cast the NULL as the datatype of the value when not null.

Be One with the Optimizer
TG

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-14 : 14:35:16

Thanks to both of you! That worked except that I had to replace '' with NULL or it returns 01/01/1900.

Final working SP (for the other newbies)
BEGIN

SELECT Last, First, Facility, DOP,
CASE WHEN DOT IS NOT NULL THEN NULL ELSE dbo.fnDueDate(DOP, 3, GETDATE()) END AS NextQDue,
CASE WHEN DOT IS NOT NULL THEN NULL ELSE dbo.fnDueDate(DOP, 6, GETDATE()) END AS NextNSPDue,
CASE WHEN DOT IS NOT NULL THEN NULL ELSE DATEADD(m, 1, DOP) END AS InitialNSPDue,
CASE WHEN DOT IS NOT NULL THEN NULL ELSE DATEADD(m, 1, DOP) END AS InitialAssessDue,
DOT, DisReason, DATEADD (m,1, DOT) AS DisSummaryDue
FROM dbo.tblResidents

END
GO

----------------
-Stephen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources