The Diff between Query1 and Query2 ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

The Diff between Query1 and Query2 ?

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-07-20 : 05:29:30

Hi

I've got a query that suppossed to work but it just can't seem to be working so I modified and it seems to be working fine and I just wondering if you guys could tell me why it's not working

Declare @Enterprise_nbr Char (12)	,
	@Survey_Code VarChar(30)	,	
	@Reo_Code char(5)	,
	@User varchar(50)	,
	@Turnover numeric

Set @User = 'PlayBoy'
Set @Reo_Code = 0

Declare Insert_EN Cursor read_only for

Select A.Enterprise_nbr, S.turnover
from SQL.Agriculture.dbo.Adjusted as A
inner join SQL.Agriculture.koketsom.sample as S
on A.Enterprise_nbr = S.Enterprisenbr
where A.Maint_date is null

Open Insert_EN 

Fetch Next from Insert_EN Into
			@Enterprise_nbr	, 
			@Turnover

While @@Fetch_Status=0
if @Turnover >= 300000
	set @Survey_Code = 'Agri2002'
Else
	Set @Survey_Code = 'Agri2003'

      	begin

		Insert into history_reo (Survey_Code,Enterprise_nbr, Reo_Code, Maint_User_Code ) 
				Values 	(@Survey_Code, @Enterprise_nbr, @Reo_Code , @User )
		Fetch Next from  Insert_EN  into 
					@Enterprise_nbr, 
					@Turnover,
                                        @Survey_Code
	End
Close Insert_EN
Deallocate Insert_EN

The code above ISN'T working so I modified it to this:

Declare @Enterprise_nbr Char (12)	,
	@Survey_Code VarChar(30)	,	
	@Reo_Code char(5)	,
	@User varchar(50)	,
	@Turnover numeric

Set @User = 'PlayBoy'
Set @Reo_Code = 0

Declare Insert_EN Cursor read_only for

Select A.Enterprise_nbr, S.turnover,'AGRI2003' as survey_code
from SQL.Agriculture.dbo.Adjusted as A
inner join SQL.Agriculture.koketsom.sample as S
on A.Enterprise_nbr = S.Enterprisenbr
where A.Maint_date is null
and s.turnover >= 300000
union
Select A.Enterprise_nbr, S.turnover,'AGRI2002' as survey_code
from SQL.Agriculture.dbo.Adjusted as A
inner join SQL.Agriculture.koketsom.sample as S
on A.Enterprise_nbr = S.Enterprisenbr
where A.Maint_date is null
and s.turnover < 300000
order by A.Enterprise_nbr

Open Insert_EN 

Fetch Next from Insert_EN Into
			@Enterprise_nbr	, 
			@Turnover,
                        @Survey_Code

While @@Fetch_Status=0

      begin

		Insert into history_reo (Survey_Code,Enterprise_nbr, Reo_Code, Maint_User_Code ) 
				Values 	(@Survey_Code, @Enterprise_nbr, @Reo_Code , @User )
		Fetch Next from  Insert_EN  into 
					@Enterprise_nbr, 
					@Turnover,
                                        @Survey_Code
	End
Close Insert_EN
Deallocate Insert_EN

Can somebody tell me why the first query isn't working. I let it run for about 25 minutes and it hadn't returned anything.

You can't teach an old mouse new clicks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-20 : 05:42:02

you have misplaced "Fetch Next from Insert_EN into
@Enterprise_nbr,
@Turnover,
@Survey_Code
"
in the 1st query.....it is inside one of the IF conditions....

HOWEVER....DROP THE USE OF CURSORS from ALL (99.99%) OF YOUR CODE....the performance will be sh*te....compared to the alternative (SET-BASED processing)
your 2nd query can be rewritten (to execute way faster)...as

Insert into history_reo (Survey_Code,Enterprise_nbr, Reo_Code, Maint_User_Code )
Select A.Enterprise_nbr, S.turnover,'AGRI2003' as survey_code
from SQL.Agriculture.dbo.Adjusted as A
inner join SQL.Agriculture.koketsom.sample as S
on A.Enterprise_nbr = S.Enterprisenbr
where A.Maint_date is null
and s.turnover >= 300000
union
Select A.Enterprise_nbr, S.turnover,'AGRI2002' as survey_code
from SQL.Agriculture.dbo.Adjusted as A
inner join SQL.Agriculture.koketsom.sample as S
on A.Enterprise_nbr = S.Enterprisenbr
where A.Maint_date is null
and s.turnover < 300000
order by A.Enterprise_nbr

This may turn up a syntax error (for you to iron out).....but the principle/style should work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources