Hi,

This is what I want to do. For a data acquisition query, if condition
A is met, set weight to 1, else if condition B is met, set weight to 2
etc.
Is this possible? I thought about using CASE function but to no
avail.

Using the classical Northwind db as target db, and its employees table
as target table, if [note] column contains 'BA', then I want to set
weight to 1, else if it contains 'BTS', then I want to set weight to 2
etc. Weight or the like is an auxillary artificially created column.

Something like

DECLARE @col int

select firstname, lastname, @col =
(
CASE notes
when '%BA%' then 1
when '%BTS%' then 2
else 0
END as aiCOL
)
-- and I'd like to alias @col as aiCOL or whatever
from employees
where notes LIKE
'%BA%'
OR
notes LIKE '%BTS%'

failed.

Thanks.

OK, so, I don't need the var, thanks.

"Anith Sen" <anith@bizdatasolutions.com> wrote in message news:<faTYa.90456$3o3.6253866@bgtnsc05-news.ops.worldnet.att.net>...
> Your CASE expression syntax should be search-based WHEN clause like :
>
> SELECT fname, lname,
> CASE WHEN notes LIKE '%ba% THEN 1
> WHEN notes LIKE '%bt% THEN 2
> ELSE 0
> END
> FROM Employees
> WHERE ..
>
> Also, you cannot do variable assignment & data retrieval together in a
> single SELECT statement. If you need to get the return result of the CASE
> expression into a variable @col, then you have to remove the other columns
> in the SELECT list.

Doug,

You've got some simple syntax errors. Does this help?

select
firstname,
lastname,
case
when notes like '%BA%' then 1
when notes like '%BTS%' then 2
else 0 end as aiCOL
from Northwind..Employees
where notes LIKE '%BA%' or notes LIKE '%BTS%'

-- Steve Kass
-- Drew University
-- Ref: 5CAD7D03-5761-4ECC-8E43-82E2A0F67529

Doug Baroter wrote:

>Hi,
>
>This is what I want to do. For a data acquisition query, if condition
>A is met, set weight to 1, else if condition B is met, set weight to 2
>etc.
>Is this possible? I thought about using CASE function but to no
>avail.
>
>Using the classical Northwind db as target db, and its employees table
>as target table, if [note] column contains 'BA', then I want to set
>weight to 1, else if it contains 'BTS', then I want to set weight to 2
>etc. Weight or the like is an auxillary artificially created column.
>
>Something like
>
>DECLARE @col int
>
>select firstname, lastname, @col =
> (
> CASE notes
> when '%BA%' then 1
> when '%BTS%' then 2
> else 0
> END as aiCOL
> )
> -- and I'd like to alias @col as aiCOL or whatever
>from employees
>where notes LIKE
> '%BA%'
> OR
> notes LIKE '%BTS%'
>
>failed.
>
>Thanks.
>
>

Thanks, Steve, I got it from the first responder, please check out a
new question just posted entitled "Tough problem".

Steve Kass <skass@drew.edu> wrote in message news:<bh2041$djn$1@slb4.atl.mindspring.net>...
> Doug,
>
> You've got some simple syntax errors. Does this help?
>
> select
> firstname,
> lastname,
> case
> when notes like '%BA%' then 1
> when notes like '%BTS%' then 2
> else 0 end as aiCOL
> from Northwind..Employees
> where notes LIKE '%BA%' or notes LIKE '%BTS%'
>
> -- Steve Kass
> -- Drew University
> -- Ref: 5CAD7D03-5761-4ECC-8E43-82E2A0F67529
>
> Doug Baroter wrote:
>
> >Hi,
> >
> >This is what I want to do. For a data acquisition query, if condition
> >A is met, set weight to 1, else if condition B is met, set weight to 2
> >etc.
> >Is this possible? I thought about using CASE function but to no
> >avail.
> >
> >Using the classical Northwind db as target db, and its employees table
> >as target table, if [note] column contains 'BA', then I want to set
> >weight to 1, else if it contains 'BTS', then I want to set weight to 2
> >etc. Weight or the like is an auxillary artificially created column.
> >
> >Something like
> >
> >DECLARE @col int
> >
> >select firstname, lastname, @col =
> > (
> > CASE notes
> > when '%BA%' then 1
> > when '%BTS%' then 2
> > else 0
> > END as aiCOL
> > )
> > -- and I'd like to alias @col as aiCOL or whatever
> >from employees
> >where notes LIKE
> > '%BA%'
> > OR
> > notes LIKE '%BTS%'
> >
> >failed.
> >
> >Thanks.
> >
> >