CASE question

Question

CASE question

velezdamian

SSC Journeyman

Points: 94
More actions
July 28, 2014 at 1:49 pm

#289072

Hello all,
If I'm using a temp table and I have 26 lines of case statements. Is each case statement considered a separate item within the select? For example, I identify 9 different columns within my Insert Into statement, but within my select I not only have the 8 different columns, but I also have 26 lines which are all case statements of one column. I've pasted some of the code for you to take a look at. Any and all help is greatly appreciated. Thanks.
INSERT INTO #TMP1A
(ACCTCORP,
HOUSE,
CUST,
BADATE,
AMOUNT,
ADJRSN,
BATCH,
CTLGRP,
POSTFLAG)
SELECT
ACCTCORP,
HOUSE,
CUST,
BADATE,
AMOUNT,
ADJRSN,
CASE WHEN BATCH BETWEEN 10000 AND 10999 THEN 'A' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 11000 AND 11999 THEN 'B' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 12000 AND 12999 THEN 'C' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 13000 AND 13999 THEN 'D' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 14000 AND 14999 THEN 'E' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 15000 AND 15999 THEN 'F' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 16000 AND 16999 THEN 'G' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 17000 AND 17999 THEN 'H' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 18000 AND 18999 THEN 'I' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 19000 AND 19999 THEN 'J' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 20000 AND 20999 THEN 'K' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 21000 AND 21999 THEN 'L' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 22000 AND 22999 THEN 'M' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 23000 AND 23999 THEN 'N' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 24000 AND 24999 THEN 'O' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 25000 AND 25999 THEN 'P' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 26000 AND 26999 THEN 'Q' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 27000 AND 27999 THEN 'R' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 28000 AND 28999 THEN 'S' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 29000 AND 29999 THEN 'T' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 30000 AND 30999 THEN 'U' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 31000 AND 31999 THEN 'V' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 32000 AND 32999 THEN 'W' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 33000 AND 33999 THEN 'X' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 34000 AND 34999 THEN 'Y' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 35000 AND 35999 THEN 'Z' + RIGHT(BATCH,3)
WHEN CONVERT(VARCHAR,BATCH) <= 9999 THEN CONVERT(VARCHAR,BATCH)
ELSE '' END AS 'DDP_BATCH_#',BATCH AS [ORIG_BATCH_#],
--BATCH,
CTLGRP,
CASE WHEN POSTFLAG = ' ' THEN 'No'
ELSE 'Yes' END AS POSTFLAG
D.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i see two case statements. one with 20+ evaluations for the CTLGRP alias , and the other one for your postflag alias.

so the individual evaluations are not considered columns, if i read your question right...they are just used to determine the value for CTLGRP

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

velezdamian SSC Journeyman Points: 94 More actions · Answer 2

Hi Lowell, thanks for replying. I haven't had to do temp tables for a very long time so I am extremely rusty. I'm trying to find out if I have to include all 26 lines within my SELECT statement into my INSERT INTO statement. Also, yest the POSTFLAG column also has a CASE statement. I'm unsure as to how I can get this temp table to work other than adding all 26 lines into my INSERT INTO statement. Am I going about this the wrong way?

D.

Xedni SSCertifiable Points: 6951 More actions · Answer 3

Yes, you need all the lines of the case statement. There's no way for it to determine the cases if you omit them.

Executive Junior Cowboy Developer, Esq.[/url]

autoexcrement SSCertifiable Points: 5956 More actions · Answer 4

If you format your code nicely, the answers to questions like these will be a lot more obvious to you and anyone else who has to work on your code in the future. It is worth the time to format your code, even if only a little.

SELECT

ACCTCORP,

HOUSE,

CUST,

BADATE,

AMOUNT,

ADJRSN,

[DDP_BATCH_#] =

CASE

WHEN BATCH BETWEEN 10000 AND 10999 THEN 'A' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 11000 AND 11999 THEN 'B' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 12000 AND 12999 THEN 'C' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 13000 AND 13999 THEN 'D' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 14000 AND 14999 THEN 'E' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 15000 AND 15999 THEN 'F' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 16000 AND 16999 THEN 'G' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 17000 AND 17999 THEN 'H' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 18000 AND 18999 THEN 'I' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 19000 AND 19999 THEN 'J' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 20000 AND 20999 THEN 'K' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 21000 AND 21999 THEN 'L' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 22000 AND 22999 THEN 'M' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 23000 AND 23999 THEN 'N' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 24000 AND 24999 THEN 'O' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 25000 AND 25999 THEN 'P' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 26000 AND 26999 THEN 'Q' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 27000 AND 27999 THEN 'R' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 28000 AND 28999 THEN 'S' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 29000 AND 29999 THEN 'T' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 30000 AND 30999 THEN 'U' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 31000 AND 31999 THEN 'V' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 32000 AND 32999 THEN 'W' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 33000 AND 33999 THEN 'X' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 34000 AND 34999 THEN 'Y' + RIGHT(BATCH,3)

WHEN BATCH BETWEEN 35000 AND 35999 THEN 'Z' + RIGHT(BATCH,3)

WHEN CONVERT(VARCHAR,BATCH) <= 9999 THEN CONVERT(VARCHAR,BATCH)

ELSE ''

END,

[ORIG_BATCH_#] = BATCH,

CTLGRP,

POSTFLAG =

CASE

WHEN POSTFLAG = ' ' THEN 'No'

ELSE 'Yes'

END

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 5

Since the case statement is doing incremental steps, it can be simplified using integer division, see the example.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SET_SIZE INT = 40000;

/* Test data 1 to @SET_SIZE */

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(@SET_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7)

SELECT

NM.N AS BATCH

-- UNCOMMEMNT THE FOLLOWING LINES TO ASSERT THE LOGIC

--,FLOOR(NM.N / 1000) AS GroupNo

--,CHAR(FLOOR(NM.N / 1000) + 55) AS GroupChar

,CASE

WHEN NM.N BETWEEN 10000 AND 35999 THEN CHAR(FLOOR(NM.N / 1000) + 55) + RIGHT(CAST(NM.N AS VARCHAR(12)),3)

WHEN NM.N < 10000 THEN CAST(NM.N AS VARCHAR(4))

ELSE ''

END AS [DDP_BATCH_#]

FROM NUMS NM

WHERE NM.N > 9990;

Daniel Matthee Mr or Mrs. 500 Points: 579 More actions · Answer 6

Hi

What about something like this.

declare @inc int = 0

create table #BATCH (

Number int,

BATCH varchar(2)

)

while (@inc < 36000)

begin

insert into #BATCH

select @inc,

case

WHEN @inc BETWEEN 10000 AND 10999 THEN 'A'

WHEN @inc BETWEEN 11000 AND 11999 THEN 'B'

WHEN @inc BETWEEN 12000 AND 12999 THEN 'C'

WHEN @inc BETWEEN 13000 AND 13999 THEN 'D'

WHEN @inc BETWEEN 14000 AND 14999 THEN 'E'

WHEN @inc BETWEEN 15000 AND 15999 THEN 'F'

WHEN @inc BETWEEN 16000 AND 16999 THEN 'G'

WHEN @inc BETWEEN 17000 AND 17999 THEN 'H'

WHEN @inc BETWEEN 18000 AND 18999 THEN 'I'

WHEN @inc BETWEEN 19000 AND 19999 THEN 'J'

WHEN @inc BETWEEN 20000 AND 20999 THEN 'K'

WHEN @inc BETWEEN 21000 AND 21999 THEN 'L'

WHEN @inc BETWEEN 22000 AND 22999 THEN 'M'

WHEN @inc BETWEEN 23000 AND 23999 THEN 'N'

WHEN @inc BETWEEN 24000 AND 24999 THEN 'O'

WHEN @inc BETWEEN 25000 AND 25999 THEN 'P'

WHEN @inc BETWEEN 26000 AND 26999 THEN 'Q'

WHEN @inc BETWEEN 27000 AND 27999 THEN 'R'

WHEN @inc BETWEEN 28000 AND 28999 THEN 'S'

WHEN @inc BETWEEN 29000 AND 29999 THEN 'T'

WHEN @inc BETWEEN 30000 AND 30999 THEN 'U'

WHEN @inc BETWEEN 31000 AND 31999 THEN 'V'

WHEN @inc BETWEEN 32000 AND 32999 THEN 'W'

WHEN @inc BETWEEN 33000 AND 33999 THEN 'X'

WHEN @inc BETWEEN 34000 AND 34999 THEN 'Y'

WHEN @inc BETWEEN 35000 AND 35999 THEN 'Z'

WHEN @inc <= 9999 THEN '' end

set @inc = @inc +1;

end

Create index idx_Number on #BATCH (Number)include (BATCH)

INSERT INTO #TMP1A

(ACCTCORP,

HOUSE,

CUST,

BADATE,

AMOUNT,

ADJRSN,

BATCH,

CTLGRP,

POSTFLAG)

SELECT

ACCTCORP,

HOUSE,

CUST,

BADATE,

AMOUNT,

ADJRSN,

CASE WHEN b.BATCH is not null then b.BATCH + RIGHT(a.BATCH,3)

WHEN b.BATCH)<= 9999 THEN a.BATCH

ELSE '' END AS 'DDP_BATCH_#',

BATCH AS [ORIG_BATCH_#],

--BATCH,

CTLGRP,

CASE WHEN POSTFLAG = ' ' THEN 'No'

ELSE 'Yes' END AS POSTFLAG

from <Table> a

left JOIN #BATCH b on a.BATCH =b.BATCH

Please feel free to ask for more detail in the explanation.

--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za