JAMES
asked on
Can I have a conditional statement in an Inline UDF
I have the following inline UDF :-
ALTER FUNCTION dbo.OptionPremiums (@PriceMove float = 0, @Style text)
RETURNS TABLE
AS
RETURN ( SELECT dbo.Options.Contract, dbo.Options.Currency, dbo.Options.Exchange,
dbo.Options.Style, dbo.Options.StrikePrice, dbo.Options.ExpiryDate,
dbo.Options.PC, dbo.Options.UnderlyingPric
dbo.Options.InterestRate, dbo.Options.ValueDate, dbo.Options.DeltaConventio
(dbo.RealTimePrices.Price * 1000) + @PriceMove AS Price,
dbo.EuropeanPremium('20040
(dbo.RealTimePrices.Price * 1000) + @PriceMove, 0.25, 0.03, '', '', 365) AS Premium
FROM dbo.Options CROSS JOIN dbo.RealTimePrices)
....but I would like to return a different TABLE depending on a the value of @Style (ie execute a different statement). I have tried the usual IF statement as would be used in a scaler function but it doesnt seem to like it.
Any thoughts?
Thanks.
James.
ALTER FUNCTION dbo.OptionPremiums (@PriceMove float = 0, @Style text)
RETURNS TABLE
AS
RETURN ( SELECT dbo.Options.Contract, dbo.Options.Currency, dbo.Options.Exchange,
dbo.Options.Style, dbo.Options.StrikePrice, dbo.Options.ExpiryDate,
dbo.Options.PC, dbo.Options.UnderlyingPric
dbo.Options.InterestRate, dbo.Options.ValueDate, dbo.Options.DeltaConventio
(dbo.RealTimePrices.Price * 1000) + @PriceMove AS Price,
dbo.EuropeanPremium('20040
(dbo.RealTimePrices.Price * 1000) + @PriceMove, 0.25, 0.03, '', '', 365) AS Premium
FROM dbo.Options CROSS JOIN dbo.RealTimePrices)
....but I would like to return a different TABLE depending on a the value of @Style (ie execute a different statement). I have tried the usual IF statement as would be used in a scaler function but it doesnt seem to like it.
Any thoughts?
Thanks.
James.
e.g.:...
ALTER FUNCTION dbo.OptionPremiums (@PriceMove float = 0, @Style text)
RETURNS TABLE
AS
RETURN ( SELECT dbo.Options.Contract, dbo.Options.Currency, dbo.Options.Exchange,
dbo.Options.Style, dbo.Options.StrikePrice, dbo.Options.ExpiryDate,
dbo.Options.PC, dbo.Options.UnderlyingPric
dbo.Options.InterestRate, dbo.Options.ValueDate, dbo.Options.DeltaConventio
(dbo.RealTimePrices.Price * 1000) + @PriceMove AS Price,
dbo.EuropeanPremium('20040
(dbo.RealTimePrices.Price * 1000) + @PriceMove, 0.25, 0.03, '', '', 365) AS Premium
FROM dbo.Options CROSS JOIN dbo.RealTimePrices
WHERE @Style = 'Option1'
UNION ALL
SELECT dbo.Options2.Contract, dbo.Options2.Currency, dbo.Options2.Exchange,
dbo.Options2.Style, dbo.Options2.StrikePrice, dbo.Options2.ExpiryDate,
dbo.Options2.PC, dbo.Options2.UnderlyingPri
dbo.Options2.InterestRate,
(dbo.RealTimePrices2.Price
dbo.EuropeanPremium('20040
(dbo.RealTimePrices2.Price
FROM dbo.Options2 CROSS JOIN dbo.RealTimePrices2
WHERE @Style = 'Option2'
)
ALTER FUNCTION dbo.OptionPremiums (@PriceMove float = 0, @Style text)
RETURNS TABLE
AS
RETURN ( SELECT dbo.Options.Contract, dbo.Options.Currency, dbo.Options.Exchange,
dbo.Options.Style, dbo.Options.StrikePrice, dbo.Options.ExpiryDate,
dbo.Options.PC, dbo.Options.UnderlyingPric
dbo.Options.InterestRate, dbo.Options.ValueDate, dbo.Options.DeltaConventio
(dbo.RealTimePrices.Price * 1000) + @PriceMove AS Price,
dbo.EuropeanPremium('20040
(dbo.RealTimePrices.Price * 1000) + @PriceMove, 0.25, 0.03, '', '', 365) AS Premium
FROM dbo.Options CROSS JOIN dbo.RealTimePrices
WHERE @Style = 'Option1'
UNION ALL
SELECT dbo.Options2.Contract, dbo.Options2.Currency, dbo.Options2.Exchange,
dbo.Options2.Style, dbo.Options2.StrikePrice, dbo.Options2.ExpiryDate,
dbo.Options2.PC, dbo.Options2.UnderlyingPri
dbo.Options2.InterestRate,
(dbo.RealTimePrices2.Price
dbo.EuropeanPremium('20040
(dbo.RealTimePrices2.Price
FROM dbo.Options2 CROSS JOIN dbo.RealTimePrices2
WHERE @Style = 'Option2'
)
ASKER
Neat Idea - I will give it a try and report back.
Thanks.
Thanks.
ASKER
Works like a dream except I had to alter the lines
WHERE @Style = 'Option1'
WHERE @Style = 'Option2'
to
WHERE @Style LIKE 'Option2'
WHERE @Style LIKE 'Option1'
Does that seem the correct thing to do?
Thanks.
James.
WHERE @Style = 'Option1'
WHERE @Style = 'Option2'
to
WHERE @Style LIKE 'Option2'
WHERE @Style LIKE 'Option1'
Does that seem the correct thing to do?
Thanks.
James.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It wouldnt even let me save the function with "=" I had to specify "LIKE".
Its working, thats the important bit ;-)
Its working, thats the important bit ;-)
Unless you incorporate both tables into the query, such as with UNION, then remove the unrequired table using a WHERE clause which will always return false.
bukko