CASE Function - SQL Server Forums

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

Transact-SQL (2008)

CASE Function

Author

Topic

RajJol
Starting Member

17 Posts

Posted - 2010-10-04 : 08:43:17

Hi All,
I am trying to us the 'CASE Function' but am not having any luck. I currently have a number of tables in a database all of which are prefixed with a letter corresponding to a system. For example a table called 'a_player' will come from a system called 'Attacking' and 'd_palyer' will come from a table called 'Defensive'.

I want the result of a loop I have to tell me where each table has come from (i.e. Attacking or Defensive). I have tried using the following code but have had no luck.

case
when (left(t.table_name,1)='a')
then 'attacking'
when (left(t.table_name,1)='d')
then 'defensive'
else 'Other'
end as 'Source'

Any help will be great.
Thank You in advanced.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-04 : 08:58:17

A loop through what?

No, you're never too old to Yak'n'Roll if you're too young to die.

RajJol
Starting Member

17 Posts

Posted - 2010-10-04 : 09:03:19

I have a database that holds around 100 tables. Each of these tables have a number of different columns with a number of different rows.

I have a loop that checks all the tables within the database that holds a column called 'Date' and then checks, in each table, that the date matches today's date (YYYY-MM-DD).

Now I am trying to use the CASE function to categorize the diffrent tables once the loop have gone round.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-04 : 09:12:34

How do you loop through the tables?
Show your code if possible.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 10:02:32

i think he's using a cursor and looking for tables in db

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RajJol
Starting Member

17 Posts

Posted - 2010-10-04 : 12:05:25

Used this loop as pointed out to me by another forum user: -

http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 03:09:00

Insert this exactly!


case
when left('''+t.table_name+''',1)=''a'' then ''attacking''
when left('''+t.table_name+''',1)=''d'' then ''defensive''
else ''Other''
end as Source,

No, you're never too old to Yak'n'Roll if you're too young to die.

RajJol
Starting Member

17 Posts

Posted - 2010-10-05 : 04:39:12

Where do I add this code to the loop from my last thread? I have tried at the end but keep getting the following error message.

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'case'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 04:42:26

We can't help if you don't show us the code.

No, you're never too old to Yak'n'Roll if you're too young to die.

RajJol
Starting Member

17 Posts

Posted - 2010-10-05 : 04:49:02

declare @sql varchar(max),@search varchar(100)
set @sql=''
set @search='insertdate'
select
@sql=@sql+'select'''' ''Source'', '''+ t.TABLE_NAME+''' ''TableName'', CONVERT(CHAR(10), InsertDate, 120) ''InsertDate'', DATEDIFF (d,InsertDate, GETDATE()) ''DaysOld'', COUNT(*) ''Count'' FROM'+' ['+t.TABLE_NAME+']'+' GROUP BY CONVERT(CHAR(10), InsertDate, 120), DATEDIFF (d,InsertDate, GETDATE()) union all '
FROM information_schema.tables as t
where
t.table_type='BASE TABLE' and t.TABLE_NAME in ('A_Players','Players_Accounts','D_Payments') --data_type like '%char%'
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
case
when left('''+t.table_name+''',1)=''a'' then ''attacking''
when left('''+t.table_name+''',1)=''d'' then ''defensive''
else ''Other''
end as Source

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 05:10:36

maybe this:

declare @sql varchar(max),@search varchar(100) 
set @sql=''
set @search='insertdate' 
select 
@sql=@sql+'select  
case
when left('''+t.table_name+''',1)=''a'' then ''attacking''
when left('''+t.table_name+''',1)=''d'' then ''defensive''
else ''Other''
end as Source,
'''+ t.TABLE_NAME+''' TableName, 
CONVERT(CHAR(10), InsertDate, 120) InsertDate, 
DATEDIFF (d,InsertDate, GETDATE()) DaysOld, 
COUNT(*) Count FROM'+' ['+t.TABLE_NAME+']'+' GROUP BY CONVERT(CHAR(10), InsertDate, 120), DATEDIFF (d,InsertDate, GETDATE()) union all '
FROM information_schema.tables as t
where
t.table_type='BASE TABLE' and t.TABLE_NAME in ('A_Players','Players_Accounts','D_Payments') --data_type like '%char%' 
set @sql=left(@sql,len(@sql)-10) 
exec(@sql)

No, you're never too old to Yak'n'Roll if you're too young to die.

RajJol
Starting Member

17 Posts

Posted - 2010-10-05 : 06:12:22

Excellent….worked fine thank you WebFred.

I wonder if you can help me with another problem I am having. From the code you posted, I need to be able to look at ALL my tables in my database and only return the tables that have a column called 'date'. At the moment I am specifying the tables to look at within the code (i.e. 'A_Players','Players_Accounts','D_Payments').

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources