Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

T-SQL CTE question

Hi,

I've got a CTE that was working fine but due to some data changes, is erratic. This query should return one row for each jobname but it returns more than one. Could someone point out what's wrong with this:
WITH CTE AS
		(
SELECT DISTINCT process_id, jobname, jobnameContactSource
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid FROM dbo.tb_process_job_Contact
				where process_id = t.process_id 
				AND	(tb_process_job_Contact.version =(select max(t2.version) from tb_process t2
						where tb_process_job_Contact.process_id = t2.process_id ))
				AND jobname = t.jobname and process_contact_job_id = 1 FOR XML PATH('') ), 1, 2, ''), '') 
				AS admins
			
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid FROM dbo.tb_process_job_Contact 
				where process_id = t.process_id 
				AND	(tb_process_job_Contact.version =(select max(t2.version) from tb_process t2
						where tb_process_job_Contact.process_id = t2.process_id ))
				AND jobname = t.jobname and process_contact_job_id = 2 FOR XML PATH('') ), 1, 2, ''), '') 
				AS approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid FROM dbo.tb_process_job_Contact 
				where process_id = t.process_id 
				AND	(tb_process_job_Contact.version =(select max(t2.version) from tb_process t2
						where tb_process_job_Contact.process_id = t2.process_id ))
				AND jobname = t.jobname and process_contact_job_id = 3 FOR XML PATH('') ), 1, 2, ''), '') 
				AS esc_approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid FROM dbo.tb_process_job_Contact 
				where process_id = t.process_id 
				AND	(tb_process_job_Contact.version =(select max(t2.version) from tb_process t2
						where tb_process_job_Contact.process_id = t2.process_id ))
				AND jobname = t.jobname and process_contact_job_id = 4 FOR XML PATH('') ), 1, 2, ''), '') 
				AS secondlevel_approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid FROM dbo.tb_process_job_Contact 
				where process_id = t.process_id 
				AND	(tb_process_job_Contact.version =(select max(t2.version) from tb_process t2
						where tb_process_job_Contact.process_id = t2.process_id ))
				AND jobname = t.jobname and process_contact_job_id = 5 FOR XML PATH('') ), 1, 2, ''), '') 
				AS secondlevel_esc_approvers
				
				FROM dbo.tb_process_job_Contact t 
				where process_id = 2062
				AND	jobname = 'IT-OS-CONTRACTS DATA MART-Developer on Non Prod'
				AND	process_contact_Actv_ind = 'Yes'
               and 	(t.version =(select max(t2.version) from tb_process t2
						where t.process_id = t2.process_id ))
	)
		
		SELECT process_Id, jobName, jobNameContactSource
		,	CASE WHEN LEFT(admins, 1) = ',' THEN 
		RIGHT(admins, LEN(admins) - 1) ELSE admins END admins
		,	CASE WHEN LEFT(approvers, 1) = ',' 
		THEN RIGHT(approvers, LEN(approvers) - 1) ELSE approvers END approvers
		,	CASE WHEN LEFT(secondlevel_approvers, 1) = ',' 
		THEN RIGHT(esc_approvers, LEN(esc_approvers) - 1) 
		ELSE esc_approvers END esc_approvers
		,	CASE WHEN LEFT(secondlevel_approvers, 1) = ',' 
		THEN RIGHT(secondlevel_approvers, LEN(secondlevel_approvers) - 1) 
		ELSE secondlevel_approvers END secondlevel_approvers
		,	CASE WHEN LEFT(secondlevel_esc_approvers, 1) = ',' 
		THEN RIGHT(secondlevel_esc_approvers, LEN(secondlevel_esc_approvers) - 1) 
		ELSE secondlevel_esc_approvers END secondlevel_esc_approvers
		FROM CTE

Open in new window

0
roger v
Asked:
roger v
  • 9
  • 7
1 Solution
 
LowfatspreadCommented:
is it to do with the fact that you are comparing different versions?

in the xml sub queries
(tb_process_job_Contact.version =(select max(t2.version) from tb_process t2

and in the main where clause
(t.version =(select max(t2.version) from tb_process t2


why the distinct if you expect only a single row?

please explain your system...
or at least show the underlying table ddl
0
 
roger vAuthor Commented:
@lowfatspread:

Yes, it got messed up when the version was introduced. I need to get the highest (max) version.

The underlying table has the following data:

 

So I should be getting for every row for process_id = 1666

process_id              jobname                         process_contact_userid
=========             =========                    ======================
1666                       Crane Operation           xt3443, jb9909


But I'm getting two rows of 1666

  sampleQuery.txt
0
 
LowfatspreadCommented:
try this

although i'm not clear that you need the case statement in the select from cte... hasn't the stuff removed the leading comma?


WITH CTE AS
		(
SELECT  process_id, jobname, jobnameContactSource
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 1 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS admins
			
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 2 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 3 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS esc_approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 4 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS secondlevel_approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 5 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS secondlevel_esc_approvers
				,row_number() over (partition by process_ID 
                                                     ORDER BY VERSION DESC,process_contact_role_id DESC) as rn
				FROM dbo.tb_process_job_Contact t 
				where process_id = 2062
				AND   jobname = 'IT-OS-CONTRACTS DATA MART-Developer on Non Prod'
				AND   process_contact_Actv_ind = 'Yes'               
	)
		
		SELECT process_Id, jobName, jobNameContactSource
		,	CASE WHEN LEFT(admins, 1) = ',' THEN 
		RIGHT(admins, LEN(admins) - 1) ELSE admins END admins
		,	CASE WHEN LEFT(approvers, 1) = ',' 
		THEN RIGHT(approvers, LEN(approvers) - 1) ELSE approvers END approvers
		,	CASE WHEN LEFT(secondlevel_approvers, 1) = ',' 
		THEN RIGHT(esc_approvers, LEN(esc_approvers) - 1) 
		ELSE esc_approvers END esc_approvers
		,	CASE WHEN LEFT(secondlevel_approvers, 1) = ',' 
		THEN RIGHT(secondlevel_approvers, LEN(secondlevel_approvers) - 1) 
		ELSE secondlevel_approvers END secondlevel_approvers
		,	CASE WHEN LEFT(secondlevel_esc_approvers, 1) = ',' 
		THEN RIGHT(secondlevel_esc_approvers, LEN(secondlevel_esc_approvers) - 1) 
		ELSE secondlevel_esc_approvers END secondlevel_esc_approvers
		FROM CTE
 where rn=1

Open in new window

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

 
LowfatspreadCommented:
even though you are getting the max version... you still have multiple rows  by contact type...

your basic problem appears to be that you haven't normalised the table  design ...

you should have a table for the job description ... and a table for all the contact information relating to the job...

job details
processid , jobname , version

jobcontacts
proessid, version, contacttype, contactuserid

then just select against a join of the tables....
0
 
roger vAuthor Commented:
@Lowfatspread:

I get an error "invalid column name process_id", so I changed all of them to t.process_id and so on but it still gives me the error.
0
 
LowfatspreadCommented:
cant see any error

please post what you are executing
WITH CTE AS
		(
SELECT  t.process_id, t,jobname, t.jobnameContactSource
				, NULLIF(STUFF((SELECT ', ' + x.process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where x.process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 1 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS admins
			
				, NULLIF(STUFF((SELECT ', ' + x,process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where x.process_id = t.process_id 
				                     AND x.version = t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 2 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS approvers
				
				, NULLIF(STUFF((SELECT ', ' + x,process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where x.process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 3 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS esc_approvers
				
				, NULLIF(STUFF((SELECT ', ' + x,process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where x,process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 4 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS secondlevel_approvers
				
				, NULLIF(STUFF((SELECT ', ' + x.process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where x,process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 5 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS secondlevel_esc_approvers
				,row_number() over (partition by t,process_ID 
                                                     ORDER BY t.VERSION DESC,t.process_contact_role_id DESC) as rn
				FROM dbo.tb_process_job_Contact t 
				where t.process_id = 2062
				AND   t.jobname = 'IT-OS-CONTRACTS DATA MART-Developer on Non Prod'
				AND   t.process_contact_Actv_ind = 'Yes'               
	)
		
		SELECT process_Id, jobName, jobNameContactSource
		,	CASE WHEN LEFT(admins, 1) = ',' THEN 
		RIGHT(admins, LEN(admins) - 1) ELSE admins END admins
		,	CASE WHEN LEFT(approvers, 1) = ',' 
		THEN RIGHT(approvers, LEN(approvers) - 1) ELSE approvers END approvers
		,	CASE WHEN LEFT(secondlevel_approvers, 1) = ',' 
		THEN RIGHT(esc_approvers, LEN(esc_approvers) - 1) 
		ELSE esc_approvers END esc_approvers
		,	CASE WHEN LEFT(secondlevel_approvers, 1) = ',' 
		THEN RIGHT(secondlevel_approvers, LEN(secondlevel_approvers) - 1) 
		ELSE secondlevel_approvers END secondlevel_approvers
		,	CASE WHEN LEFT(secondlevel_esc_approvers, 1) = ',' 
		THEN RIGHT(secondlevel_esc_approvers, LEN(secondlevel_esc_approvers) - 1) 
		ELSE secondlevel_esc_approvers END secondlevel_esc_approvers
		FROM CTE
                where rn=1

Open in new window

0
 
roger vAuthor Commented:
Here is what I've got:
WITH CTE AS
		(
SELECT  process_id, jobname, jobnameContactSource
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 1 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS admins
			
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 2 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 3 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS esc_approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 4 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS secondlevel_approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 5 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS secondlevel_esc_approvers
				,row_number() over (partition by process_ID 
                                                     ORDER BY VERSION DESC,process_contact_role_id DESC) as rn
				FROM dbo.tb_process_job_Contact t 
				where process_id = 2243
				AND   jobname = 'MART-Developer on Non Prod'
				AND   process_contact_Actv_ind = 'Yes'               
	)
		
		SELECT process_Id, jobName, jobNameContactSource
		,	CASE WHEN LEFT(admins, 1) = ',' THEN 
		RIGHT(admins, LEN(admins) - 1) ELSE admins END admins
		,	CASE WHEN LEFT(approvers, 1) = ',' 
		THEN RIGHT(approvers, LEN(approvers) - 1) ELSE approvers END approvers
		,	CASE WHEN LEFT(secondlevel_approvers, 1) = ',' 
		THEN RIGHT(esc_approvers, LEN(esc_approvers) - 1) 
		ELSE esc_approvers END esc_approvers
		,	CASE WHEN LEFT(secondlevel_approvers, 1) = ',' 
		THEN RIGHT(secondlevel_approvers, LEN(secondlevel_approvers) - 1) 
		ELSE secondlevel_approvers END secondlevel_approvers
		,	CASE WHEN LEFT(secondlevel_esc_approvers, 1) = ',' 
		THEN RIGHT(secondlevel_esc_approvers, LEN(secondlevel_esc_approvers) - 1) 
		ELSE secondlevel_esc_approvers END secondlevel_esc_approvers
		FROM CTE
 where rn=1

Open in new window

0
 
LowfatspreadCommented:
ok what was the actual error and please indicate the appropriate line...
0
 
roger vAuthor Commented:
Msg 207, Level 16, State 1, Line 43
Invalid column name 'process_id'
0
 
LowfatspreadCommented:
o.k. i don't get any errors with that

have you started the cte with a semi colon?

are you sure you're executing the whole statement?
0
 
LowfatspreadCommented:
this

;WITH CTE AS
		(
SELECT  process_id, jobname, jobnameContactSource
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 1 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS admins
			
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 2 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 3 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS esc_approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 4 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS secondlevel_approvers
				
				, NULLIF(STUFF((SELECT ', ' + process_contact_userid 
                                                  FROM dbo.tb_process_job_Contact as x
				                   where process_id = t.process_id 
				                     AND x.version =t.version
				                     AND x.jobname = t.jobname 
                                                     and x.process_contact_job_id = 5 
                                                FOR XML PATH('') )
                                               , 1, 2, ''), '') 
				AS secondlevel_esc_approvers
				,row_number() over (partition by process_ID 
                                                     ORDER BY VERSION DESC,process_contact_role_id DESC) as rn
				FROM dbo.tb_process_job_Contact t 
				where process_id = 2243
				AND   jobname = 'MART-Developer on Non Prod'
				AND   process_contact_Actv_ind = 'Yes'               
	)
		
		SELECT process_Id, jobName, jobNameContactSource
		, admins
		, approvers
		, esc_approvers
		, secondlevel_approvers
		, secondlevel_esc_approvers
		FROM CTE
 where rn=1

Open in new window


gives

process_Id  jobName                                                      jobNameContactSource admins                                                                                                                                                                                                                                                           approvers                                                                                                                                                                                                                                                        esc_approvers                                                                                                                                                                                                                                                    secondlevel_approvers                                                                                                                                                                                                                                            secondlevel_esc_approvers
----------- ------------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2243        MART-Developer on Non Prod                                   1                    abc                                                                                                                                                                                                                                                              def, abc                                                                                                                                                                                                                                                         NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             NULL

Open in new window



for this test setup

--drop table dbo.tb_process_job_Contact
--go
create table dbo.tb_process_job_Contact

(process_id int not null
 ,jobname varchar(60), jobnameContactSource int
 ,process_contact_job_id int,process_contact_Actv_ind char(3),process_contact_role_id int,
 process_contact_userid varchar(30)
 ,version int
)
select * from dbo.tb_process_job_Contact
insert into dbo.tb_process_job_Contact
select 2243,'MART-Developer on Non Prod',1,1,'yes',1,'abc',2
union all select 2243,'MART-Developer on Non Prod',1,1,'yes',1,'abc',1
union all select 2243,'MART-Developer on Non Prod',1,2,'yes',1,'def',2
union all select 2243,'MART-Developer on Non Prod',1,2,'yes',1,'abc',2
		
				

Open in new window

0
 
roger vAuthor Commented:
OK it worked this time! I'm gonna test it with multiple rows and see if it is indeed correct and not a false positive.

Question: row_number() - is this to remove duplicates? Also, why did you filter on rn?
0
 
LowfatspreadCommented:
yes

well the row number  gives the order of the rows by for each process id  ordered by  version and contact id

so rn=1 gives you the latest values for a process id

your "duplicates" arise because there are many rows for a process_id and the same version number...
(you always need to consider the primary/natural key for the row)
0
 
roger vAuthor Commented:
@lowfatspread:

I'm still confused by the row_number() and partition by. What is the purpose of them?

My primary key for this table is a combination of process_id, version, jobname, process_contact_job_id, process_contact_userid.
0
 
roger vAuthor Commented:
I'll ask another question on how this all works, a break up if you will.
0
 
LowfatspreadCommented:
OVER is a windowing function introduced with sql server 2005

think of it as a row level group by clause...

row_number() is a function than can be used with the OVER function

    the partition clause acts like a group by  
   and the order by determines the order of the subset for the scope of the over function,,

row_number() produces a sequential number for each of the rows starting at 1 for each "group" identified by the partition by clause in the order determined by the order by clause...

In this case the main determinant seems to be the process id with the other components of the primary key just determining the order/version hierarchy of the data....

you only want 1 row per process id , utilising the highest version number present...

so  ordering by version descending and the contact id descending appears to do that for us...

ie indicates the "latest" row for the jobname info etc , and since we only take the "first" one (rn=1) that overcomes your
duplicate row problem...

as i previously stated you appear to be storing redundant data on what should basically be just a relationship link table
(jobname etc,,, rather than just the contact userid which is related to the contact type for the process...)
proper 3rd normal form normalisation of the table would probably overcome some of this complexity
so that you could just query a "single" row from a process information table and use the process contact table to resolve
the latest contact ids...

... your max(version) processing was on the righr lines but needed to be extended to account for the muliple possible contact ids that can be specified for each process...

hth
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now