Help with Case

I have the following data with following conditions:

Old Type New Type

Number: 01 in table1 with Connection : 'J' in table2 'GE'

Number: 01 in table1 with Connection : 'K' in table2 'GPE'

Number: 01 in table1 with Connection: 'L' In table2 'GPA'

Number: 02 in table1 with Connection: 'I' in table2 'I02'

I used queries like:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

ELSE CASE WHEN t2.Connection = 'K' THEN 'GPE'

ELSE CASE WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END END END END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID).

I'll take an example:

An ID can have multiple connections attached to it. Let Say:

Table1 ID : 0001

Number: 01

Table2 ID: 0001

Connection: J, K, S, O, P.

Now, when i do the above query, i would get a result of 3 rows new case types for ID 0001.

I would like to just get one, and the first priority will be put on the condition of (GE).

everytime I see a Number '01' and Connection 'J', I would ignore the other connection. If i do not see Number = '01' and Connection: 'J'. i would go for the rest of the conditions.

Hope you guys see what problem i am having.

Thanks,

Jul.

Try this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

WHEN t2.Connection = 'K' THEN 'GPE'

WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

Since I do not like repeating the condition, I would do this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

|||Still, i get multiple results.....Thanks though.|||So you want only one row returned?

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN (

select id, min(Connection) [Connection]

from Table2

group by id) AS t2

ON (t1.ID = t2.ID)

|||It works....Thanks sooo much.....

Help to verify ?

I do a SQL migration. Help to verify whether it's OK.
Old Server: SVR1, SQL Server Enterprise edition
New Server: SVR2, SQL Server Enterprise Edition
There are many user databases and linked server in SVR1,
it's inconvenient to copy database file and attach to new
server. And also need to create user account, etc. Because
all user database registeration info are stored in master
database, so I try to modify master database. Following is
the procedure:
1.At SVR1, set using SQL authentication, stop SQL server.
copy all system and user database to SVR2, save at
c:\olddata
2.At SVR2, attach master.mdf and mastlog.ldf in c:\olddata
as masternew.
3.In three table sysdatabases, sysaltfiles, sysdevices of
masternew database, modify old path to the real path
c:\sqldata.
4.At SVR2, stop all SQL service, copy all file from
c:\oldpath to c:\sqldata, select yes if need to confirm
file replace
5.At SVR2, start SQL Service, use SQL Query analyzer, run
following script:
exec sp_dropserver 'SVR1'
go
exec sp_addserver 'SVR2','local'
go
6.Open SQL Server Enterprise Manager, it seems all is ok.
Is there any problem to do like this? Or I need to do any
more? Your message to me is my pleasure.Hi
The following article describes how to transfer over to a new server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
If you followed the steps then there would be no need copy the master
database, but the following article details how to:
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071
Your process does not seem to transfer any jobs
John
"ronggui" <ronggui999@.hotmail.com> wrote in message
news:001601c34967$c1a43850$a601280a@.phx.gbl...
> I do a SQL migration. Help to verify whether it's OK.
> Old Server: SVR1, SQL Server Enterprise edition
> New Server: SVR2, SQL Server Enterprise Edition
> There are many user databases and linked server in SVR1,
> it's inconvenient to copy database file and attach to new
> server. And also need to create user account, etc. Because
> all user database registeration info are stored in master
> database, so I try to modify master database. Following is
> the procedure:
> 1.At SVR1, set using SQL authentication, stop SQL server.
> copy all system and user database to SVR2, save at
> c:\olddata
> 2.At SVR2, attach master.mdf and mastlog.ldf in c:\olddata
> as masternew.
> 3.In three table sysdatabases, sysaltfiles, sysdevices of
> masternew database, modify old path to the real path
> c:\sqldata.
> 4.At SVR2, stop all SQL service, copy all file from
> c:\oldpath to c:\sqldata, select yes if need to confirm
> file replace
> 5.At SVR2, start SQL Service, use SQL Query analyzer, run
> following script:
> exec sp_dropserver 'SVR1'
> go
> exec sp_addserver 'SVR2','local'
> go
> 6.Open SQL Server Enterprise Manager, it seems all is ok.
> Is there any problem to do like this? Or I need to do any
> more? Your message to me is my pleasure.
>|||Hi,
Because in new server SVR2, there is already a master
database. I want to change all the old paths in the master
database from old server SVR1, so I attached as masternew.
By the way, at old server SVR1 system and user databases
are saved in different path, I need changing path in
master, so it can work in new server SVR2.
After changing path, I stop SQL Server SVR2, and copy all
old files to exact location.
>--Original Message--
>You attached the master database as "Masternew". I
>thought the master database must be called "Master".
>I will only copy all the databases to the exact location
>in SVR2 including "Master", "Msdb" etc as SVR1.
>.
>|||Hi
The articles do show how to move the master database, you should follow that
procedure.
John
"ronggui" <ronggui999@.hotmail.com> wrote in message
news:040b01c349b0$cab4bca0$a301280a@.phx.gbl...
> Hi,
> Because in new server SVR2, there is already a master
> database. I want to change all the old paths in the master
> database from old server SVR1, so I attached as masternew.
> By the way, at old server SVR1 system and user databases
> are saved in different path, I need changing path in
> master, so it can work in new server SVR2.
> After changing path, I stop SQL Server SVR2, and copy all
> old files to exact location.
> >--Original Message--
> >You attached the master database as "Masternew". I
> >thought the master database must be called "Master".
> >
> >I will only copy all the databases to the exact location
> >in SVR2 including "Master", "Msdb" etc as SVR1.
> >.
> >

