June 26, 2012 at 9:42 am
Hello,
I wanted to know how to use scalar valued function in select statement. Right now I have a select statement like this which pulls data from Pipeline summary master table.Apart from these columns I also wanted to include diameter column which is calculted using a function called get_predominantDiameter.Here I am trying to get the diameter for each line_guid.So, I want to use this function get_predominantDiameter in the select statement to get the diameter for each line_guid.
Here is the select statement in which I want to include the function to get the diameter for each record.
SELECT PS.LineGUID AS LINE_GUID,
PS.PODSID,
PS.SystemName,
PS.LineNumber,
PS.LineName,
PS.RegulatoryTypes,
PS.OperatingStatus,
PS.Jurisdiction,
PS.Mileage,
PS.Operator,
PS.ProductTypes,
--DerivedAsset.dbo.Get_PredominantDiameter(PS.LineGUID) as PredominantDiameter
FROM DerivedAsset.dbo.PipelineSummaryMaster PS
The above select statement is not working. I am getting error as
An insufficient number of arguments were supplied for the procedure or function DerivedAsset.dbo.Get_PredominantDiameter.
I have a function that will calculate the diameter for each line_guid.
This is my function
ALTER FUNCTION [dbo].[Get_PredominantDiameter](@LINE_GUID AS UNIQUEIDENTIFIER)
RETURNS NUMERIC(8,4)
AS
BEGIN
DECLARE @DIA NUMERIC(8,4), @mileage numeric(12,3)
select top 1 @dia = Diameter, @mileage =
SUM(abs(begin_measure - end_measure))/5280
FROM DERIVEDASSET.DBO.EU_DIAMETER EU
where EU.line_GUid = @LINE_GUID
group by Diameter
ORDER BY SUM(abs(begin_measure - end_measure))/5280 DESC
RETURN @DIA
END
Now I want to use this function in my above select statement and pass the parameter Line_guid from Pipeline summary table to get the diameter for each line_guid.
Please help.
Thank You
June 26, 2012 at 10:05 am
Not sure why you got the error message you did, the function only has one parameter and you are passing it one parameter.
The following, however, is how I would do it:
DROP FUNCTION [dbo].[Get_PredominantDiameter];
GO
ALTER FUNCTION [dbo].[Get_PredominantDiameter](
@LINE_GUID AS UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN(
select top 1
cast(Diameter as numeric(8,4)) PredominantDiameter
FROM
DERIVEDASSET.DBO.EU_DIAMETER EU
where
EU.line_GUid = @LINE_GUID
group by
Diameter
ORDER BY
SUM(abs(begin_measure - end_measure))/5280 DESC
);
GO
SELECT
PS.LineGUID AS LINE_GUID,
PS.PODSID,
PS.SystemName,
PS.LineNumber,
PS.LineName,
PS.RegulatoryTypes,
PS.OperatingStatus,
PS.Jurisdiction,
PS.Mileage,
PS.Operator,
PS.ProductTypes,
PD.PredominantDiameter
FROM
DerivedAsset.dbo.PipelineSummaryMaster PS
CROSS APPLY DerivedAsset.dbo.Get_PredominantDiameter(PS.LineGUID) PD;
GO
June 26, 2012 at 10:16 am
Here is another way that does your function logic inline.
SELECT PS.LineGUID AS LINE_GUID
,PS.PODSID
,PS.SystemName
,PS.LineNumber
,PS.LineName
,PS.RegulatoryTypes
,PS.OperatingStatus
,PS.Jurisdiction
,PS.Mileage
,PS.Operator
,PS.ProductTypes
,
--DerivedAsset.dbo.Get_PredominantDiameter(PS.LineGUID) as PredominantDiameter
d.Diameter
FROM DerivedAsset.dbo.PipelineSummaryMaster PS
CROSS APPLY (
SELECT TOP 1 Diameter
FROM DERIVEDASSET.DBO.EU_DIAMETER EU
WHERE EU.line_GUid = PS.LineGuid
GROUP BY Diameter
ORDER BY SUM(abs(begin_measure - end_measure)) / 5280 DESC
) d
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2012 at 10:18 am
Yeah Thats weird. Thank You so much. I followed your code and I got it. Thank You
June 26, 2012 at 10:20 am
and here's another way just using a sub select instead of the function:
note to self: That cross apply stuff is kewl, need to use it more.
SELECT PS.LineGUID AS LINE_GUID,
PS.PODSID,
PS.SystemName,
PS.LineNumber,
PS.LineName,
PS.RegulatoryTypes,
PS.OperatingStatus,
PS.Jurisdiction,
PS.Mileage,
PS.Operator,
PS.ProductTypes,
DerivedAsset.dia as PredominantDiameter
FROM DerivedAsset.dbo.PipelineSummaryMaster PS
LEFT OUTER JOIN (
SELECT dia,mileage
FROM (
SELECT
line_GUid,
row_number() over (partition by Diameter order by (SUM(ABS(begin_measure - end_measure)) / 5280) DESC ) AS RW,
dia = Diameter,
mileage = SUM(ABS(begin_measure - end_measure)) / 5280
FROM DERIVEDASSET.DBO.EU_DIAMETER EU
GROUP BY Diameter
) myAlias
WHERE myAlias.RW = 1
) DerivedAsset
ON DerivedAsset.line_GUid = PS.LineGUID
Lowell
June 26, 2012 at 10:22 am
--note about apply above--
If you wanted it to function more like a left join to either the function or the inline you could change the cross apply to an outer apply and the diameter column would be NULL when there is no match.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2012 at 10:24 am
The beauty of the community!
June 26, 2012 at 10:24 am
Here is an example based off the OP's criteria.
declare @GuidToFind uniqueidentifier = newid()
;with Derived (Line_GUID, Diameter, begin_measure, end_measure)
as
(
select @GuidToFind, 5, 108394, 24819570 union all
select @GuidToFind, 10, 45839290, 509589373
),
Guids(SomeValue, MyGuid)
as
(
select 1, @GuidToFind union all
select 2, NEWID() union all
select 3, NEWID() union all
select 4, NEWID()
)
select d.*, x.Diameter
from Guids d
outer apply
(
select top 1 Diameter
from Derived
where Line_GUID = d.MyGuid
group by Diameter
order by SUM((begin_measure - end_measure)/5280)
) x
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2012 at 2:51 pm
Thank you so much for your replies. I really appreciate it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply