CASE in SELECT to return nulls? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

CASE in SELECT to return nulls?

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-10 : 17:46:24

Here we go again!
Consider for a moment the following SELECT query:

select *
from 
(

SELECT svc_dat,nam,time01,time04,time07,
time10,time13,time16,
cast('testing the error message'  as varchar(512))  as ERR_MSG
 From Timetable
where 1=1 and  svc_dat>=convert(datetime, convert(varchar(10), getdate(), 120))
 and (isnumeric(time01)=1 or isnumeric(time04)=1 or isnumeric(time07)=1
 or isnumeric(time10)=1 or isnumeric(time13)=1
 or isnumeric(time16)=1)
) Query1
left join
(
  Select * from SY_USR
) SY_USR
on Query1.NAM = SY_USR.NAM
 order by SY_USR.USR_ID

This query works fine as long as there is at least one timexx column that meets the ISNUMERIC tests, but if there are NO rows of data that match the tests, it returns nothing as it should.
Is there a way to get this query to return one row of NULLS for all the timexx columns and also the resulting error message when no data meets the ISNUMERIC tests? I need the query to return as-is when data meets the tests, but I still need the error flag ecen when NO data meets the test. The timexx columns are varchar, and the ISNUMERIC test is used to find any rows that have a document number in them and ignore the rest. Seems like a job for CASE to me, but I just can't seem to get my head around it..
Andy
btw- the "1=1" thing is for Crystal; it is a placeholder for filter text passed in by our app-

There's never enough time to type code right,
but always enough time for a hotfix...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 18:32:30

Can you give us a DDL and some data?

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-10 : 18:57:09

you should consider normalizing your table if you can; it makes things MUCH simplier.

- Jeff

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-10 : 19:05:25

Hey TG,
The table is populated off a proc and a udf. here is the table:


create table TimeTable
(
SVC_DAT T_DAT, NAM T_NAM,
CONSTRAINT
	PK_TimeTable PRIMARY KEY CLUSTERED 
	(
	SVC_DAT,
	NAM),
Time01 T_DOC_NO, Time02 T_DOC_NO, Time03 T_DOC_NO, Time04 T_DOC_NO,
Time05 T_DOC_NO, Time06 T_DOC_NO, Time07 T_DOC_NO, Time08 T_DOC_NO,
Time09 T_DOC_NO, Time10 T_DOC_NO, Time11 T_DOC_NO, Time12 T_DOC_NO,
Time13 T_DOC_NO, Time14 T_DOC_NO, Time15 T_DOC_NO, Time16 T_DOC_NO,
Time17 T_DOC_NO, City01 T_CITY, CIty02 T_CITY, CITY03 T_CITY, City04 T_CITY,
City05 T_CITY, City06 T_CITY, City07 T_CITY, City08 T_CITY,
City09 T_CITY, City10 T_CITY, City11 T_CITY, City12 T_CITY,
City13 T_CITY, City14 T_CITY, City15 T_CITY, City16 T_CITY,
City17 T_CITY, Cust01 T_LST_NAM, Cust02 T_LST_NAM, Cust03 T_LST_NAM, Cust04 T_LST_NAM,
Cust05 T_LST_NAM, Cust06 T_LST_NAM, Cust07 T_LST_NAM, Cust08 T_LST_NAM, Cust09 T_LST_NAM,
Cust10 T_LST_NAM, Cust11 T_LST_NAM, Cust12 T_LST_NAM, Cust13 T_LST_NAM, Cust14 T_LST_NAM,
Cust15 T_LST_NAM, Cust16 T_LST_NAM, Cust17 T_LST_NAM
)

Here is the proc:


create procedure pr_CreateTimeTable
as

set nocount on

declare @RunDate datetime,
@PrevDate datetime,
@StartDate datetime,
@EndDate datetime,
@DateCount datetime,
@WriteAhead int,
@DeleteThreshold int

--set write ahead to 1 month
set @WriteAhead = 1

--set delete threshold to 6 months
set @DeleteThreshold = 6

--find today's date
set @RunDate = convert(datetime, convert(varchar(10), getdate(), 120))

--find date that procedure last ran
select @PrevDate = max(SVC_DAT) from TimeTable

if @PrevDate is null
set @PrevDate = dateadd(dd, -1, @RunDate)

--set start date to day after last run
set @StartDate = dateadd(dd, @WriteAhead, @PrevDate)

--set end date to next month 
set @EndDate = dateadd(mm, 1, @RunDate)

set @DateCount = @StartDate

--loop through dates and insert records
while @DateCount <= @EndDate

begin

insert TimeTable
(
SVC_DAT, NAM,
Time01, Time02, Time03, Time04,
Time05, Time06, Time07, Time08,
Time09, Time10, Time11, Time12,
Time13, Time14, Time15, Time16,
Time17, City01, City02, City03,
City04, City05, City06, City07,
City08, City09, City10, City11,
City12, City13, City14, City15,
City16, City17, Cust01, Cust02,
Cust03, Cust04, Cust05, Cust06,
Cust07, Cust08, Cust09, Cust10,
Cust11, Cust12, Cust13, Cust14,
Cust15, Cust16, Cust17
)
select @DateCount,
NAM,
dbo.udf_DefaultTime(@datecount, NAM, 1),
dbo.udf_DefaultTime(@datecount, NAM, 2),
dbo.udf_DefaultTime(@datecount, NAM, 3),
dbo.udf_DefaultTime(@datecount, NAM, 4),
dbo.udf_DefaultTime(@datecount, NAM, 5),
dbo.udf_DefaultTime(@datecount, NAM, 6),
dbo.udf_DefaultTime(@datecount, NAM, 7),
dbo.udf_DefaultTime(@datecount, NAM, 8),
dbo.udf_DefaultTime(@datecount, NAM, 9),
dbo.udf_DefaultTime(@datecount, NAM, 10),
dbo.udf_DefaultTime(@datecount, NAM, 11),
dbo.udf_DefaultTime(@datecount, NAM, 12),
dbo.udf_DefaultTime(@datecount, NAM, 13),
dbo.udf_DefaultTime(@datecount, NAM, 14),
dbo.udf_DefaultTime(@datecount, NAM, 15),
dbo.udf_DefaultTime(@datecount, NAM, 16),
dbo.udf_DefaultTime(@datecount, NAM, 17),
dbo.udf_DefaultTime(@datecount, NAM, 18),
dbo.udf_DefaultTime(@datecount, NAM, 19),
dbo.udf_DefaultTime(@datecount, NAM, 20),
dbo.udf_DefaultTime(@datecount, NAM, 21),
dbo.udf_DefaultTime(@datecount, NAM, 22),
dbo.udf_DefaultTime(@datecount, NAM, 23),
dbo.udf_DefaultTime(@datecount, NAM, 24),
dbo.udf_DefaultTime(@datecount, NAM, 25),
dbo.udf_DefaultTime(@datecount, NAM, 26),
dbo.udf_DefaultTime(@datecount, NAM, 27),
dbo.udf_DefaultTime(@datecount, NAM, 28),
dbo.udf_DefaultTime(@datecount, NAM, 29),
dbo.udf_DefaultTime(@datecount, NAM, 30),
dbo.udf_DefaultTime(@datecount, NAM, 31),
dbo.udf_DefaultTime(@datecount, NAM, 32),
dbo.udf_DefaultTime(@datecount, NAM, 33),
dbo.udf_DefaultTime(@datecount, NAM, 34),
dbo.udf_DefaultTime(@datecount, NAM, 35),
dbo.udf_DefaultTime(@datecount, NAM, 36),
dbo.udf_DefaultTime(@datecount, NAM, 37),
dbo.udf_DefaultTime(@datecount, NAM, 38),
dbo.udf_DefaultTime(@datecount, NAM, 39),
dbo.udf_DefaultTime(@datecount, NAM, 40),
dbo.udf_DefaultTime(@datecount, NAM, 41),
dbo.udf_DefaultTime(@datecount, NAM, 42),
dbo.udf_DefaultTime(@datecount, NAM, 43),
dbo.udf_DefaultTime(@datecount, NAM, 44),
dbo.udf_DefaultTime(@datecount, NAM, 45),
dbo.udf_DefaultTime(@datecount, NAM, 46),
dbo.udf_DefaultTime(@datecount, NAM, 47),
dbo.udf_DefaultTime(@datecount, NAM, 48),
dbo.udf_DefaultTime(@datecount, NAM, 49),
dbo.udf_DefaultTime(@datecount, NAM, 50),
dbo.udf_DefaultTime(@datecount, NAM, 51)

from SY_USR
where IS_SERVICER='Y'

set @DateCount = dateadd(dd, 1, @DateCount)

end

--delete records older than threshold
delete
from TimeTable
where datediff(mm, SVC_DAT, @RunDate) > @DeleteThreshold

And here is the udf:


create function udf_DefaultTime
(
@SVC_DAT T_DAT,
@NAM T_NAM,
@TimeSlot int
)
returns varchar(15)
as

begin

declare @OutString varchar(15),
@WorkDays char(7),
@Lunchtime char(51),
@DayWeek int

/*
@DayWeek defined as: Sunday=1, Monday=2, Tuesday=3...Saturday=7

WorkDays field in SY_USR table should be char(7)
and formatted like 'SMTWTFS' if they work all seven days
any days that aren't worked should be changed to X like 'XMTWTXS'

WorkTimes field in SY_USR table should be char(51)
and formatted like 'XXXBXXLLXXXXXBXXXX;XXXBXXLLXXXXXBXXXX;XXXBXXLLXXXXXBXXXX'
X indicates working, B indicates Break and L indicates lunch
it must repeat 3 times to fill time city and last name columns (no semicolons)*/

select @WorkDays = WorkDays,
@Lunchtime = Lunchtime
from SY_USR
where NAM = @NAM

set @DayWeek = datepart(dw, @SVC_DAT)

set @OutString = null

if substring(@Workdays, @DayWeek, 1) = 'X'
set @OutString = 'NO SVC'

if @OutString is null
set @OutString = case substring(@Lunchtime, @TimeSlot, 1)
when 'B' then 'BREAK'
when 'L' then 'LUNCH'
else null
end

return @OutString

end

You will also need to have an SY_USR table:



BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.SY_USR
	(
	usr_id varchar(1) NOT NULL,
	NAM varchar(50) NULL,
	IS_SERVICER varchar(1) NULL,
        Workdays varchar(7) NULL,
        Lunchtime varchar (51) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
	PK_Table1_1 PRIMARY KEY CLUSTERED 
	(
	usr_id
	) ON [PRIMARY]

GO
COMMIT

And finally, here is some data:

Insert into SY_USR (usr_id,NAM,IS_SERVICER,Workdays,Lunchtime)
VALUES (1,Joe Blow,Y,XMTWTXS,XXBXXLLXXXXXBXXXXXXXBXXLLXXXXXBXXXXXXXBXXLLXXXXXBXX)

All of this mess will create the timetable with one user and no numeric entries in the timexx columns. using the original query I posted will return nothing from the timetable, which is where we need to get the err_msg to come over. if you put a numeric entry into any of the timexx columns used in the proc(01,04,07,10,13, or 16), then the query will return that row with the err_msg.
Aren't you glad you asked?

There's never enough time to type code right,
but always enough time for a hotfix...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 22:26:01

Yes, I'm very sorry I asked :) I couldn't even start to work on this because all your datatypes are custom types. Also, I'm just going to pretend that whatever the logic is behind that mess is the best way to do it.

If your objective is to return one row of nulls if your query doesn't return results, how about if you check @@Rowcount after the select. If it's 0, do a seperate select:
Select cast(null as T_DOC_NO) as Time01, cast(null as T_DOC_NO) as Time02, etc...

Be One with the Optimizer
TG

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-11 : 15:00:15

Hey TG!!
Sorry, I forgot the custom datatype stuff....
It's Ok, I came up with a solution just now within Crystal Reports that will allow me to handle the exception when no data exists in the query. This whole timetable thing is unreal:
Denormalized tables, update tests that loop within loops (Get a load of the cursor ROWCOUNT test at the beginning; isn't this cool??),
and of course, udf's and the ever popular Crystal Reports.
Life is good....
Anyway, I already knew about doing a @@ROWCOUNT, but that would require resetting the @SQL select in order to get into Crystal, which is something that Crystal just does not like doing (once a query is executed, Crystal gets an attitude if you do it again)
Hey Dr. Cross Join!
That's it?
Just "Normalize if possible?"

After reading some of your posts (really good stuff!), I expected to see some multi-table-invert-join-not-in-if-exists-then-replace-after-update-while-having query to do the job!

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources