Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
End
Could you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:

> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>
|||Hi
Some ideas
http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>
|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>Some ideas
>http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||Hi Linchi, thank you for responding back and the guidance. I'll try rewriting
it again.
Linchi Shea wrote:[vbcol=seagreen]
>Could you shed some light on what you are trying to accomplish with the CASE
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>[quoted text clipped - 20 lines]
Message posted via http://www.droptable.com