i have 4 columns that hold phone numbers. at least one has to be filled in.
how could i select the first one with a value in it
i tried:
SELECT (IF PhoneWork != '' BEGIN (SELECT CAST(PhoneWork AS bigint)) END ELSE
(SELECT CAST('0' AS bigint))) AS PhoneWork FROM Customers
but that isnt working. its not checking the other 3 columns: PhoneHome,
PhoneFax, PhoneOther, because i got an error: Incorrect syntax near ')'
thanks for your help.have u tried CASE
SELECT
CASE
WHEN PhoneWork1 IS NOT NULL THEN PhoneWork1
WHEN PhoneWork2 IS NOT NULL THEN PhoneWork2
WHEN PhoneWork3 IS NOT NULL THEN PhoneWork3
WHEN PhoneWork4 IS NOT NULL THEN PhoneWork4
END
FROM Customers|||Abraham Luna,
See function COALESCE in BOL.
Example:
select coalesce(c1, c2, c3, c4) as c
from
(
select 1, cast(null as int), cast(null as int), cast(null as int)
union all
select cast(null as int), 2, cast(null as int), cast(null as int)
union all
select cast(null as int), cast(null as int), 3, cast(null as int)
union all
select cast(null as int), cast(null as int), cast(null as int), 4
) as t1(c1, c2, c3, c4)
go
AMB
"Abraham Luna" wrote:
> i have 4 columns that hold phone numbers. at least one has to be filled in
.
> how could i select the first one with a value in it
> i tried:
> SELECT (IF PhoneWork != '' BEGIN (SELECT CAST(PhoneWork AS bigint)) END EL
SE
> (SELECT CAST('0' AS bigint))) AS PhoneWork FROM Customers
> but that isnt working. its not checking the other 3 columns: PhoneHome,
> PhoneFax, PhoneOther, because i got an error: Incorrect syntax near ')'
> thanks for your help.
>
>|||>i have 4 columns that hold phone numbers. at least one has to be filled in.
>how could i select the first one with a value in it
CREATE TABLE dbo.foo
(
PhoneWork VARCHAR(32),
PhoneHome VARCHAR(32),
PhoneFax VARCHAR(32),
PhoneOther VARCHAR(32)
)
GO
SET NOCOUNT ON
INSERT dbo.foo SELECT '555',NULL,NULL,NULL
INSERT dbo.foo SELECT '555',NULL,'222',NULL
INSERT dbo.foo SELECT NULL,'333',NULL,NULL
INSERT dbo.foo SELECT NULL,NULL,NULL,'444'
INSERT dbo.foo SELECT NULL,NULL,'666',NULL
INSERT dbo.foo SELECT NULL,NULL,NULL,NULL
INSERT dbo.foo SELECT NULL,NULL,NULL,'x'
GO
SELECT PhoneWork = COALESCE
(
NULLIF(PhoneWork, ''),
NULLIF(PhoneHome, ''),
NULLIF(PhoneFax, ''),
NULLIF(PhoneOther,''),
'0'
)
FROM dbo.foo
GO
DROP TABLE dbo.foo
Why are you casting this as a BIGINT? How are you preventing non-numeric
values from being entered into any of these columns? What on earth is a
PhoneFax?|||thank you for your answer
"Manshu" <upadhyay.himanshu@.gmail.com> wrote in message
news:1126012613.765505.60260@.g49g2000cwa.googlegroups.com...
> have u tried CASE
> SELECT
> CASE
> WHEN PhoneWork1 IS NOT NULL THEN PhoneWork1
> WHEN PhoneWork2 IS NOT NULL THEN PhoneWork2
> WHEN PhoneWork3 IS NOT NULL THEN PhoneWork3
> WHEN PhoneWork4 IS NOT NULL THEN PhoneWork4
> END
> FROM Customers
>|||LOL,
thank you for your answer. i cast it as a bigint because the gridview
boundfield dataformatstring is set to {0:(###) ###-####} and it only works
on integer columns. i can't figure out how to format strings using the
dataformatstring property (if you know how let me know).
phonefax is there fax number :)
they tried to keep the naming convention of PhoneXXX where XXX is the type
of phone number.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23SR4WXusFHA.260@.TK2MSFTNGP11.phx.gbl...
>
> CREATE TABLE dbo.foo
> (
> PhoneWork VARCHAR(32),
> PhoneHome VARCHAR(32),
> PhoneFax VARCHAR(32),
> PhoneOther VARCHAR(32)
> )
> GO
> SET NOCOUNT ON
> INSERT dbo.foo SELECT '555',NULL,NULL,NULL
> INSERT dbo.foo SELECT '555',NULL,'222',NULL
> INSERT dbo.foo SELECT NULL,'333',NULL,NULL
> INSERT dbo.foo SELECT NULL,NULL,NULL,'444'
> INSERT dbo.foo SELECT NULL,NULL,'666',NULL
> INSERT dbo.foo SELECT NULL,NULL,NULL,NULL
> INSERT dbo.foo SELECT NULL,NULL,NULL,'x'
> GO
> SELECT PhoneWork = COALESCE
> (
> NULLIF(PhoneWork, ''),
> NULLIF(PhoneHome, ''),
> NULLIF(PhoneFax, ''),
> NULLIF(PhoneOther,''),
> '0'
> )
> FROM dbo.foo
> GO
> DROP TABLE dbo.foo
>
> Why are you casting this as a BIGINT? How are you preventing non-numeric
> values from being entered into any of these columns? What on earth is a
> PhoneFax?
>
