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