Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CASE question Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 1:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:20 AM
Points: 3, Visits: 8
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.
Post #1597049
Posted Monday, July 28, 2014 2:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 12,927, Visits: 32,333
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1597052
Posted Monday, July 28, 2014 2:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:20 AM
Points: 3, Visits: 8
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.
Post #1597057
Posted Thursday, July 31, 2014 4:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 134, Visits: 416
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.
Post #1598531
Posted Thursday, July 31, 2014 4:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:19 PM
Points: 157, Visits: 608
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
Post #1598534
Posted Thursday, July 31, 2014 10:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 2,420, Visits: 6,735
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;

Post #1598569
Posted Friday, August 1, 2014 5:04 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 15, 2014 1:58 AM
Points: 47, Visits: 79
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.
Post #1598834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse