We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

CASE WHEN PHONE = NULL then ID ELSE PHONE END

5thcav
5thcav asked
on
Medium Priority
1,082 Views
Last Modified: 2009-07-29
CASE WHEN PHONE = NULL then ID ELSE PHONE END)

what is the correct way to replce a null field with another field such as the ID column...


Thanks


Ray
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Hi 5thcav,

case when phone is null then [ID] else  PHONE

or

@phoneNo = ISNULL(Phone, ID)

or

@phoneNo = COALESCE(Phone,ID)


Cheers!

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
ok, the second and the third won't work in this situation for you
Top Expert 2005

Commented:
   CASE PHONE WHEN NULL THEN
          ID
     ELSE
          PHONE
     END
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Sorry for my above post(its a wrong post ), you can use any of these based on the requirement.
> ok, the second and the third won't work in this situation for you

Author

Commented:
Line 2: Incorrect syntax near ')'.

SELECT ( case when phone is null then [ID] else PHONE), sitename, SUM([1]), SUM([2]),SUM([3]),

Line 2: Incorrect syntax near ','.
SELECT case when phone is null then [ID] else PHONE,


:(


Author

Commented:

Column '##temptable1.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

INSERT INTO ##temptable2
SELECT (case phone when null then ID else PHONE end), sitename, SUM([1]), SUM([2]),SUM([3]),SUM([4]),SUM([5]),SUM([6]),SUM([7]),SUM([8]),SUM([9]),SUM([10]),SUM([11]),SUM([12])
FROM       ##temptable1
GROUP BY PHONE, sitename

Commented:
Hi 5thcav,
> v            03/21/2006 08:07PM
>  Points: 500
>  Title: CASE WHEN PHONE = NULL then ID ELSE PHONE END
> ===================================================================
> CASE WHEN PHONE = NULL then ID ELSE PHONE END)

> what is the correct way to replce a null field with another field such
> as the ID column...


> Thanks


> Ray

> ===================================================================
>  Comment from aneeshattingal            03/21/2006 08:09PM
> ===================================================================
> Hi 5thcav,

> case when phone is null then [ID] else  PHONE

> or

> @phoneNo = ISNULL(Phone, ID)

> or

> @phoneNo = COALESCE(Phone,ID)


> Cheers!


> ===================================================================



try

SELECT ( case when phone is null then [ID] else PHONE) AS MYFIELD, sitename, SUM([1]), SUM([2]),SUM([3]), ***NOTE YOU"LL NEED ONE MORE FIELD HERE OR KILL THE LAST COMMA!****

not looking for points here, just trying to help

- Jack

Commented:
5thcav,
 our last post crossed.

 you cannot do a groupby like that.

  try

SELECT (case phone when null then ID else PHONE end) AS MYFIELD, sitename, SUM([1]), SUM([2]),SUM([3]),SUM([4]),SUM([5]),SUM([6]),SUM([7]),SUM([8]),SUM([9]),SUM([10]),SUM([11]),SUM([12])
FROM       ##temptable1
GROUP BY MYFIELD, sitename

Author

Commented:
not working,,, even as phone or when i name the table in ##temptable2 to myfield
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'MYFIELD'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MYFIELD'.
Server: Msg 207, Level 16, State 1, Line 1

when i use AS PHONE it goes back to Column '##temptable1.ID' is invalid in the select list

phone is nvarchar(11) and ID is int 4,, could this be it?


AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
5thcav,
> Line 2: Incorrect syntax near ')'.

> SELECT ( case when phone is null then [ID] else PHONE), sitename,
> SUM([1]), SUM([2]),SUM([3]),

> Line 2: Incorrect syntax near ','.
> SELECT case when phone is null then [ID] else PHONE,


I forgot to put 'END' for the case statement

SELECT ( case when phone is null then [ID] else PHONE END), sitename,

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
just shooting from the hip (again not looking to take points from aneesh, just trying to help)

What about Select (case when phone is null then [ID] as MYFIELD Else PHONE AS MYFIELD end), ....
Top Expert 2005
Commented:
INSERT INTO ##temptable2
SELECT (case phone when null then ID else PHONE end), sitename, SUM([1]), SUM([2]),SUM([3]),SUM([4]),SUM([5]),SUM([6]),SUM([7]),SUM([8]),SUM([9]),SUM([10]),SUM([11]),SUM([12])
FROM       ##temptable1
GROUP BY (case phone when null then ID else PHONE end), sitename

Commented:
Good thought Dmitryz
Top Expert 2005

Commented:
fruhj.

Thanks,hope it will work.

Author

Commented:

dmitryz6 wins the grand prize!!!

INSERT INTO ##temptable2
SELECT (case phone when null then CAST(ID as nvarchar) else PHONE end), sitename, SUM([1]), SUM([2]),SUM([3]),SUM([4]),SUM([5]),SUM([6]),SUM([7]),SUM([8]),SUM([9]),SUM([10]),SUM([11]),SUM([12])
FROM       ##temptable1
GROUP BY (case phone when null then CAST(ID as nvarchar) else PHONE end), sitename

Author

Commented:
thanks for the help!

ya gota love this EE :)
Top Expert 2005

Commented:
You welcome and good luck

Author

Commented:
:0(

it ran but the phone is stull NULL,,, what a bummer
Top Expert 2005

Commented:
may be phone zero length string as well

case when phone is null  then ID
                   when datalength(trim(phone)) = 0 then ID
                   else PHONE end

Author

Commented:
SELECT  ISNULL(LEFT(PHONE, 11),CAST(ID as nvarchar)) AS Phone


fixed it.... :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.