Problem referencing a field - 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 2000 Forums

Transact-SQL (2000)

Problem referencing a field

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-09 : 13:00:13

Hi,

I have a few lines of code where I am trying to fill the variable @ROI based
on condition of a field.
DECLARE @ROI varchar(30)
If (dbo_PWOPayback.AnnualVolumePer1000 = 0)
set @ROI = 0

Thus @ROI should give output based on the field. If there are ten matches
ROI should output ten values.

However I am getting the following error when I am trying to run the above code from QA

The column prefix 'dbo_PWOPayback' does not match with a table name or alias
name used in the query.
Any help is appreciated. Physically there is dbo_PWOPayback in the system.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-09 : 13:07:54

@ROI can only hold one value at a time, perhaps you need a table-valued function? Explain more on what you want to accomplish.
Jim
DECLARE @ROI varchar(30)

set @ROI = dbo_PWOPayback.AnnualVolumePer1000
from dbo_PWOPayback
where "this being true of dbo_PWOPayback means only one value returned"

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-09 : 13:34:22

OK
I have a table named dbo_PWOPayback. This has few fields and aslo field AnnualVolumePer1000.
The values of the AnnualVolumePer1000 is as follows
100
200
0
150
0
0
50
100
0

NOW I need to calculate ROI based on the condition if (dbo_PWOPayback.AnnualVolumePer1000 = 0) in the above table
then ROI should have a value of 0 else it should have a value of 1. Thus I am looking for a value of ROI
out of the calculation as
ROI
1
1
0
1
0
0
1
1
0. Hope I could explain what I intend to do. Thanks for your help.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-09 : 13:43:44

set @ROI = CASE WHEN ISNULL(dbo_PWOPayback.AnnualVolumePer1000,0) > THEN 1 ELSE 0 END
from dbo_PWOPayback

But you still have to get just one value at a time or you'll get an error. If ROI is a field in the table then
UPDATE dbo_PWOPayback
ROI = CASE WHEN ISNULL(dbo_PWOPayback.AnnualVolumePer1000,0) > 0 THEN 1 ELSE 0 END

I'm still not completely clear what you're trying to accomplish

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-09 : 14:54:10

jimf
I need to create a stored procedure and get the value of ROI field based on a few calculations. I guess the best approach is to create a temporary table, adding a ROI field to the #tmp_dbo_PWOPayback table and update it in accodance with your approach. Hope I could explain. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources