SQL jelp needed before I go insane!!

[pincher]

I’m trying to solve an issue SQL but I just can't work it out, so thought I’d throw it open to the PH uber-brains

The bit I’m having trouble with is the following;

select left(short_name, datalength(short_name)-3), short_name, user_field_14 from account
where short_name in
('asa201',
'869gcffix')

The result set is as follows;

(No column name), short_name, user_field_14
869GCF, 869GCFFIX, 0.0
ASA, ASA201, 1.0

What I want to do is trim the last 3 characters of short_name only if user_field_14 is 0

I’m sure there should be some sort of IF…ELSE query involved by I can’t do it

(Apols for hte poor formatting of the results but it won't let me tab it)

Anyone got any bright ideas?

(It's part of a larger query so have used a representative sample of short_names)

[Don]

you need something like

select CASE user_field_14
WHEN 0 THEN left(short_name, datalength(short_name)-3)
WHEN 1 THEN short_name
END as short_name_with_modifications,
user_field_14

You could also use the construct

select CASE user_field_14
WHEN 0 THEN left(short_name, datalength(short_name)-3)
ELSE short_name
END as short_name_with_modifications


Depends on what SQL dialect you are using of course...but CASE WHEN...THEN...ELSE...END is the SQL Server one.

[pdV6]

select case user_field_14 when 0 then left(short_name, datalength(short_name)-3) else short_name end, user_field_14 from account
where short_name in
('asa201',
'869gcffix')

{Edited to add the "then" that I, er, 'deliberately' forgot)

Edited by pdV6 on Monday 13th November 15:20

[pincher]

to both of you!

Thank you so much

[ATG]

depending on size of datasets, complexity of the query, how your SQL engine chooses to cache data and execute the query etc, you could try something like the following. Personally I tend to find this easier to read if the basic SELECT query is simple. As SQL is declaritive, in theory the way you phrase a query shouldn't make any difference to its execution speed. So long as the underlying logic is identical, the particular way you choose to phrase the SQL should be irrelevant to the way the SQL engine chooses to calculate its execution plan. Whether or not this works in practice I will leave as an exercise in wishful thinking for the reader.

SELECT LEFT(blah) WHERE your_field = 1
UNION
SELECT blah WHERE your_field != 1