Showing posts with label ifelse. Show all posts
Showing posts with label ifelse. Show all posts

Friday, March 9, 2012

How to use IF..ELSE statement in Select?

Hi expert,
May i know how to use apply IF..ELSE statement into Select query'
For example:
Select A,B,(If (C like '') D ELSE E) as F, G
From XBN
I test out the example, i got error msg on it "Incorrect syntax near the
keyword "IF".
Please adivce on how to accomplish above example.
thank you in advance.
Ocurnos."Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:1C17E682-946F-42F9-87A2-BFD4A2586AA1@.microsoft.com...
> Hi expert,
> May i know how to use apply IF..ELSE statement into Select query'
> For example:
> Select A,B,(If (C like '') D ELSE E) as F, G
> From XBN
> I test out the example, i got error msg on it "Incorrect syntax near the
> keyword "IF".
> Please adivce on how to accomplish above example.
> thank you in advance.
> Ocurnos.
Look up CASE in books on-line.
Select A, B, CASE WHEN C = '' THEN D ELSE E END as F, G
From XBN|||Thanks Raymond.
"Raymond D'Anjou" wrote:

> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:1C17E682-946F-42F9-87A2-BFD4A2586AA1@.microsoft.com...
> Look up CASE in books on-line.
> Select A, B, CASE WHEN C = '' THEN D ELSE E END as F, G
> From XBN
>
>

how to use if...else

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?
>