September 10, 2009 at 6:26 am
I have a table definition such as:
Select
null as 'Amount'
,1 as 'Priority'
UNION
Select
10.50 as 'Amount'
,2 as 'Priority'
UNION
Select
100 as 'Amount'
,3 as 'Priority'
I want the amount for the item with the highest priority that is not null or 0.
Thus. I created a temp table, and insert the data.
Create Table #PriorityAmount(Amount decimal(18,2), priority int)
Insert Into #PriorityAmount
Select
null as 'Amount'
,1 as 'Priority'
UNION
Select
10.50 as 'Amount'
,2 as 'Priority'
UNION
Select
100 as 'Amount'
,3 as 'Priority'
Good, Now I can Select the data as I need It....
Select
Top 1 Amount
from
#PriorityAmount
Where
isnull(Amount,0) > 0
Order By
Priority Asc
Drop Table #PriorityAmount
Sorted! This returns a value of 10.50, which for this sample is exactly correct
So Convert that to a function, I get the error Cannot use temp table bloah blah blah.
Hit head against monitor a couple of time and revert to Table Variables
Thus Converted the code to:
DECLARE @PriorityAmount table (
Amount Decimal(18,2)
,Priotity int)
Insert Into @PriorityAmount
Select
null as 'Amount'
,1 as 'Priority'
UNION
Select
10.50 as 'Amount'
,2 as 'Priority'
UNION
Select
100 as 'Amount'
,3 as 'Priority'
Select
Top 1 Amount
from
@PriorityAmount
Where
isnull(Amount,0) > 0
Order By
Priority Asc
Got the Error:
Msg 207, Level 16, State 3, Line 21
Invalid column name 'Priority'.
The code runs fine if i leave out the order by clause.
In this sample it returns the correct vallue, but the correct value is not garunteed.
How can I make the @TableVar option work?
September 10, 2009 at 6:39 am
Would renaming Priotity to Priority fix the issue?
DECLARE @PriorityAmount table (
Amount Decimal(18,2)
,Priotity int) -- shouldn't be Priority??
-- Gianluca Sartori
September 10, 2009 at 6:46 am
Gianluca Sartori (9/10/2009)
Would renaming Priotity to Priority fix the issue?
DECLARE @PriorityAmount table (
Amount Decimal(18,2)
,Priotity int) -- shouldn't be Priority??
DOH!
Do I feel like like an idiot or do I feel like an IDIOT
Damn!
:blush:
:Whistling:
Thanks. It's working now...
September 10, 2009 at 6:50 am
Don't worry: I lost hours and hours in the past because of typos.... and I still do!:-D
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy