NOT IN multiple column query quandry..

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

Transact-SQL (2000)

NOT IN multiple column query quandry..

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-04-25 : 11:14:22

Hi Gurus,
Long time no post for me...
Have a look at the followinf select statement:

select tkt_no
from ps_tkt_hdr
where is_svc_call='Y'
and tkt_no not in (select time01 from timetable where isnumeric(time01)=1 and svc_dat>=getdate())

Simple enough, and this works fine. Problem is, I need to compare TKT_NO to several columns in Timetable, and I can't seem to get it right. I need something like:

select tkt_no
from ps_tkt_hdr
where is_svc_call='Y'
and tkt_no not in (select time01,time02 from timetable where isnumeric(time01)=1 and svc_dat>=getdate())

This is the denormalized timetable from hell that I need to rewrite, but I haven't gotten permission from the powers that be just yet.
Can anyone sort this out?
Andy

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

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 11:27:37

a join instead of the not exists or not in operator perhaps?

--------------------
keeping it simple...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 11:29:03

Here's one way...


select tkt_no
from ps_tkt_hdr
where is_svc_call='Y'
and tkt_no not in (
select time01 from timetable where isnumeric(time01)=1 and svc_dat>=getdate()
union
select time02 from timetable where isnumeric(time01)=1 and svc_dat>=getdate()
)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-04-25 : 12:04:55

Ah Ryan,
Right on....
I spent most of the morning sticking that damned UNION in the wrong place...
Thanks Again!
Andy

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

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 12:10:17

It's probably not the best way (a join or not exists as jen suggests probably is), but if it works for you...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-04-25 : 16:56:37

Yep,
She is probably right. This view is for reporting purposes, and the UNION works fine. I'll be tearing down this denormalized mess this summer, so we'll deal with it then...
Thanks!
Andy

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-26 : 01:58:46

Also InNumeric is not always reliable. Read this
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-04-27 : 07:01:22

AndyHey Madhivanan,
I never gave it a thought, but the Isnumeric problem makes sense. Thanks for pointing it out. The Timetable table columns are populated one of two ways; either a user looks up an invoice number (through the app) which is always an integer, or they can select some varchar words like 'OFF' or 'LUNCH'. I don't think Isnumeric will cause me a problem is this particular situation, but what do you think? I've been cramming SQL school like mad since the first of the year, trying to learn it all...

Andy

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 09:12:37

If you have alphabets in the column then IsNumeric will return 0. No problem on that

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources