Can Recursive CTEs solve my problem?

[bboffin]

I have a situation where I need to find a valid price for an object.

I have three tables:
Price with columns
PriceID int
ObjectID int
AccountID int
PriceDetail with columns
PriceDetailID int
PriceID int
ChargeRate float
Object with columns
ObjectID int
ParentObjectID int

The rules are:
Look up the ChargeRate for a given ObjectID and AccountID.
If the ChargeRate is NULL (i.e. there is no price for this Object and Account) then repeat the lookup using the ParentObjectID.
If the ParentObjectID IS NULL then it's the BaseObject and the lookup of the ChargeRate uses 0 as the AccountID.

Here's a scalar function that does the job:
[tt]
CREATE FUNCTION sufApplicablePrice
(
-- Add the parameters for the function here
@O INT,
@A INT
)
RETURNS float
AS
BEGIN
-- Declare the return variable here
DECLARE @Price float

-- Add the T-SQL statements to compute the return value here
SELECT @Price = PD.ChargeRate FROM PRICE P LEFT OUTER JOIN PRICEDETAIL PD ON P.PriceID = PD.PriceID
WHERE P.AccountID = @A AND P.ObjectID = @O

WHILE @Price IS NULL AND @O IS NOT NULL
BEGIN
SELECT @O = O.ParentObjectID FROM OBJECT O WHERE O.ObjectID = @O
IF @O IS NOT NULL
SELECT @Price = PD.ChargeRate FROM PRICE P LEFT OUTER JOIN PRICEDETAIL PD ON P.PriceID = PD.PriceID
WHERE P.AccountID = @A AND P.ObjectID = @O
ELSE
SELECT @Price = PD.ChargeRate FROM PRICE P LEFT OUTER JOIN PRICEDETAIL PD ON P.PriceID = PD.PriceID
WHERE P.AccountID = 0 AND P.ObjectID = @O
END
IF @Price IS NULL SET @Price = 0
-- Return the result of the function
RETURN @Price
END
[/tt]

I would like to know if there's any way I can avoid having to use a function. Recursive Common Table Expressions look promising but none of the examples seem to cover this type of case.



Bob Boffin

 

[Emtucifor]

PriceID int
ObjectID int
AccountID int
PriceDetail with columns
PriceDetailID int
PriceID int
ChargeRate float

First, the PriceDetail table seems to have an unnecessary PriceDetailID column in it. Unless there is more than one charge rate for a given PriceID? This doesn't make any sense given the queries you have.

Second, I would recommend renaming the confusing table names. "PriceDetail" sound like a child table when in fact it's the source price data table.

Price -> ObjectPrice
PriceDetail -> Price

Even reversing the two table names would make more sense to me. I realize you may not be allowed or able to make schema changes.

Third, you should NOT be using float to store money values. You should scream this one all the way to the top if you have to to get it changed to numeric. Float is an inexact data type which can yield different results depending on how it is used and/or stored (there can be multiple representations for the "same" value). In the float world, the 1 you stored may not equal the 1 you're trying to test it against later, since it might have been stored as 0.9999999999999999.

Last, why do you have to left join to PriceDetail each time? Is it that the PriceID in the Price table can refer to a PriceID that doesn't exist in PriceDetail, or can ChargeRate be NULL in PriceDetail? There's a problem in there somewhere.

If I have time later I'll try to help you with the query... have to run now.

Erik

 

[bboffin]

This is a greatly simplified version of the table schema which is, as you suspected, immutable as it's a 3rd party DB. They chose float for what, I agree, should really be a Money column.

The Price Detail table is separate and has its own primary ID as there may be multiple Price Detail records for a given price only one of which is current (there's a CurrentFlag bit column in the detail record which needs to be set to 1 to be included but I left this out for simplicity) hence the need for the left outer join as it's possible that no price detail records are marked as current.

All of which is pretty irrelevant as my problem is walking backwards through the Object table following the ParentObjectID if I find no Price Detail record (or indeed no Price record which is also possible)


Bob Boffin