Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CASE question


CASE question

Author
Message
velezdamian
velezdamian
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 10
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
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
velezdamian
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 10
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
Xedni
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 677
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.
autoexcrement
autoexcrement
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
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
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6714 Visits: 17699
Since the case statement is doing incremental steps, it can be simplified using integer division, see the example.
Cool

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
Daniel Matthee
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 206
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search