?
Solved

CASE WHEN PHONE = NULL then ID ELSE PHONE END

Posted on 2006-03-21
20
Medium Priority
?
1,058 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
0
Comment
Question by:5thcav
  • 7
  • 5
  • 4
  • +1
20 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16253626
Hi 5thcav,

case when phone is null then [ID] else  PHONE

or

@phoneNo = ISNULL(Phone, ID)

or

@phoneNo = COALESCE(Phone,ID)


Cheers!

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16253639
ok, the second and the third won't work in this situation for you
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16253646
   CASE PHONE WHEN NULL THEN
          ID
     ELSE
          PHONE
     END
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16253659
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16253662
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,


:(


0
 
LVL 7

Author Comment

by:5thcav
ID: 16253687

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
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16253702
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
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16253712
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16253810
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?


0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 16253824
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,
0
 
LVL 12

Assisted Solution

by:fruhj
fruhj earned 200 total points
ID: 16253870
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), ....
0
 
LVL 19

Accepted Solution

by:
dmitryz6 earned 1600 total points
ID: 16253926
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
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16253949
Good thought Dmitryz
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16253963
fruhj.

Thanks,hope it will work.
0
 
LVL 7

Author Comment

by:5thcav
ID: 16254048

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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16254063
thanks for the help!

ya gota love this EE :)
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16254112
You welcome and good luck
0
 
LVL 7

Author Comment

by:5thcav
ID: 16254330
:0(

it ran but the phone is stull NULL,,, what a bummer
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16254418
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
0
 
LVL 7

Author Comment

by:5thcav
ID: 16254419
SELECT  ISNULL(LEFT(PHONE, 11),CAST(ID as nvarchar)) AS Phone


fixed it.... :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question