troubleshooting Question

SQL - Data Type Issue

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
5 Comments1 Solution15 ViewsLast Modified:
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)


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

Any assistance is appreciated.

Thank you 

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros