Remove function from Select Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Remove function from Select Statement

Author

Topic

S_Lakshmi
Starting Member

22 Posts

Posted - 2008-10-02 : 11:53:48

Dear friends,

I wanted to remove the function from the select statement. Here is the script for SP and Function. Could any please help me to solve this issue

Thanks
Lakshmi


-- Store Procedure

DECLARE @Micrometer_To_Inches AS REAL
DECLARE @Index AS TINYINT, @Coil_Id as varchar(12)
DECLARE @Pass_Num AS TINYINT
SET @Index = CHARINDEX('.', @Coil_Id)

SET @Micrometer_To_Inches = 25400.0
DECLARE @Ret_Value int
SET @Ret_Value=null



SELECT 
  Thickness.point_num AS Point_Num,


  CAST(MAX(Thickness.measurement)/@Micrometer_To_Inches AS DECIMAL(9,5)) AS Max_Gauge,

cast( avg((Select top 1 Ret_Value=Porf.measurement
FROM Pickle_Profiles AS Porf
INNER JOIN Pickle_Length_Info AS Plength
   ON Plength.msg_num = Porf.msg_num
              INNER JOIN Pickle_Alias AS PAlias
         ON PAlias.msg_num = Plength.msg_num
Where Lengthtype='0' and
Plength.msg_num =Thickness.msg_num and
plenght.point_num = Thickness.point_num)/ @Micrometer_To_Inches AS DECIMAL(9,5)) as avg_gauge,

/***********************************************************
This is the part i want to change -- Begins
***********************************************************/
  CAST(
    AVG(fnPl_Get_Avg_Gauge(Thickness.msg_num, Thickness.point_num, Thickness.measurement))

/***********************************************************
This is the part i want to change -- Ends
***********************************************************/

/ @Micrometer_To_Inches AS DECIMAL(9,5)) AS Avg_Gauge,
  CAST(STDEV(Thickness.measurement)/@Micrometer_To_Inches AS DECIMAL(9,5)) AS StdDev_Gauge

FROM Pickle_Profiles AS Thickness
  INNER JOIN Pickle_Length_Info AS Length
    ON Length.msg_num = Thickness.msg_num
      AND Length.pass_num = @Pass_Num
         INNER JOIN Pickle_Alias AS Alias
          ON Alias.msg_num = Length.msg_num
WHERE Alias.cid = SUBSTRING(@Coil_Id, 0, @Index) + '.000'
GROUP BY Thickness.point_num
ORDER BY Thickness.point_num


/********************************************
Function Script (fnPl_Get_Avg_Gauge)
*********************************************/

(
 @Msg_Num INT, 
 @Point INT, 
 @Thickness INT
)
RETURNS INT
AS
BEGIN
 DECLARE @Msg_Num INT 
 DECLARE @Point INT 
 DECLARE @Thickness INT
 DECLARE @Ret_Val INT
 SET @Ret_Val = null
SELECT TOP 1
Thickness.measurement
 FROM
 IMSWarehouse.dbo.Pickle_Profiles AS Thickness
   INNER JOIN dbo.Pickle_Length_Info AS Length
     ON Length.msg_num = Prof.msg_num
WHERE
 Thickness.msg_num = @Msg_Num and
 Thickness.point_num = @Point and
 Length.type = '0'  
 RETURN @Ret_Val
END

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-02 : 12:06:40

quote:

Originally posted by S_Lakshmi

Dear friends,

I wanted to remove the function from the select statement. Here is the script for SP and Function. Could any please help me to solve this issue

Thanks
Lakshmi


-- Store Procedure

DECLARE @Micrometer_To_Inches AS REAL
DECLARE @Index AS TINYINT, @Coil_Id as varchar(12)
DECLARE @Pass_Num AS TINYINT
SET @Index = CHARINDEX('.', @Coil_Id)

SET @Micrometer_To_Inches = 25400.0
DECLARE @Ret_Value int
SET @Ret_Value=null



SELECT 
  Thickness.point_num AS Point_Num,


  CAST(MAX(Thickness.measurement)/@Micrometer_To_Inches AS DECIMAL(9,5)) AS Max_Gauge,

cast( avg((Select top 1 Ret_Value=Porf.measurement
FROM Pickle_Profiles AS Porf
INNER JOIN Pickle_Length_Info AS Plength
   ON Plength.msg_num = Porf.msg_num
              INNER JOIN Pickle_Alias AS PAlias
         ON PAlias.msg_num = Plength.msg_num
Where Lengthtype='0' and
Plength.msg_num =Thickness.msg_num and
plenght.point_num = Thickness.point_num)/ @Micrometer_To_Inches AS DECIMAL(9,5)) as avg_gauge,

/***********************************************************
This is the part i want to change -- Begins
***********************************************************/
  CAST(
    AVG(fnPl_Get_Avg_Gauge(Thickness.msg_num, Thickness.point_num, Thickness.measurement))

/***********************************************************
This is the part i want to change -- Ends
***********************************************************/

/ @Micrometer_To_Inches AS DECIMAL(9,5)) AS Avg_Gauge,
/***********************************************************
Try end it here
***********************************************************/

  CAST(STDEV(Thickness.measurement)/@Micrometer_To_Inches AS DECIMAL(9,5)) AS StdDev_Gauge

FROM Pickle_Profiles AS Thickness
  INNER JOIN Pickle_Length_Info AS Length
    ON Length.msg_num = Thickness.msg_num
      AND Length.pass_num = @Pass_Num
         INNER JOIN Pickle_Alias AS Alias
          ON Alias.msg_num = Length.msg_num
WHERE Alias.cid = SUBSTRING(@Coil_Id, 0, @Index) + '.000'
GROUP BY Thickness.point_num
ORDER BY Thickness.point_num


/********************************************
Function Script (fnPl_Get_Avg_Gauge)
*********************************************/

(
 @Msg_Num INT, 
 @Point INT, 
 @Thickness INT
)
RETURNS INT
AS
BEGIN
 DECLARE @Msg_Num INT 
 DECLARE @Point INT 
 DECLARE @Thickness INT
 DECLARE @Ret_Val INT
 SET @Ret_Val = null
SELECT TOP 1
Thickness.measurement
 FROM
 IMSWarehouse.dbo.Pickle_Profiles AS Thickness
   INNER JOIN dbo.Pickle_Length_Info AS Length
     ON Length.msg_num = Prof.msg_num
WHERE
 Thickness.msg_num = @Msg_Num and
 Thickness.point_num = @Point and
 Length.type = '0'  
 RETURN @Ret_Val
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 04:21:27

whats the purpose of the function? as of noew it just returns a NULL value for Ret_Val always. do you meant this?

(
 @Msg_Num INT, 
 @Point INT, 
 @Thickness INT
)
RETURNS INT
AS
BEGIN
 DECLARE @Msg_Num INT 
 DECLARE @Point INT 
 DECLARE @Thickness INT
 DECLARE @Ret_Val INT
 SET @Ret_Val = null
SELECT TOP 1 @Ret_Val =Thickness.measurement
 FROM
 IMSWarehouse.dbo.Pickle_Profiles AS Thickness
   INNER JOIN dbo.Pickle_Length_Info AS Length
     ON Length.msg_num = Prof.msg_num
WHERE
 Thickness.msg_num = @Msg_Num and
 Thickness.point_num = @Point and
 Length.type = '0'  
 RETURN @Ret_Val
END

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-03 : 08:37:12

just write the select in the function as an inline view and outer join to it on msg_num and point_num.
To get the TOP 1
a) Make sure you really need it and your data won't give you dupes (i.e. unique constraint on msg_num & point_num)
b) If they do give dupes then you have to use min/max or row_num() partition by ....

What you have does not give you deterministic results.

Bloody TOP .... It's a stupid function and should not be allowed without order by.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources