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!
MartinSELECT 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
--|||Thanks David!
Have a nice day :-)