help to remove old .bak file

I have an old .bak file that is not being removed via our maitenenance plan
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.
Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.
|||Thanks for the suggestion, but I already tried that. There was only tran log
going on for different db. I killed it anyway just to make sure and tried to
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:

> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>
>
|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
|||I believe that http://www.sysinternals.com/ has tools for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran log
> going on for different db. I killed it anyway just to make sure and tried to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean up
> files until then but would prefer if there is another solution. Is there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.

help to remove old .bak file

I have an old .bak file that is not being removed via our maitenenance plan
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.|||Thanks for the suggestion, but I already tried that. There was only tran lo
g
going on for different db. I killed it anyway just to make sure and tried t
o
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:

> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>
>|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>|||I believe that http://www.sysinternals.com/ has tools for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestion, but I already tried that. There was only tran
log
> going on for different db. I killed it anyway just to make sure and tried
to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean u
p
> files until then but would prefer if there is another solution. Is the
re
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.

help to remove old .bak file

I have an old .bak file that is not being removed via our maitenenance plan
backups. When I try to manually remove it, it says 'file in use'. How do
determine what is holding this file open?
The last time the job ran it failed.
We run daily backups and I see current backups and am able to remove some of
the more recent ones manually.Take a look at EM - Current Activity to see if the backup step is still
there. Try KILLing it if it is.
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.|||Thanks for the suggestion, but I already tried that. There was only tran log
going on for different db. I killed it anyway just to make sure and tried to
remove file but still same error. My maintenance window is not until
weekend and I am trying to avoid bouncing server. I can manually clean up
files until then but would prefer if there is another solution. Is there
any what to determine what is holding the file open?
"Jerry Spivey" wrote:
> Take a look at EM - Current Activity to see if the backup step is still
> there. Try KILLing it if it is.
> HTH
> Jerry
> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
> >I have an old .bak file that is not being removed via our maitenenance plan
> > backups. When I try to manually remove it, it says 'file in use'. How
> > do
> > determine what is holding this file open?
> >
> > The last time the job ran it failed.
> >
> > We run daily backups and I see current backups and am able to remove some
> > of
> > the more recent ones manually.
>
>|||Depending on how the maint plans are implemented you might check the
Processes tab in Task Manager. Also, do you need to bounce the server or
just the SQL Server/Agent services? Also, why not wait til off-peak hours
to stop - delete the file?
HTH
Jerry
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...
> Thanks for the suggestion, but I already tried that. There was only tran
> log
> going on for different db. I killed it anyway just to make sure and tried
> to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean
> up
> files until then but would prefer if there is another solution. Is
> there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>> Take a look at EM - Current Activity to see if the backup step is still
>> there. Try KILLing it if it is.
>> HTH
>> Jerry
>> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
>> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>> >I have an old .bak file that is not being removed via our maitenenance
>> >plan
>> > backups. When I try to manually remove it, it says 'file in use'.
>> > How
>> > do
>> > determine what is holding this file open?
>> >
>> > The last time the job ran it failed.
>> >
>> > We run daily backups and I see current backups and am able to remove
>> > some
>> > of
>> > the more recent ones manually.
>>|||I believe that http://www.sysinternals.com/ has tools for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:10519EF6-CBAE-415D-8CF9-A9FABA3C0EDC@.microsoft.com...
> Thanks for the suggestion, but I already tried that. There was only tran log
> going on for different db. I killed it anyway just to make sure and tried to
> remove file but still same error. My maintenance window is not until
> weekend and I am trying to avoid bouncing server. I can manually clean up
> files until then but would prefer if there is another solution. Is there
> any what to determine what is holding the file open?
> "Jerry Spivey" wrote:
>> Take a look at EM - Current Activity to see if the backup step is still
>> there. Try KILLing it if it is.
>> HTH
>> Jerry
>> "DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
>> news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>> >I have an old .bak file that is not being removed via our maitenenance plan
>> > backups. When I try to manually remove it, it says 'file in use'. How
>> > do
>> > determine what is holding this file open?
>> >
>> > The last time the job ran it failed.
>> >
>> > We run daily backups and I see current backups and am able to remove some
>> > of
>> > the more recent ones manually.
>>|||looks at NTHandle from http://www.Sysinternals.com
cheerrs,
Andy
"DBAdan" <DBAdan@.discussions.microsoft.com> wrote in message
news:21D38F1A-C470-45BD-B51E-03031A38F4F0@.microsoft.com...
>I have an old .bak file that is not being removed via our maitenenance plan
> backups. When I try to manually remove it, it says 'file in use'. How
> do
> determine what is holding this file open?
> The last time the job ran it failed.
> We run daily backups and I see current backups and am able to remove some
> of
> the more recent ones manually.