Incorrect syntax near the keyword 'when' 3

[sqldevrequiretektips]

select cust_life_num,
case merch_mon_ind when 1 then 'M' else merch_full_mon when 1 then 'M' else merch_part_mon when 1 then 'M'
else null end,'MON' from #CustID9

I have errror "Incorrect syntax near the keyword 'when'"
Is any one can please help me out what wrong with this select statement

AD

 

[SQLSister]

You have not used the case statement correctly. Try:

select cust_life_num,
case  when merch_mon_ind = 1 then 'M' 
when merch_full_mon = 1 then 'M' 
when merch_part_mon = 1 then 'M'
else null end,'MON' from #CustID9

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

I'm guessing that this is what you are shooting for:

[Blue]SELECT[/Blue] cust_life_num[Gray],[/Gray]
       [Blue]CASE[/Blue] [Blue]WHEN[/Blue] merch_mon_ind [Gray]=[/Gray] 1 [Gray]OR[/Gray] 
                 merch_full_mon [Gray]=[/Gray] 1 [Gray]OR[/Gray] 
                 merch_part_mon [Gray]=[/Gray] 1 [Blue]THEN[/Blue] [red]'M'[/red] 
            [Blue]ELSE[/Blue] [Gray]NULL[/Gray] [Blue]END[/Blue] [red]'MON'[/red] 
   [Blue]FROM[/Blue] #CustID9

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

Many ways to skin a Cat !

[blue]DBomrrsm[/blue]

 

[sqldevrequiretektips]

Thanks For your reply...
If any one could help me out on this case...


a) master table terrredesign_WIActCat_WIDay has cca. 8,000,000 rows.
b) final join result to insert into tblWIActCat_WIDay has some 2,000 rows.
c) EXISTS() and JOIN are significantly faster than IN()
---
You right on above things...let me tell your something in detail...

b)-> In final join result, Mostly I get 2000-6000 Rows Maximum I would say 10000 Records..As you know It depends
SQLDB Expression...

c) EXISTS() and JOIN are significantly faster than IN()
In my case Inner Join run faster then Exists() and Exists() run faster then IN()

dbo_Custtb1 - 50000 (Total rows)
SQLDB Expression for an Example:

(Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 1) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 2) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 6)

Above Region_Num, Team_Num,Territory_Num all these fields are in dbo_Custttb1,

SQLDB Expression depends what user select from VB Form then when user click on process button then i am passing SQLDB value to my query to Insert Data in WIActCat_WIDay Table(Local Access Table).

***
select count(distinct CustLifeNo) from terrredesign_WIActCat_WIDay

Above Query return Only 38000 Records..For your Information
terrredesign_WIActCat_WIDay contain somany redundant data and for some reason we really require redundant data..


Now I really wants to create a store procedure which return recordset (get my output really quick..)if anyone can help me out i will really appriciate...


AD

 

[dbomrrsm]

why is it redundant - because it has been aged out ? - ie a newer record has been inserted - if so create a table that has the latestrecord and use that if that is what people are querying on - archive the rest to another table to shut up management about keeping history.

[blue]DBomrrsm[/blue]

 

[sqldevrequiretektips]

Thanks for reply...
No we can't...we must have to use existing table...

It's redudant cause it contain the history records...and user wants to use this history whenever they want...

Also we can't normalized this table either...
If you have more question let me know

Thaks
Ad

 

[dbomrrsm]

Then your stuck with a deficcient table and deficient management - there are ways of still seeing the old data !

[blue]DBomrrsm[/blue]

 

[donutman]

Why do you load this data into an Access table? Did I understand that correctly?
In order to help you with an efficient query, we need to know the table design and the output required.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]