question

SajoNez1510-5981 avatar image
0 Votes"
SajoNez1510-5981 asked SajoNez1510-5981 commented

How to avoid converting data in the nvarchar phone number column to int

Hello everyone. I created an inline spreadsheet function that, based on the parameter entered, which is essentially a number of some found in BusinessEntityID, displays personal information about the employee. One of these data is the phone number. However when I call this function it shows me that it cannot convert the pp.PhoneNumber column from nvarchar to int. Can this conversion be bypassed because I need a nvarchar data type in the pp.PhoneNumber column


Here is my query
Create FUNCTION dbo.Workersprivatedata (@ID [int])
RETURNS TABLE
RETURN
(WITH CTE([WORKERID],[Name],[MiddleName],[LastName],[Email],[City],[PhoneNumber])
AS
(SELECT p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName, ea.EmailAddress,a.City, pp.PhoneNumber FROM Person.Person p join Person.EmailAdDress ea on
p.BusinessEntityID=ea.BusinessEntityID join Person.BusinessEntity b on p.BusinessEntityID=b.BusinessEntityID join
Person.BusinessEntityAddress bea on b.BusinessEntityID=bea.BusinessEntityID
join Person.[Address] a on bea.AddressID=a.AddressID LEFT OUTER JOIN Person.PersonPhone pp on p.BusinessEntityID=pp.PhoneNumber
)
SELECT FROM CTE
WHERE @ID=WORKERID
)
Select
from Workersprivatedata(5)



This is what it looks like
86569-pomoc.png


sql-server-transact-sql
pomoc.png (169.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @SajoNez1510-5981

Welcome to the microsoft TSQL Q&A forum!

Please refer to:

 Create FUNCTION dbo.Workersprivatedata (@ID [int])
 RETURNS TABLE
 RETURN
 (WITH CTE([WORKERID],[Name],[MiddleName],[LastName],[Email],[City],[PhoneNumber])
 AS
 (SELECT p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName, ea.EmailAddress,a.City, pp.PhoneNumber FROM Person.Person p join Person.EmailAdDress ea on
 p.BusinessEntityID=ea.BusinessEntityID 
 join Person.BusinessEntity b on p.BusinessEntityID=b.BusinessEntityID 
 join Person.BusinessEntityAddress bea on b.BusinessEntityID=bea.BusinessEntityID
 join Person.[Address] a on bea.AddressID=a.AddressID 
 LEFT OUTER JOIN Person.PersonPhone pp on cast(p.BusinessEntityID as nvarchar)=pp.PhoneNumber
 )
 SELECT FROM CTE
 WHERE @ID=WORKERID
 )
 Select * from Workersprivatedata(5)

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered SajoNez1510-5981 commented

So what did you have in mind here:

LEFT OUTER JOIN Person.PersonPhone pp on p.BusinessEntityID=pp.PhoneNumber

Are you really looking for people whose BusinessEntityID matches some other persons phone number?

Or could it be that you have grabbed hold of the wrong column?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for answer. I corrected my query. I made mistake when I joined Person table to PersonPhone table. I have to join them on BusinessEntityID not on phoneNumber because PhoneNumber column and BusinessEntityID columnt are not same type and that was problem. Now I have LEFT OUTER JOIN Person.PersonPhone pp on p.BusinessEntityID=pp.BusinessEntityID and it works fine. Thanks for answer guys

0 Votes 0 ·