CASE function result with result expression values (for IN keyword)

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE
GROUP.GROUP_ID = 2
AND DEPT.DEPT_ID = 'D'
AND WORK_TYPE_ID IN
(
CASE DEPT_ID
WHEN 'D' THEN 'A','B','C' <- ERROR
WHEN 'F' THEN 'C','D
ELSE 'A','B','C','D'
END
)

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'WHERE'.

which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

AND

(

(CASE DEPT_ID = 'D' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID = 'F' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID != 'D' AND CASE DEPT_ID != 'F' AND

WORK_TYPE_ID IN ('A','B','C'))

)

Though this could lead to bad performance :-(

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi Jens,

Thanks for the reply . It works, and I agree with you that it could lead to performance degradation.

However, if the number of records involved are filtered and limited to, say under 1000 rows, it would still be managable ? Just a feeling, I know it is hard to quantify the expense of a query by just the row count alone.

Kenny

CASE does not give correct result

How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)

Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.

The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').

What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0

The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.

When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.

So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.

Does anyone know how I can make this work?Read this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67923𺇃|||You said you wanted iron clad?
-- prepare test data
declare @.test table (t varchar(50))

insert @.test
select 'ov1' union all
select 'ov2' union all
select 'ov4' union all
select 'ov31' union all
select 'ssss1' union all
select 'ssss99'

-- show data before inserting new values
select * from @.test

-- do the work
declare @.prefix varchar(50)

select @.prefix = 'ov' -- Throw anything in!

insert @.test
select @.prefix + convert(varchar, t.n + 1)
from (
select max(convert(int, substring(t, 1 + len(@.prefix), 50))) n
from @.test
where t like @.prefix + '%'
and not substring(t, 1 + len(@.prefix), 50) like '%[^0-9]%'
) t
where not @.prefix like '%[0-9]%'
and not t.n like '%[^0-9]%'

-- show data after inserting new values
select * from @.test|||I honestly tried to fit/convert your solution to my problem but I simply do not understand what you mean with things like "@.test" and "@.prefix".

This is mainly because I am merely a functional consultant in SAP Software and not a technical specialist like you. Is it therefore possible to explain your solution more in my context? :-)|||@.test is the table used for this test.
@.prefix is the first couple of characters used for searching a client number. you set @.prefix to the client number you want, in your case "c" or "s".

Care and Feeding of Databases

I've been wrestling with some questions about databases.
1. I allow my databases to grow 10% and I find that I will have tons of
excess space according to EM database properties. I try to shrink but
it doesn't. I try a full databack up and transaction backup, but the
shrink still doens't help. (I understand that it may be bad form to
allow the database to automaticly grow, however I wish to learn of
whats going on here. Changing the option to prevent growth wont teach
me anything) ;)
2. Because of the above situation, I've found a brute force way: Create
a smaller database (the original size) and use Import/Export to copy
all the data and objects. However, many times this process will fail
with vague errors, (when I check for details about the error, it just
restates it had an error, nothing I could sink my teeth into).

Anyone have links or info that speaks to these situations, or "Do's and
Dont's" about database maintance?
TIA1. It's not really clear (to me) what the exact problem is. Do you mean
that you 'successfully' shrank the database, but the file sizes didn't
change? If so, how did you shink it - with Enterprise Manager, or with
DBCC SHRINKDATABASE/SHRINKFILE (see Books Online)? Did you get any
error messages? How big is the database, and what size are you trying
to shrink it to?

2. No idea - what exactly are you doing, and what are the errors?

You might find these links useful:

http://support.microsoft.com/defaul...kb;en-us;315512
http://support.microsoft.com/defaul...kb;en-us;272318
http://support.microsoft.com/defaul...kb;en-us;324432
http://www.sql-server-performance.c...se_settings.asp

Finally, have you installed the latest servicepack? There are a number
of fixes documented in the Knowledge Base related to shrinking
databases.

Simon|||Thanks Simon,
As an example: Databae DS_V5_TARGET is 23,328MB with 16,086 MB free. I
think the original size that I created was 2,048MB. I try to shrink it,
no change in size. I then learned that I have to backup the
transactions so that they can be flagged as clearable (not sure the
exact terms). So I did a backup via EM, shrink still wont work (via
EM). I dont get errors, i just dont get the files to shrink in size. I
will say, that I have shrunk the databases in the past succesfully, it
just seems hit or miss.
I know point 2 is very vague, I'll try to recreate and post follow ups.
running with SP4.
Thanks for the links
Rob|||Simon,
The third article you listed is esentially what I've been doing...
Thanks!
Rob|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1119367627.676850.223550@.g44g2000cwa.googlegr oups.com...
> Thanks Simon,
> As an example: Databae DS_V5_TARGET is 23,328MB with 16,086 MB free. I
> think the original size that I created was 2,048MB. I try to shrink it,
> no change in size. I then learned that I have to backup the
> transactions so that they can be flagged as clearable (not sure the
> exact terms). So I did a backup via EM, shrink still wont work (via
> EM). I dont get errors, i just dont get the files to shrink in size. I
> will say, that I have shrunk the databases in the past succesfully, it
> just seems hit or miss.

First question:
Why are you letting it grow in the first place. You're better off
keeping it one size. (i.e. not growing and shrinking it.)

Second question: Where is most of the space, in the DB file or the log file?
If it's the log file most likely the "virtual log" is at the end of the
physical log file. (I thought SQL 2000 "fixed" this issue but I haven't
really looked into it.)

Suggestion:
Don't let DB grow by 10% if you do insist on using autogrowth. Use a fixed
amount. Otherwise each time it grows it'll grow by a larger amount each
time.

> I know point 2 is very vague, I'll try to recreate and post follow ups.
> running with SP4.
> Thanks for the links
> Rob

Capacity Planning

We are going to install a call centre application.
According to end user, there will be around 500 request
has to be inputted into the system.
We will use SQL Server 2000 as the DB. We would like to
know what factors we have to consider - Like recovery
model, database maintenance plan, fill factors ? Is it
necessary for us to archive some old data to an archive
datbase ?
Thanks"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:1cb701c4b569$d40000a0$a401280a@.phx.gbl...
> We are going to install a call centre application.
> According to end user, there will be around 500 request
> has to be inputted into the system.
>
500 requests over what time period?
> We will use SQL Server 2000 as the DB. We would like to
> know what factors we have to consider - Like recovery
> model, database maintenance plan, fill factors ? Is it
> necessary for us to archive some old data to an archive
> datbase ?
>
Those are really business decisions.
i.e. if you need to run 24x7 vs 9-5x5, your decisions will be different.
If you can do with downtime, you may go with a different decisions on
architecture.
As for archiving, again, that's a business decision. Do you want to archive
data or not?
> Thanks|||Dear Greg,
It should be 500 requests between 9:00am to 5:00pm from
Monday to Friday.
We will make full database backup daily. What is the
difference between full database backup and archive then ?
Thanks|||"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0fba01c4b574$0e2f4830$a501280a@.phx.gbl...
> Dear Greg,
> It should be 500 requests between 9:00am to 5:00pm from
> Monday to Friday.
500 a day? That's about one a minute. You can run this thing on a desktop
machine.
> We will make full database backup daily. What is the
> difference between full database backup and archive then ?
Generally a backup is for disaster recovery. An archive is for storing data
for later analysis or for other reasons.
For example, I keep backups of only a few days (my databases generally have
enough churn that in a few days the bulk of the data has changed anyway.)
But there's certain data I archive to tape (in a different schema etc) that
I may keep for much longer.
Now, as for once a day backups, that may or may not work. Your database
sounds like it will probably be fairly small to start, so recovery time will
be about the same as backup. i.e. if it takes 10 minutes to backup, it'll
take about 10 minutes to restore. Plus any time to fix up minor issues.
However, let's say you start a backup at 5:01 PM.
What happens if your DB crashes at 5:00 PM. Can you afford to lose a day's
worth of data?
> Thanks
>

Capacity Planning

We are going to install a call centre application.
According to end user, there will be around 500 request
has to be inputted into the system.
We will use SQL Server 2000 as the DB. We would like to
know what factors we have to consider - Like recovery
model, database maintenance plan, fill factors ? Is it
necessary for us to archive some old data to an archive
datbase ?
Thanks"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:1cb701c4b569$d40000a0$a401280a@.phx.gbl...
> We are going to install a call centre application.
> According to end user, there will be around 500 request
> has to be inputted into the system.
>
500 requests over what time period?

> We will use SQL Server 2000 as the DB. We would like to
> know what factors we have to consider - Like recovery
> model, database maintenance plan, fill factors ? Is it
> necessary for us to archive some old data to an archive
> datbase ?
>
Those are really business decisions.
i.e. if you need to run 24x7 vs 9-5x5, your decisions will be different.
If you can do with downtime, you may go with a different decisions on
architecture.
As for archiving, again, that's a business decision. Do you want to archive
data or not?

> Thanks|||Dear Greg,
It should be 500 requests between 9:00am to 5:00pm from
Monday to Friday.
We will make full database backup daily. What is the
difference between full database backup and archive then ?
Thanks|||"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0fba01c4b574$0e2f4830$a501280a@.phx.gbl...
> Dear Greg,
> It should be 500 requests between 9:00am to 5:00pm from
> Monday to Friday.
500 a day? That's about one a minute. You can run this thing on a desktop
machine.

> We will make full database backup daily. What is the
> difference between full database backup and archive then ?
Generally a backup is for disaster recovery. An archive is for storing data
for later analysis or for other reasons.
For example, I keep backups of only a few days (my databases generally have
enough churn that in a few days the bulk of the data has changed anyway.)
But there's certain data I archive to tape (in a different schema etc) that
I may keep for much longer.
Now, as for once a day backups, that may or may not work. Your database
sounds like it will probably be fairly small to start, so recovery time will
be about the same as backup. i.e. if it takes 10 minutes to backup, it'll
take about 10 minutes to restore. Plus any time to fix up minor issues.
However, let's say you start a backup at 5:01 PM.
What happens if your DB crashes at 5:00 PM. Can you afford to lose a day's
worth of data?

> Thanks
>

Capacity Planning

We are going to install a call centre application.
According to end user, there will be around 500 request
has to be inputted into the system.
We will use SQL Server 2000 as the DB. We would like to
know what factors we have to consider - Like recovery
model, database maintenance plan, fill factors ? Is it
necessary for us to archive some old data to an archive
datbase ?
Thanks
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:1cb701c4b569$d40000a0$a401280a@.phx.gbl...
> We are going to install a call centre application.
> According to end user, there will be around 500 request
> has to be inputted into the system.
>
500 requests over what time period?

> We will use SQL Server 2000 as the DB. We would like to
> know what factors we have to consider - Like recovery
> model, database maintenance plan, fill factors ? Is it
> necessary for us to archive some old data to an archive
> datbase ?
>
Those are really business decisions.
i.e. if you need to run 24x7 vs 9-5x5, your decisions will be different.
If you can do with downtime, you may go with a different decisions on
architecture.
As for archiving, again, that's a business decision. Do you want to archive
data or not?

> Thanks
|||Dear Greg,
It should be 500 requests between 9:00am to 5:00pm from
Monday to Friday.
We will make full database backup daily. What is the
difference between full database backup and archive then ?
Thanks
|||"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0fba01c4b574$0e2f4830$a501280a@.phx.gbl...
> Dear Greg,
> It should be 500 requests between 9:00am to 5:00pm from
> Monday to Friday.
500 a day? That's about one a minute. You can run this thing on a desktop
machine.

> We will make full database backup daily. What is the
> difference between full database backup and archive then ?
Generally a backup is for disaster recovery. An archive is for storing data
for later analysis or for other reasons.
For example, I keep backups of only a few days (my databases generally have
enough churn that in a few days the bulk of the data has changed anyway.)
But there's certain data I archive to tape (in a different schema etc) that
I may keep for much longer.
Now, as for once a day backups, that may or may not work. Your database
sounds like it will probably be fairly small to start, so recovery time will
be about the same as backup. i.e. if it takes 10 minutes to backup, it'll
take about 10 minutes to restore. Plus any time to fix up minor issues.
However, let's say you start a backup at 5:01 PM.
What happens if your DB crashes at 5:00 PM. Can you afford to lose a day's
worth of data?

> Thanks
>