CASE Statement

Hello

How would I go about using the CASE statement with the WHEN and THEN statements with a scenario like this:

======================================--
CASE PhoneNumber

WHEN 9990000000 THEN City1
WHEN 8030000000 THEN City2

ect...
==============================================

What I want to do is check only the first 3 digits - just the 999. There would be 4 different possible first 3 digits:

999, 997, 998, and 803. Each one of these first 3 digits of the 10 digit phone number identifies a city

What I would like to do is iether use a wild card - like this:

CASE PhoneNumber
WHEN LIKE (999%) THEN City1

OR try something like taking the first 3 numbers and assigning it to a variable then doing the CASE statrement like this:

TAKE PhoneNumber 3 and asign it to CHK_NUM

CASE CHK_NUM
WHEN '999' THEN 'City1'

Any ideas would be greatly appriciated

Thanks!If you know the solution, what is the problem then.|||I don't know the solution. My examples do not have the correct syntax - that's what I'm looking for. What would the correct string look like for the solution?

Thanks|||OK - Never Mind, you were right, I had the solution - it works!

Thanks

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:
>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascade Delete

Hi,
I need to delete data from child table when I delete data from parent
table. I think trigger can be used in this scenario.
I want to know your views on using trigger for this purpose.
And can someone give me a sample on how to do it using triggers.
Thanks
Kiranor define your foreign key to cascade on delete. No trigger required
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||You can "automate" deletes using ON DELETE CASCADE when creating/altering
foreign key constraints.
Read more here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
Example:
alter table owner.foreignkey_table
add constraint fk_name
foreign key (foreignkey_column_name)
references owner.primarykey_table
(primarykey_column_name)
on delete cascade
go
ML|||You can accomplish this in SQL Server 2000 without a trigger, using ON
DELETE CASCADE when creating FOREIGN KEY constraints.
The following script illustrates this:
use tempdb
go
set nocount on
go
create table tbl1 (id int not null primary key)
go
insert tbl1 values (1)
insert tbl1 values (2)
go
create table tbl2
(
id int not null primary key
references tbl1 (id) on delete cascade
)
go
insert tbl2 values (1)
insert tbl2 values (2)
go
delete from tbl1
where id = 1
go
select *
from tbl2
go
drop table tbl2
drop table tbl1
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||ML wrote:
> You can "automate" deletes using ON DELETE CASCADE when creating/altering
> foreign key constraints.
> Read more here:
> http://msdn.microsoft.com/library/d... />
z_3ied.asp
> Example:
> alter table owner.foreignkey_table
> add constraint fk_name
> foreign key (foreignkey_column_name)
> references owner.primarykey_table
> (primarykey_column_name)
> on delete cascade
> go
>
> ML
Thanks ML|||Adam Machanic wrote:
> You can accomplish this in SQL Server 2000 without a trigger, using ON
> DELETE CASCADE when creating FOREIGN KEY constraints.
> The following script illustrates this:
>
> use tempdb
> go
> set nocount on
> go
> create table tbl1 (id int not null primary key)
> go
> insert tbl1 values (1)
> insert tbl1 values (2)
> go
> create table tbl2
> (
> id int not null primary key
> references tbl1 (id) on delete cascade
> )
> go
> insert tbl2 values (1)
> insert tbl2 values (2)
> go
> delete from tbl1
> where id = 1
> go
> select *
> from tbl2
> go
> drop table tbl2
> drop table tbl1
> go
>
>
Thanks Adam|||Farmer wrote:
> or define your foreign key to cascade on delete. No trigger required
>
> "Kiran" <kiran@.nospam.net> wrote in message
> news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
>
>
>
thanks Farmer