June 14, 2018 at 1:43 am
Hello,
I'm new to SQL. I have encountered a problem and I don't even know where to begin. I use views that are derived from one view. This view is showing me an error (SQL text cannot be represented in the grid pane and diagram pane) and I don't even know where to start looking for problem. The view is working but it creates unexpected error when I use it further (Conversion failed when converting the nvarchar value 'XXX' to data type int.)
Can anyone maybe at least give me some pointers how to start dealing with this problem? I'd like to make this view more readable because I need it on a daily basis.
This is the view with the first error:
SELECT ROW_NUMBER() OVER (ORDER BY p.FirstName, p.LastName, p.Code DESC) AS Id, p.FirstName, p.LastName, p.Code, p.TaxNumber, dbo.GetPersonAddressText(addres.ID_Person,
addres.ID_CT_PersonAddressType, addres.Street, addres.HouseNumber, addres.ID_CT_Post, addres.ValidFrom, addres.ValidTo, addres.Comment, NULL) AS PersonAddress, dbo.GetCT_PostTextById(addres.ID_CT_Post, 'sl')
AS PersonAddressPost, STUFF
((SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax ppf INNER JOIN
H_CT_PersonPhoneFaxType ctppft ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person AND ctppft.Type = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.Business') AND ppf.Active = 1 FOR XML PATH('')), 1, 2, '') AS Phone, STUFF
((SELECT ', ' + Email
FROM H_PersonEmail pe
WHERE pe.ID_Person = p.ID_Person AND pe.Active = 1
ORDER BY pe.IsPrimary DESC FOR XML PATH('')), 1, 2, '') AS Email, STUFF
((SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax ppf INNER JOIN
H_CT_PersonPhoneFaxType ctppft ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person AND ctppft.Type = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.MobileBusiness') AND ppf.Active = 1 FOR XML PATH('')), 1, 2, '') AS Gsm, STUFF
((SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax ppf INNER JOIN
H_CT_PersonPhoneFaxType ctppft ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person AND ctppft.Type = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.Fax') AND ppf.Active = 1 FOR XML PATH('')), 1, 2, '') AS Fax, p.Photo,
psup.FirstName AS SuperiorFirstName, psup.LastName AS SuperiorLastName, psup.Code AS SuperiorCode, ou.Code AS OrganizationUnitCode, dbo.GetLocalizedValueFromXML(ou.Name, 'si') AS OrganizationUnit,
dbo.GetLocalizedValueFromXML(ou.Name, 'en') AS OrganizationUnitEn, dbo.GetLocalizedValueFromXML(j.Name, 'sl') AS Job, dbo.GetLocalizedValueFromXML(j.Name, 'en') AS JobEn, j.Code AS JobCode,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(dbo.GetLocalizedValueFromXML(ou.Name, 'sl'), ' - ', ' '), ', ', ' '), '\', ''), ' & ',
' in '), ' / ', ' '), 'đ', 'dz'), 'ć', 'c'), 'ž', 'z'), 'č', 'c'), 'š', 's'), 'Đ', 'DZ'), 'Ć', 'C'), 'Ž', 'Z'), 'Č', 'C'), 'Š', 'S') AS OrganizationUnitISIM, STUFF
((SELECT ', ' + PhoneFaxNumber
FROM H_OrganizationUnitPhoneFax oupf INNER JOIN
H_CT_OrganizationUnitPhoneFaxType ctoupft ON ctoupft.ID_CT_OrganizationUnitPhoneFaxType = oupf.ID_CT_OrganizationUnitPhoneFaxType
WHERE oupf.ID_OrganizationUnit = ou.ID_OrganizationUnit AND ctoupft.Type = 1 AND oupf.Active = 1 FOR XML PATH('')), 1, 2, '') AS OrganizationUnitPhone, STUFF
((SELECT ', ' + PhoneFaxNumber
FROM H_OrganizationUnitPhoneFax oupf INNER JOIN
H_CT_OrganizationUnitPhoneFaxType ctoupft ON ctoupft.ID_CT_OrganizationUnitPhoneFaxType = oupf.ID_CT_OrganizationUnitPhoneFaxType
WHERE oupf.ID_OrganizationUnit = ou.ID_OrganizationUnit AND ctoupft.Type = 3 AND oupf.Active = 1 FOR XML PATH('')), 1, 2, '') AS OrganizationUnitFax, ISNULL(oua.Street, '') + ISNULL(' ' + oua.HouseNumber, '')
AS OrganizationUnitAddress, ctp.Code AS OrganizationUnitPostalCode, dbo.GetLocalizedValueFromXML(ctp.Name, 'sl') AS OrganizationUnitCity, ec.EnterpriseWorkDateStart AS EmployedFromDate,
ec.WorkDateEnd AS EmployedToDate, dbo.GetPersonStatusText(p.ID_Person, pe.ID_Enterprise, GETDATE() + 20, 1, 'sl') AS EmploymentContractStatus, dbo.GetLocalizedValueFromXMLWithDefault(ctDt.Name, 'sl', NULL)
AS EmploymentContractDurationType, ISNULL
((SELECT STUFF
((SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax ppf INNER JOIN
H_CT_PersonPhoneFaxType ctppft ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person AND ctppft.Code IN ('Position0', 'Position1', 'Position2', 'Position3') AND ctppft.Type = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.Other') AND
ppf.Active = 1 FOR XML PATH('')), 1, 2, '')), 0) AS JobTypeCode, dbo.GetLocalizedValueFromXMLWithDefault(cttr.Name, 'sl', NULL) AS EmploymentTerminationReason,
ps.Comment AS StatusComment, STUFF
((SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax ppf INNER JOIN
H_CT_PersonPhoneFaxType ctppft ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person AND ctppft.Code = 'CardNumber' AND ctppft.Type = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.Other') AND ppf.Active = 1 FOR XML PATH('')), 1, 2, '')
AS CardNumber
FROM H_Person p LEFT JOIN
(H_JobAssignment ja INNER JOIN
H_JobPosition jp ON jp.ID_JobPosition = ja.ID_JobPosition INNER JOIN
H_OrganizationUnit ou ON ou.ID_OrganizationUnit = jp.ID_OrganizationUnit INNER JOIN
H_Job j ON j.ID_Job = jp.ID_Job) ON ja.ID_JobAssignment =
(SELECT TOP 1 ja.ID_JobAssignment
FROM H_EmploymentContract ec INNER JOIN
H_JobAssignment ja ON ec.ID_Person = p.ID_Person AND ec.ID_EmploymentContract = ja.ID_EmploymentContract AND ec.ID_Enterprise = dbo.GetPersonPrimaryEnterpriseID(p.ID_Person) AND
ec.Active = 1 AND ja.Active = 1
WHERE (ja.DateEnd IS NULL OR
ja.DateEnd >= GETDATE()) AND ja.Status = dbo.GetConstantTinyInt('H_JobAssignment.Status.Valid')
ORDER BY ISNULL(ja.DateEnd, '18001231'), ja.DateStart DESC) /*dbo.GetLastJobAssignmentID(p.ID_Person, NULL, GETDATE(), GETDATE(), 1) */ INNER JOIN
H_PersonStatus ps ON ps.ID_PersonStatus =
(SELECT TOP 1 ps.ID_PersonStatus
FROM H_PersonEnterprise pe2 INNER JOIN
H_PersonStatus ps ON ps.ID_PersonEnterprise = pe2.ID_PersonEnterprise AND ps.Active = 1 INNER JOIN
H_CT_PersonStatus ctps ON ctps.ID_CT_PersonStatus = ps.ID_CT_PersonStatus
WHERE pe2.ID_Person = p.ID_Person AND pe2.Active = 1 /*AND (ps.ValidFrom IS NULL OR ps.ValidFrom <= GETDATE()) */ AND (ps.ValidTo IS NULL OR
ps.ValidTo >= GETDATE())
ORDER BY pe2.IsPrimary DESC) INNER JOIN
H_PersonEnterprise pe ON pe.ID_PersonEnterprise = ps.ID_PersonEnterprise AND pe.Active = 1 LEFT JOIN
H_CT_EmploymentTerminationReason cttr ON cttr.ID_CT_EmploymentTerminationReason = ps.ID_CT_EmploymentTerminationReason LEFT JOIN
H_EmploymentContract ec ON ec.ID_EmploymentContract = dbo.GetLastEmploymentContractID(p.ID_Person, NULL, NULL, NULL, 1) LEFT JOIN
H_CT_EmploymentContractDurationType ctDt ON ctDt.ID_CT_EmploymentContractDurationType = ec.ID_CT_EmploymentContractDurationType LEFT JOIN
H_Person psup ON psup.ID_Person = pe.ID_PersonSuperior LEFT JOIN
H_OrganizationUnitAddress oua ON oua.ID_OrganizationUnitAddress = dbo.GetOrganizationUnitAddressID(ou.ID_OrganizationUnit, 1, GETDATE()) LEFT JOIN
H_CT_Post ctp ON ctp.ID_CT_Post = oua.ID_CT_Post LEFT JOIN
H_CT_Town ctt ON ctt.ID_CT_Town = oua.ID_CT_Town LEFT JOIN
H_CT_JobType3 dm ON dm.ID_CT_JobType3 = j.ID_CT_JobType3 LEFT JOIN
H_PersonAddress addres ON addres.ID_PersonAddress = dbo.GetPersonAddressID(p.ID_Person, dbo.GetConstantTinyint('PersonAddressType.Type.Permanent'), NULL)
WHERE p.Active = 1 AND isnull(p.code, 0) <> '00220'
Please help.
June 14, 2018 at 1:58 am
There a lot going on in that query, and don't forget, we can't run it (we don't have access to your server). I can see loads of sub queries, as well as Scalar functions in your SELECT, and sub SELECTs (and i think there's some in your JOINs as well, which'll probably be awful for performance).
With what we have, it's going to be pretty much impossible for us to help you actually find the issue. I would, personally, suggest chopping the query into parts, and running those. Introduce parts back into the query and find out where the query falls over, then you can look at that part and see what's going on.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 14, 2018 at 2:07 am
This is one very busy view! It is very hard to advice on anything here as one does not have any schema nor data.
😎
On this error, the first thoughts are that there is either a character data in a numerical-character column or it could be the missing value function with a type directive on the FOR XML clauses.
Questions, how much can you change and how is the performance?
June 14, 2018 at 5:43 am
I can change it and I think sooner or later I will have to change it but I'm new at this job, it would take me a really long time. Performance is actually ok (3 seconds and it returns 621 rows). I use this view for many other views and stored procedures that I use on a daily basis.
All the tables, that are joined in this view are not ours, they belong to the company that owns and administers our HRM data, I can't touch the tables that start with HRM_.
June 14, 2018 at 7:19 am
sani.bozic - Thursday, June 14, 2018 5:43 AMI can change it and I think sooner or later I will have to change it but I'm new at this job, it would take me a really long time. Performance is actually ok (3 seconds and it returns 621 rows). I use this view for many other views and stored procedures that I use on a daily basis.
All the tables, that are joined in this view are not ours, they belong to the company that owns and administers our HRM data, I can't touch the tables that start with HRM_.
Are you using the query designer to create/modify your views?
If so, not all views can be represented in the designer meaning you need to learn to work the queries directly in SSMS.
June 14, 2018 at 7:32 am
Lynn Pettis - Thursday, June 14, 2018 7:19 AMsani.bozic - Thursday, June 14, 2018 5:43 AMI can change it and I think sooner or later I will have to change it but I'm new at this job, it would take me a really long time. Performance is actually ok (3 seconds and it returns 621 rows). I use this view for many other views and stored procedures that I use on a daily basis.
All the tables, that are joined in this view are not ours, they belong to the company that owns and administers our HRM data, I can't touch the tables that start with HRM_.Are you using the query designer to create/modify your views?
If so, not all views can be represented in the designer meaning you need to learn to work the queries directly in SSMS.
I don't use the query designer.
June 14, 2018 at 7:44 am
This looks like a job for INFORMATION_SCHEMA - specifically, information_schema.columns.
Let us consider the error message:
Conversion failed when converting the nvarchar value 'XXX' to data type int.
What does this tell us? It says it's doing a join or a where clause, and that one column is nvarchar and the other is int. So first, let's look at the joins
H_JobAssignment ja INNER JOIN
H_JobPosition jp ON jp.ID_JobPosition = ja.ID_JobPosition
we'd go
select * from information_schema.columns where table_name in ('H_JobAssignment', 'H_JobPosition') and column_name in ('ID_JobPosition ','ID_JobPosition')
and look at the data_type column. Is one of the fields int and the other nvarchar? if so, you might have found it - if not move on to the next join and repeat the process. If you think you've found it, go select * from <table with the nvarchar column> where <columnName>= 'XXX'
and if you see an XXX you've found it - your bad data, If you go through all the joins and still can't find it you'll have to move on to the where clause. Look for column to column comparisons like
WHERE pe2.ID_Person = p.ID_Person
Guaranteed to work, might be a bit tedious though. You gotta do it though, no one can solve this for you remotely.
June 14, 2018 at 7:53 am
lnardozi 61862 - Thursday, June 14, 2018 7:44 AMThis looks like a job for INFORMATION_SCHEMA - specifically, information_schema.columns.Let us consider the error message:
Conversion failed when converting the nvarchar value 'XXX' to data type int.
What does this tell us? It says it's doing a join or a where clause, and that one column is nvarchar and the other is int. So first, let's look at the joins
H_JobAssignment ja INNER JOIN
H_JobPosition jp ON jp.ID_JobPosition = ja.ID_JobPositionwe'd go
select * from information_schema.columns where table_name in ('H_JobAssignment', 'H_JobPosition') and column_name in ('ID_JobPosition ','ID_JobPosition')
and look at the data_type column. Is one of the fields int and the other nvarchar? if so, you might have found it - if not move on to the next join and repeat the process. If you think you've found it, goselect * from <table with the nvarchar column> where <columnName>= 'XXX'
and if you see an XXX you've found it - your bad data, If you go through all the joins and still can't find it you'll have to move on to the where clause. Look for column to column comparisons likeand do the information_schema compare bit on those/[/code]
WHERE pe2.ID_Person = p.ID_PersonGuaranteed to work, might be a bit tedious though. You gotta do it though, no one can solve this for you remotely.
Personally, I would use the system views instead of the information_schema views. The system views can provide more information if/when needed. Also, the INFORMATION_SCHEMA views are not as universal as they are made out to be.
June 14, 2018 at 9:37 am
I didn't understand the error message correctly so I didn't know that I am searching for an actual XXX string. I thought that meant something else, like ***. So I looked for the value XXX, changed it to an actual number and the problem is gone. I didn't see it because it was in some table that was used in some view, that was used in another view, that was used in another view and so on. I asumed that the other error roots in this one, cuz this View is so messy. But the error that is in the title of the post persists - Error in WHERE clause near '('. SQL text cannot be represented in the grid pane and diagram pane.
June 14, 2018 at 10:35 am
sani.bozic - Thursday, June 14, 2018 9:37 AMI didn't understand the error message correctly so I didn't know that I am searching for an actual XXX string. I thought that meant something else, like ***. So I looked for the value XXX, changed it to an actual number and the problem is gone. I didn't see it because it was in some table that was used in some view, that was used in another view, that was used in another view and so on. I asumed that the other error roots in this one, cuz this View is so messy. But the error that is in the title of the post persists - Error in WHERE clause near '('. SQL text cannot be represented in the grid pane and diagram pane.
Lynn Pettis answered you on that. There are some queries that cannot be represented in the grid pane. I've cleaned up the formatting of your query so that it's a lot more easily read, and perhaps problems will be easier to see. Mind you, it's a lot less compact, but that really shouldn't be quite the issue that so many people perceive it as. I've always figured that if you've got this much stuff in one query, then either it really needs to be large and therefore should engender a fair amount of caution when editing, and thus the length will actually help by slowing you down a little, or maybe you should be re-designing the mess from scratch and re-thinking how you derive data.
Here's the updated query:SELECT
ROW_NUMBER() OVER (ORDER BY p.FirstName, p.LastName, p.Code DESC) AS Id,
p.FirstName,
p.LastName,
p.Code,
p.TaxNumber,
dbo.GetPersonAddressText(addres.ID_Person, addres.ID_CT_PersonAddressType, addres.Street, addres.HouseNumber, addres.ID_CT_Post, addres.ValidFrom, addres.ValidTo, addres.Comment, NULL) AS PersonAddress,
dbo.GetCT_PostTextById(addres.ID_CT_Post, 'sl') AS PersonAddressPost,
STUFF((
SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax AS ppf
INNER JOIN H_CT_PersonPhoneFaxType AS ctppft
ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person
AND ctppft.[Type] = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.Business')
AND ppf.Active = 1
FOR XML PATH('')
), 1, 2, '') AS Phone,
STUFF((
SELECT ', ' + Email
FROM H_PersonEmail AS pe
WHERE pe.ID_Person = p.ID_Person
AND pe.Active = 1
ORDER BY pe.IsPrimary DESC
FOR XML PATH('')
), 1, 2, '') AS Email,
STUFF((
SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax AS ppf
INNER JOIN H_CT_PersonPhoneFaxType AS ctppft
ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person
AND ctppft.[Type] = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.MobileBusiness')
AND ppf.Active = 1
FOR XML PATH('')
), 1, 2, '') AS Gsm,
STUFF((
SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax AS ppf
INNER JOIN H_CT_PersonPhoneFaxType AS ctppft
ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person
AND ctppft.[Type] = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.Fax')
AND ppf.Active = 1
FOR XML PATH('')
), 1, 2, '') AS Fax,
p.Photo,
psup.FirstName AS SuperiorFirstName,
psup.LastName AS SuperiorLastName,
psup.Code AS SuperiorCode,
ou.Code AS OrganizationUnitCode,
dbo.GetLocalizedValueFromXML(ou.Name, 'si') AS OrganizationUnit,
dbo.GetLocalizedValueFromXML(ou.Name, 'en') AS OrganizationUnitEn,
dbo.GetLocalizedValueFromXML(j.Name, 'sl') AS Job,
dbo.GetLocalizedValueFromXML(j.Name, 'en') AS JobEn,
j.Code AS JobCode,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(dbo.GetLocalizedValueFromXML(ou.Name, 'sl'), ' - ', ' '),
', ', ' '),
'\', ''),
' & ', ' in '),
' / ', ' '),
'Ä‘', 'dz'),
'ć', 'c'),
'ž', 'z'),
'Ä', 'c'),
'Å¡', 's'),
'Ä', 'DZ'),
'Ć', 'C'),
'Ž', 'Z'),
'Č', 'C'),
'Å ', 'S') AS OrganizationUnitISIM,
STUFF((
SELECT ', ' + PhoneFaxNumber
FROM H_OrganizationUnitPhoneFax AS oupf
INNER JOIN H_CT_OrganizationUnitPhoneFaxType AS ctoupft
ON ctoupft.ID_CT_OrganizationUnitPhoneFaxType = oupf.ID_CT_OrganizationUnitPhoneFaxType
WHERE oupf.ID_OrganizationUnit = ou.ID_OrganizationUnit
AND ctoupft.Type = 1
AND oupf.Active = 1
FOR XML PATH('')
), 1, 2, '') AS OrganizationUnitPhone,
STUFF((
SELECT ', ' + PhoneFaxNumber
FROM H_OrganizationUnitPhoneFax AS oupf
INNER JOIN H_CT_OrganizationUnitPhoneFaxType AS ctoupft
ON ctoupft.ID_CT_OrganizationUnitPhoneFaxType = oupf.ID_CT_OrganizationUnitPhoneFaxType
WHERE oupf.ID_OrganizationUnit = ou.ID_OrganizationUnit
AND ctoupft.[Type] = 3
AND oupf.Active = 1
FOR XML PATH('')
), 1, 2, '') AS OrganizationUnitFax,
ISNULL(oua.Street, '') + ISNULL(' ' + oua.HouseNumber, '') AS OrganizationUnitAddress,
ctp.Code AS OrganizationUnitPostalCode,
dbo.GetLocalizedValueFromXML(ctp.Name, 'sl') AS OrganizationUnitCity,
ec.EnterpriseWorkDateStart AS EmployedFromDate,
ec.WorkDateEnd AS EmployedToDate,
dbo.GetPersonStatusText(p.ID_Person, pe.ID_Enterprise, GETDATE() + 20, 1, 'sl') AS EmploymentContractStatus,
dbo.GetLocalizedValueFromXMLWithDefault(ctDt.Name, 'sl', NULL) AS EmploymentContractDurationType,
ISNULL((
SELECT STUFF((
SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax AS ppf
INNER JOIN H_CT_PersonPhoneFaxType AS ctppft
ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person
AND ctppft.Code IN ('Position0', 'Position1', 'Position2', 'Position3')
AND ctppft.[Type] = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.Other')
AND ppf.Active = 1
FOR XML PATH('')
), 1, 2, '')
), 0) AS JobTypeCode,
dbo.GetLocalizedValueFromXMLWithDefault(cttr.Name, 'sl', NULL) AS EmploymentTerminationReason,
ps.Comment AS StatusComment,
STUFF((
SELECT ', ' + PhoneFaxNumber
FROM H_PersonPhoneFax AS ppf
INNER JOIN H_CT_PersonPhoneFaxType AS ctppft
ON ctppft.ID_CT_PersonPhoneFaxType = ppf.ID_CT_PersonPhoneFaxType
WHERE ppf.ID_Person = p.ID_Person
AND ctppft.Code = 'CardNumber'
AND ctppft.[Type] = dbo.GetConstantTinyint('H_CT_PersonPhoneFaxType.Type.Other')
AND ppf.Active = 1
FOR XML PATH('')
), 1, 2, '') AS CardNumber
FROM H_Person AS p
LEFT JOIN (H_JobAssignment AS ja
INNER JOIN H_JobPosition AS jp
ON jp.ID_JobPosition = ja.ID_JobPosition
INNER JOIN H_OrganizationUnit AS ou
ON ou.ID_OrganizationUnit = jp.ID_OrganizationUnit
INNER JOIN H_Job AS j
ON j.ID_Job = jp.ID_Job)
ON ja.ID_JobAssignment = (
SELECT TOP (1) ja.ID_JobAssignment
FROM H_EmploymentContract AS ec
INNER JOIN H_JobAssignment AS ja
ON ec.ID_Person = p.ID_Person
AND ec.ID_EmploymentContract = ja.ID_EmploymentContract
AND ec.ID_Enterprise = dbo.GetPersonPrimaryEnterpriseID(p.ID_Person)
AND ec.Active = 1
AND ja.Active = 1
WHERE (
ja.DateEnd IS NULL
OR
ja.DateEnd >= GETDATE()
)
AND ja.[Status] = dbo.GetConstantTinyInt('H_JobAssignment.Status.Valid')
ORDER BY
ISNULL(ja.DateEnd, '18001231'),
ja.DateStart DESC
) /*dbo.GetLastJobAssignmentID(p.ID_Person, NULL, GETDATE(), GETDATE(), 1) */
INNER JOIN H_PersonStatus AS ps
ON ps.ID_PersonStatus = (
SELECT TOP (1) ps.ID_PersonStatus
FROM H_PersonEnterprise AS pe2
INNER JOIN H_PersonStatus AS ps
ON ps.ID_PersonEnterprise = pe2.ID_PersonEnterprise
AND ps.Active = 1
INNER JOIN H_CT_PersonStatus AS ctps
ON ctps.ID_CT_PersonStatus = ps.ID_CT_PersonStatus
WHERE pe2.ID_Person = p.ID_Person
AND pe2.Active = 1
/*AND (ps.ValidFrom IS NULL OR ps.ValidFrom <= GETDATE()) */
AND (
ps.ValidTo IS NULL
OR
ps.ValidTo >= GETDATE()
)
ORDER BY pe2.IsPrimary DESC
)
INNER JOIN H_PersonEnterprise AS pe
ON pe.ID_PersonEnterprise = ps.ID_PersonEnterprise
AND pe.Active = 1
LEFT OUTER JOIN H_CT_EmploymentTerminationReason AS cttr
ON cttr.ID_CT_EmploymentTerminationReason = ps.ID_CT_EmploymentTerminationReason
LEFT OUTER JOIN H_EmploymentContract AS ec
ON ec.ID_EmploymentContract = dbo.GetLastEmploymentContractID(p.ID_Person, NULL, NULL, NULL, 1)
LEFT OUTER JOIN H_CT_EmploymentContractDurationType AS ctDt
ON ctDt.ID_CT_EmploymentContractDurationType = ec.ID_CT_EmploymentContractDurationType
LEFT OUTER JOIN H_Person AS psup
ON psup.ID_Person = pe.ID_PersonSuperior
LEFT OUTER JOIN H_OrganizationUnitAddress AS oua
ON oua.ID_OrganizationUnitAddress = dbo.GetOrganizationUnitAddressID(ou.ID_OrganizationUnit, 1, GETDATE())
LEFT OUTER JOIN H_CT_Post AS ctp
ON ctp.ID_CT_Post = oua.ID_CT_Post
LEFT OUTER JOIN H_CT_Town AS ctt
ON ctt.ID_CT_Town = oua.ID_CT_Town
LEFT OUTER JOIN H_CT_JobType3 AS dm
ON dm.ID_CT_JobType3 = j.ID_CT_JobType3
LEFT OUTER JOIN H_PersonAddress AS addres
ON addres.ID_PersonAddress = dbo.GetPersonAddressID(p.ID_Person, dbo.GetConstantTinyint('PersonAddressType.Type.Permanent'), NULL)
WHERE p.Active = 1
AND ISNULL(p.code, 0) <> '00220';
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply