Conditional SELECT

[Martin]

Dear Group

I'm having trouble with the statement below. I tried CASE and IF
without success. What I'm trying to do:
There is a field in the database called Business_TelNo. If the field
has some value, I would like to return a generated field
(LaBusinessTelNo), which is the label of Busines_TelNo, reading
'Phone:'
If Business_TelNo has no value, the label should be set to ''.

Something like this:
SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,
IF (LEN(Business_TelNo) > 0) BEGIN SELECT 'Phone: ' AS LaBusinessTelNo
END ELSE BEGIN SELECT '' AS LaBusinessTelNo END
FROM i2b_vw_contact

This is working:
SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,
'Phone: ' AS LaBusinessTelNo
FROM i2b_vw_contact

PS: I know it would be much easier to add some logic in the
application but need to do this in SQL.

Thanks very much for your time and efforts!

Martin

[David Portas]

SELECT business_telno,
CASE WHEN business_telno>'' THEN 'Phone: ' ELSE '' END AS labusiness_telno
FROM i2b_vw_contact

You can find the CASE and IF syntax in Books Online but understand that CASE
is an *expression* whereas IF is a *statement* and therefore IF can't be
used as part of a query.

--
David Portas
SQL Server MVP
--

[Martin]

Thanks David!
Have a nice day :-)