Looking to convert to CASE Statement or If statement

Question

Looking to convert to CASE Statement or If statement

RDOT

SSC Veteran

Points: 204
More actions
October 13, 2022 at 1:28 am

#4098921

(If {@route}='' then
if ({@USRoute}='') then ''
else ('('+{@USRoute} + '), ')
else
if ({@USRoute}='') then ({@route}+', ')
else ({@route}+' ('+{@USRoute}+'), '))
+
Left ({PM_PROJECT.TERMINI_TXT},230)

Viewing 4 posts - 1 through 3 (of 3 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Something like this, I think:

DECLARE
    @Route   VARCHAR(50)
,   @USRoute VARCHAR(50);

SELECT
    @Route = 'x'
,   @USRoute = '';

SELECT SomeCol = CONCAT(   CASE
                               WHEN @Route = '' THEN
                                   IIF(@USRoute = '', '', CONCAT('(', @USRoute, ')'))
                               WHEN @USRoute = '' THEN
                                   CONCAT(@Route, ', ')
                               ELSE
                                   CONCAT(@Route, ' (', @USRoute, '), ')
                           END
                       ,   LEFT(PM_PROJECT.TERMINI_TXT, 230)
                       );

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

I'd go with:

SELECT CONCAT(@Route, '(' + NULLIF(@USRoute, '') + ')', 
    CASE WHEN LEN(@Route) + LEN(@USRoute) = 0 THEN '' ELSE ', ' END, 
    LEFT(PM_PROJECT.TERMINI_TXT,230))

Or this, if you don't have CONCAT available:

SELECT ISNULL(NULLIF(ISNULL(@Route, '') + ISNULL('(' + NULLIF(@USRoute, '') + ')', '') 
    + ', ', ', '), '') + LEFT('abcd',230)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

SQL is a declarative language, but you write it as if it were procedural. CASE is an expression, not a statement. IF-THEN controls flow of execution. Since you did not post DDL, we have to guess ar everything. but it looks like you are assembling strings as if this were COBOL or Basic. SQL uses atomic scalar values and not CSV structures. Whar are you acrually trying to do?

Please post DDL and follow ANSI/ISO standards when asking for help.