Code Change - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Code Change

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-21 : 05:33:15

Hallo,

I have an error with this select query -- the error is at from bas highlighted. I want to read th eprac_no and col_uid from the above select statement... any help please.. Thanks

select b.*
from
(SELECT     TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end) 
                      AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start, 
                      MAX(dbo.QryColProcessing.system_time) AS sys_time
FROM         gprdsql.TblColProcessing INNER JOIN
                      dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage,dbo.QryColProcessing.system_time 
HAVING      (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
                      (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
ORDER BY dbo.QryColProcessing.prac_no) b
inner join
(select prac_no, Max(col_uid) as Maxcol_uid
    from b
    group by prac_no, col_uid
) m 
on b.prac_no = m.prac_no and b.col_uid = m.Maxcol_uid
inner join
(select prac_no,col_uid, Max(sys_time) as Maxsys_time
from b
   group by prac_no, col_uid, sys_time
)y
on m.prac_no = y.prac_no and m.Maxcol_uid = y.col_uid and b.sys_time = y.Maxsys_time

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-21 : 05:39:05

It looks like you want to refer the result set "B" many times in the query.

So defined it as CTE, then you will be able to refer it many times.

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 05:39:50

Your derived table b doesn't exist in either of the scopes you are referencing it in.

You could turn it into a CTE.

Look up common table expression in books online if you have never used them before.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-21 : 05:46:16

So is there any other way I can update the select query.. Or any other way of establishing what I want to achieve in a different way

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-21 : 05:56:04

I guess we already suggested you an alternative.

try somehting like

; WITH B AS
(SELECT TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start,
MAX(dbo.QryColProcessing.system_time) AS sys_time
FROM gprdsql.TblColProcessing INNER JOIN
dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage,dbo.QryColProcessing.system_time
HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
(gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
ORDER BY dbo.QryColProcessing.prac_no)
select b.*
from B inner join
(select prac_no, Max(col_uid) as Maxcol_uid
from b
group by prac_no, col_uid
) m
on b.prac_no = m.prac_no and b.col_uid = m.Maxcol_uid
inner join
(select prac_no,col_uid, Max(sys_time) as Maxsys_time
from b
group by prac_no, col_uid, sys_time
)y
on m.prac_no = y.prac_no and m.Maxcol_uid = y.col_uid and b.sys_time = y.Maxsys_time

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 06:00:52

well you could replace each reference of b with the whole text of your select query again.

or turn it into a view if it's going to be used a lot.

Or even dump the query for b into a temp table if you only want to use it once -- be careful with this as it leads to cache plan recompiles.

Why the reluctance for the CTE -- it's very efficient and once you get your head round them they are very useful for all kinds of things.

Try this (I haven't tested as I don't have your data):


; WITH b AS (
SELECT TOP (100) PERCENT
	dbo.QryColProcessing.prac_no
	, MAX(dbo.QryColProcessing.col_uid) AS col_uid
	, MAX(dbo.QryColProcessing.audit_end) AS audit_end
	, gprdsql.TblColProcessing.status
	, gprdsql.TblColProcessing.stage
	, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start
	, MAX(dbo.QryColProcessing.system_time) AS sys_time
FROM
	gprdsql.TblColProcessing
	INNER JOIN dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY
	dbo.QryColProcessing.prac_no
	, gprdsql.TblColProcessing.status
	, gprdsql.TblColProcessing.stage
	, dbo.QryColProcessing.system_time 
HAVING
	(gprdsql.TblColProcessing.status = 'completed')
	AND (gprdsql.TblColProcessing.stage = 'cancelled')

	OR (gprdsql.TblColProcessing.status = 'completed')

	AND (gprdsql.TblColProcessing.stage = 'stage 8')
	)
select b.*
from
	b
	inner join (
		select
			prac_no
			, Max(col_uid) as Maxcol_uid
		from b
		group by
			prac_no
			, col_uid
		) m on b.prac_no = m.prac_no and b.col_uid = m.Maxcol_uid

	inner join (
		select
			prac_no,col_uid
			, Max(sys_time) as Maxsys_time
		from b
		group by
			prac_no
			, col_uid
			, sys_time
		) y on m.prac_no = y.prac_no and m.Maxcol_uid = y.col_uid and b.sys_time = y.Maxsys_time
ORDER BY
	b.prac_no

I'll stop replying now -- you are getting identical advice
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-21 : 06:07:54

Thanks Mangal, but the above query brings forward all the completed & cancelled and completed & stage 8, for the practices...

The results is not correct I am expecting ONLY 3 records.

The results should be identical to the query further below

; WITH B AS
(SELECT TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start,
MAX(dbo.QryColProcessing.system_time) AS sys_time
FROM gprdsql.TblColProcessing INNER JOIN
dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage,dbo.QryColProcessing.system_time
HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
(gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
ORDER BY dbo.QryColProcessing.prac_no)
select b.*
from B inner join
(select prac_no, Max(col_uid) as Maxcol_uid
from b
group by prac_no, col_uid
) m
on b.prac_no = m.prac_no and b.col_uid = m.Maxcol_uid
inner join
(select prac_no,col_uid, Max(sys_time) as Maxsys_time
from b
group by prac_no, col_uid, sys_time
)y
on m.prac_no = y.prac_no and m.Maxcol_uid = y.col_uid and b.sys_time = y.Maxsys_time

However for the query below, I have the OVER sql construct or statement is not supported error when am running this query that's why am trying to do it in a different way

select prac_no,col_uid,audit_end,[status],stage,audit_start, sys_time
from
(
select
row_number() over (partition by prac_no order by sys_time desc) as rownum,
*
from
(
SELECT TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start, Max (gprdsql.TblColProcessing.system_time)as sys_time
FROM gprdsql.TblColProcessing INNER JOIN
dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
(gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
) as dt
) as dt2
where rownum = 1

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-21 : 07:06:20

Ok, I reverted the code as shown below similar to th eabove code but removed dbo.QryColProcessing.system_time from the group by.. as shown below

; WITH b
( prac_no
, col_uid
, audit_end
, status
, stage
, audit_start
, sys_time ) AS
(
SELECT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start,
Max(dbo.QryColProcessing.system_time)AS sys_time
FROM gprdsql.TblColProcessing INNER JOIN
dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
(gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
)
SELECT b.*
FROM b
INNER
JOIN ( SELECT prac_no
, MAX(col_uid) AS MAXcol_uid
FROM b
GROUP
BY prac_no
, col_uid ) m
ON b.prac_no = m.prac_no
AND b.col_uid = m.MAXcol_uid

INNER
JOIN ( SELECT prac_no
, col_uid
, MAX(sys_time) as MAXsys_time
FROM b
GROUP
BY prac_no
, col_uid
, sys_time ) y
ON m.prac_no = y.prac_no
AND m.MAXcol_uid = y.col_uid
AND b.sys_time = y.MAXsys_time

The result is;

prac_no col_uid audit_end status stage audit_start sys_time
323 21255 10809711 Completed Stage 8 10809712 2009-07-06 19:46:16.747
926 6768 7818384 Completed Stage 8 7818385 2009-02-05 23:52:39.697
21923 20465 7564365 Completed Cancelled 7564366 2009-03-23 12:39:54.950
21923 20745 7655776 Completed Stage 8 7655777 2009-07-07 13:03:52.283

but the correct answer should exclude;

21923 20465 7564365 Completed Cancelled 7564366 2009-03-23 12:39:54.950

this means the query doesnt accomodate Max col_uid and Max sys_time...

I am sorry guys but cant see the problem, the logic seems fine

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 07:55:36

Your having clause seems really suspect The OR isn't doing what I think you think it's doing (if that makes any sense):


HAVING
     (gprdsql.TblColProcessing.status = 'completed')
     AND (gprdsql.TblColProcessing.stage = 'cancelled')

     OR (gprdsql.TblColProcessing.status = 'completed')
     AND (gprdsql.TblColProcessing.stage = 'stage 8')

Should it maybe be this instead?


HAVING
     (
         (gprdsql.TblColProcessing.status = 'completed')
         AND (gprdsql.TblColProcessing.stage = 'cancelled')
     )
     OR (
         (gprdsql.TblColProcessing.status = 'completed')
         AND (gprdsql.TblColProcessing.stage = 'stage 8')
     )

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-21 : 08:05:03

Thanks Charlie.. The code I have below works now .. It gives me the right results, but when I try to save the query in a view I receive the following error;

Query Definition Differ
The following errors were encountered while parsing the contents of the SQL pane: Unable to parse the quert text.

The query cant be represented graphically in the Diagram and Criteria pane.

When you Ok, it prompts for the name of the view, when you enter for example Qrytest and ok. It gives you the following error :

'Incorrect Syntax near ';' when you OK - it prompts "User Canceled out of save dialogue" and it cancels the save process... How to sort this out? Thanks

; WITH b
( prac_no
, col_uid
, audit_end
, status
, stage
, audit_start
, sys_time ) AS
(SELECT dbo.QryColProcessing.prac_no
, MAX(dbo.QryColProcessing.col_uid) AS col_uid
, MAX(dbo.QryColProcessing.audit_end) AS audit_end
, gprdsql.TblColProcessing.status
, gprdsql.TblColProcessing.stage
, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start
, MAX(dbo.QryColProcessing.system_time) AS sys_time
FROM gprdsql.TblColProcessing
INNER
JOIN dbo.QryColProcessing
ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
WHERE gprdsql.TblColProcessing.status = 'completed'
AND gprdsql.TblColProcessing.stage IN ( 'cancelled', 'stage 8' )
GROUP
BY dbo.QryColProcessing.prac_no
, gprdsql.TblColProcessing.status
, gprdsql.TblColProcessing.stage
)
SELECT b.*
FROM b
INNER
JOIN ( SELECT prac_no
, MAX(col_uid) AS MAXcol_uid
FROM b
GROUP
BY prac_no) m
ON b.prac_no = m.prac_no
AND b.col_uid = m.MAXcol_uid
INNER
JOIN ( SELECT prac_no
, col_uid
, MAX(sys_time) as MAXsys_time
FROM b
GROUP
BY prac_no
, col_uid ) y
ON m.prac_no = y.prac_no
AND m.MAXcol_uid = y.col_uid
AND b.sys_time = y.MAXsys_time

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 08:11:12

You don't use the ; in a view.

Here's an example for something I wrote recently.



CREATE VIEW dbo.previous_ebr (
	[ebrId]
	, [prevEbrId]
	)
AS

	WITH ebrOrder (
		[employeeId]
		, [ebrId]
		, [status]
		, [startDate]
		, [actualOrder]
		, [logicalOrder]
		)
	AS (
		SELECT
			[employeeID]
			, [ebrId]
...
...
...
...

So you don't use the ;

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-21 : 09:35:12

Thank you very much Charlie - Sorted

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 10:18:45

You're welcome

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources