CASE statement vs LOOKUP TABLES

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 2012 Forums

Transact-SQL (2012)

CASE statement vs LOOKUP TABLES

Author

Topic

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-04-09 : 21:12:11

i created a lookup table named CONVERSATION this was based of the following query ( SELECT phone from CALLS where exists (SELECT home_phone, mobile, toll_free_number from CALLS2). Meaning if the phone nunumer in CALLS table exists in any of those 3 columns in CALLS2 it should populate my lookup table (CONVERSION). after that table is created. Each time a new file is loaded i would like to do case statement that looks up the phone numberes in the column phone and if it finds a match in my lookup table it should flag it as 1 which would indicate a contact. I maybe doing too much by creating a lookup table, should i just do the CASE WHEN PHONE = ( SELECT phone from CALLS where exists (SELECT home_phone, mobile, toll_free_number from CALLS2) THEN 1 END ' Phone numbers'.. how would i constuct the case statenment within my query if the LOOKUP table is too much

any HELP WOULD BE APPRECIATED

THANKS

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-10 : 08:15:04

quote:
Originally posted by Blessed1978

i created a lookup table named CONVERSATION this was based of the following query ( SELECT phone from CALLS where exists (SELECT home_phone, mobile, toll_free_number from CALLS2). Meaning if the phone nunumer in CALLS table exists in any of those 3 columns in CALLS2 it should populate my lookup table (CONVERSION). after that table is created. Each time a new file is loaded i would like to do case statement that looks up the phone numberes in the column phone and if it finds a match in my lookup table it should flag it as 1 which would indicate a contact. I maybe doing too much by creating a lookup table, should i just do the CASE WHEN PHONE = ( SELECT phone from CALLS where exists (SELECT home_phone, mobile, toll_free_number from CALLS2) THEN 1 END ' Phone numbers'.. how would i constuct the case statenment within my query if the LOOKUP table is too much

any HELP WOULD BE APPRECIATED

THANKS

First off, if you really created the CONVERSATION table with the query above, it would include all phone numbers in CALLS if there is anything in CALLS2. I assume that CONVERSATION is built like this:


insert into CONVERSATION
    SELECT phone 
    from CALLS 
    where exists (
        SELECT home_phone, mobile, toll_free_number 
        from CALLS2)

Since there's no constraint clause in the sub-query, the insert is all-or nothing. That is, if there is at least one row in CALLS2, every phone in CALLS gets inserted in CONVERSATION. If CALLS2 is empty, nothing gets inserted.

Is this truly what you want?

On the question about a CASE statement, this sounds more like a mission for INNER JOIN. e.g. something like this:


select * from <table containing file just loaded> as f
inner join CALLS2 c
on f.phone in (c.home_phone, movile, toll_free_number)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources