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.
If you are trying to replace zeros then
select coalesce(name,alt_name)....
it will return the first non-zero value
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.
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
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.
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.
CHOOSE CASE userid = somenum THEN name ELSE alt_name END FROM users