Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag for Australia

asked on

SQL - Data Type Issue

Hi EE,

I have an issue with the following query:

SELECT CAST(W.PERSONNELNUMBER AS BIGINT) AS EMPLOYEEID, pd.description as TITLE,


(select top 1 c.COMPENSATIONLEVELID from HCMJOBDETAIL as j join HCMCOMPENSATIONLEVEL as c


on c.RECID = j.COMPENSATIONLEVEL where j.JOB =  pd.Job) as GRADE,


DEPARTMENT.NAME AS DEPARTMENT,DIVISION.NAME AS DEVISION


FROM HCMWORKER AS W


JOIN


(


select wa.POSITION,convert(nvarchar(12),wa.VALIDFROM,110) as VALIDFROM,convert(nvarchar(12),wa.VALIDTO,110) as VALIDTO


,wa.WORKER from HCMPOSITIONWORKERASSIGNMENT as wa


where DATEDIFF(m,wa.ValidFrom,wa.ValidTo)>1


group by wa.POSITION,convert(nvarchar(12),wa.VALIDFROM,110) ,convert(nvarchar(12),wa.VALIDTO,110),wa.WORKER


)


AS WA ON WA.WORKER = W.RECID and WA.VALIDFROM < GETDATE() and WA.VALIDTO > GETDATE()


join


(SELECT POSITION,convert(nvarchar(12),VALIDFROM,110) as VALIDFROM,


convert(nvarchar(12),VALIDTO,110) as VALIDTO,DEPARTMENT,


JOB,[DESCRIPTION],TITLE FROM (SELECT * FROM HCMPOSITIONDETAIL as pd


where DATEDIFF(m,pd.ValidFrom,pd.ValidTo)>1


and pd.DEPARTMENT <>0 and pd.POSITION <>0 and pd.JOB <> 0) as pd


group by POSITION,convert(nvarchar(12),VALIDFROM,110),convert(nvarchar(12),VALIDTO,110),DEPARTMENT,


JOB,[DESCRIPTION],TITLE


)


as pd on pd.POSITION = WA.POSITION and pd.VALIDFROM<GETDATE() and pd.VALIDTO > GETDATE()


LEFT OUTER JOIN dbo.DIRPARTYTABLE AS department ON department.RECID = pd.DEPARTMENT AND department.INSTANCERELATIONTYPE IN (2377)


LEFT OUTER JOIN dbo.DIRPARTYTABLE AS division ON division.NAMEALIAS = department.HCMWORKER AND division.INSTANCERELATIONTYPE IN (2377)

Open in new window


Error: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.

Any assistance is appreciated.

Thank you 

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zack

ASKER

Many thanks, PortletPaul I'll tap away on this query with your suggestions. 
Be aware that the actual error is most likely to be this:

  CAST(W.PERSONNELNUMBER AS bigint) AS EMPLOYEEID

Error: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.

You might have to remove that cast instruction because one or more values in that column cannot be converted to bigint

Avatar of Zack

ASKER

Noted I'll post the query here when I have fixed it. 
TRY_CAST()

You can use TRY_CAST() instead of just CAST().
With try_cast you don't get fatal errors if the type conversion isn't possible, instead it returns NULL

e.g.
select try_cast('1234' as bigint) << this works and returns 1234
select try_cast('zack' as bigint) << this won't error out but what you get back is NULL