set based query instead of variable table

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)

set based query instead of variable table

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2007-05-07 : 13:11:00

I don't want to use a variable table but I don't know how to do this without one.
Here is my code so far:

declare @obj char(4)
declare @pca varchar(5)
declare @budgetyear numeric
declare @cnt int

set @pca='17922'
set @budgetyear='2007'

Declare @t Table (tid int identity, obj varchar(4), objdesc varchar(60) )

insert into @t (obj, objdesc)

Select distinct expend_sub_obj, object_desc
From fin_exp_enc_fp_detail WITH (NOLOCK)
JOIN bud_stars_object WITH (NOLOCK) ON expend_sub_obj = object_code
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object= '4101'

union

Select distinct exp_sum_object, object_desc
From fin_exp_enc_fp_detail WITH (NOLOCK)
JOIN bud_stars_object WITH (NOLOCK) ON exp_sum_object = object_code
Where pca = @pca
And budget_year = @budgetyearwhile loop
AND exp_sum_object <> '4101'
Order by 1 desc

set @cnt=@@rowcount
while @cnt>0
BEGIN

select @obj=obj from @t where tid= @cnt

-----
IF substring(@obj,1,2)='41'

BEGIN
--starts with 41
Select isnull(sum(amount),0) * -1 as budget
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = '4101'
And expend_sub_obj = @obj
And gl_Account = '5070'
And budget_year=trans_year
END

ELSE

BEGIN
--not 41
Select isnull(sum(amount),0) * -1 as budget
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = @obj
And gl_Account = '5070'
And budget_year=trans_year
END

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-07 : 13:19:47

I dont understand why you need to separate objects '4101' from others? You are not doing anything specific to those anyway in your code..

your code of

 set @cnt=@@rowcount

will give you the total count from both the SELECT's (After the UNION). Then you are querying the table variable for the row WHERE Id = @cnt so basically you are retrieving the last record and depending on that you are showing either 4101 or the other records. so is your requirement to show only 4101 records if "at least" one record is of that type?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

forwheeler
Starting Member

44 Posts

Posted - 2007-05-07 : 13:34:56

Sorry, the query should be:

declare @obj char(4)
declare @pca varchar(5)
declare @budgetyear numeric
declare @cnt int

--set @sumobject='4105'
set @pca='17922'
set @budgetyear='2007'

Declare @t Table (tid int identity, obj varchar(4), objdesc varchar(60) )

insert into @t (obj, objdesc)

Select distinct expend_sub_obj, object_desc
From fin_exp_enc_fp_detail WITH (NOLOCK)
JOIN bud_stars_object WITH (NOLOCK) ON expend_sub_obj = object_code
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object= '4101'

union

Select distinct exp_sum_object, object_desc
From fin_exp_enc_fp_detail WITH (NOLOCK)
JOIN bud_stars_object WITH (NOLOCK) ON exp_sum_object = object_code
Where pca = @pca
And budget_year = @budgetyear
Order by 1 desc

set @cnt=@@rowcount
while @cnt>0
BEGIN

select @obj=obj from @t where tid= @cnt

-----
IF substring(@obj,1,2)='41'

BEGIN
--starts with 41
Select isnull(sum(amount),0) * -1 as budget
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = '4101'
And expend_sub_obj = @obj
And gl_Account = '5070'
And budget_year=trans_year
END

ELSE

BEGIN
--not 41
Select isnull(sum(amount),0) * -1 as budget
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = @obj
And gl_Account = '5070'
And budget_year=trans_year
END

I want to return the expend_sub_obj if exp_sum_object-'4101' other wise return the exp_sum_object.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-07 : 13:42:52

look into something like this:


IF EXISTS ( SELECT * FROM fin_exp_enc_fp_detail WITH (NOLOCK)
			JOIN bud_stars_object WITH (NOLOCK) ON expend_sub_obj = object_code 
			Where pca = @pca 
			And budget_year = @budgetyear
			And substring(exp_sum_object,1,2)='41')
	BEGIN
		Select isnull(sum(amount),0) * -1 as budget
		From fin_exp_enc_fp_detail WITH (NOLOCK)
		Where pca = @pca
		And budget_year = @budgetyear
		And exp_sum_object = '4101' 
		And gl_Account = '5070'
		And budget_year=trans_year
	END
ELSE
	BEGIN
		Select isnull(sum(amount),0) * -1 as budget
		From fin_exp_enc_fp_detail WITH (NOLOCK)
		Where pca = @pca
		And budget_year = @budgetyear
		And gl_Account = '5070'
	
	END

Your SELECTs are little confusing as you have a JOIN with a table in your first set of SELECTs but its not in the final SELECT.

Please post the table structure, some sample data and the expected output. perhaps there's an easier way to do what you are doing.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

forwheeler
Starting Member

44 Posts

Posted - 2007-05-07 : 14:08:13

Thanks for your help so far. This logic was originally in Java by someone else and I am moving it to SQL for reporting reasons so it may not make much sense.

What I need to do is get either the exp_sum_object or the expend_sub_obj (depending on whether exp_sum_object= 4101 )and then perform some queries on the resulting object number. The join in this first query is just to get the description of this object number.

Sample data in fin_exp_enc_fp_detail:

pca expend_sub_obj exp_sum_object
34810 6099 6001
34810 6230 6201
81802 6099 6001
81802 6160 6151
81804 6160 6151
19330 6099 6001

Here is the full query:
declare @obj char(4)
declare @pca varchar(5)
declare @budgetyear numeric
declare @cnt int

--set @obj='4105'
set @pca='17922'
set @budgetyear='2007'

Declare @t Table (tid int identity, obj varchar(4), objdesc varchar(60) )

insert into @t (obj, objdesc)

Select distinct expend_sub_obj, object_desc
From fin_exp_enc_fp_detail WITH (NOLOCK)
JOIN bud_stars_object WITH (NOLOCK) ON expend_sub_obj = object_code
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object= '4101'

union

Select distinct exp_sum_object, object_desc
From fin_exp_enc_fp_detail WITH (NOLOCK)
JOIN bud_stars_object WITH (NOLOCK) ON exp_sum_object = object_code
Where pca = @pca
And budget_year = @budgetyear
Order by 1 desc

set @cnt=@@rowcount
while @cnt>0
BEGIN

select @obj=obj from @t where tid= @cnt

-----
IF substring(@obj,1,2)='41'

BEGIN
--starts with 41
Select isnull(sum(amount),0) * -1 as budget
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = '4101'
And expend_sub_obj = @obj
And gl_Account = '5070'
And budget_year=trans_year

Select isnull(sum(amount),0) as ytdspent
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca =@pca
And budget_year = @budgetyear
And exp_sum_object = '4101'
And expend_sub_obj = @obj
And gl_Account = '4200'
And trans_month in ('01','02','03','04','05','06','07','08','09','10','11','12')

Select isnull(sum(amount),0) as ytdenc
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = '4101'
And expend_sub_obj = @obj
And gl_Account = '4300'
And budget_year=trans_year

Select isnull(sum(amount),0) as postytd
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = '4101'
And expend_sub_obj = @obj
And gl_Account between '4200' and '4300'
And budget_year < trans_year
END

ELSE

BEGIN
--not 41
Select isnull(sum(amount),0) * -1 as budget
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = @obj
And gl_Account = '5070'
And budget_year=trans_year

Select isnull(sum(amount),0) as ytdspent
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = @obj
And gl_Account = '4200'
And trans_month in ('01','02','03','04','05','06','07','08','09','10','11','12')

Select isnull(sum(amount),0) as ytdenc
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = '@obj'
And gl_Account = '4200'
And budget_year=trans_year

Select isnull(sum(amount),0) as postytd
From fin_exp_enc_fp_detail WITH (NOLOCK)
Where pca = @pca
And budget_year = @budgetyear
And exp_sum_object = @obj
And gl_Account between '4200' and '4300'
And budget_year < trans_year
END

set @cnt=@cnt-1
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources