Clever Geek Handbook

SQL inline conditional in Select

I was wondering if something like this is possible:

SELECT name or alt_name FROM users Where userid=somenum

I also tried this:

SELECT name IF NOT NULL ELSE alt_name ...

If anyone has any understanding I would appreciate it.

+2


source to share


6 answers


If you are trying to replace zeros then

select coalesce(name,alt_name)....


it will return the first non-zero value

+7


source


How about this?

SELECT IsNull(name, alt_name) FROM users Where userid=somenum


Similar to Coalesce, but only takes two arguments and is easier to write.

+2


source


Do you mean something like this? I accept TSQL

SELECT CASE WHEN [name] IS NOT NULL THEN [name] ELSE [alt_name] END AS [UserName]
FROM [Users] WHERE [UserId] = somenum
+1


source


Not the most efficient answer, but how about this:

SELECT name FROM users WHERE userid=somenum AND name IS NOT NULL
UNION
SELECT altname FROM users WHERE userid=somenum AND name IS NULL

I think this will lead to what you are after.

0


source


If you are using a database that supports them, then one of the easiest ways is to use a user defined function, then you will have

SELECT udfPreferredName() FROM users

where udfPreferredName()

will encapsulate the logic needed to choose between the name and alternative_name fields.

One of the benefits of using a function is that you can abstract away the selection logic and apply it across multiple SQL statements wherever you need it. Doing inline logic using a case is fine, but it will usually be (much) more difficult to maintain the system. In most DBMSs the additional overhead of a function call will not be significant unless you are handling very large tables.

0


source


CHOOSE CASE userid = somenum THEN name ELSE alt_name END FROM users

0


source






More articles:


All Articles