User Defined Function - In Line Table Valued

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 2000 Forums

Transact-SQL (2000)

User Defined Function - In Line Table Valued

Author

Topic

cobra429
Starting Member

4 Posts

Posted - 2005-07-13 : 11:02:34

I am trying to determine how you can select from an in-line table valued function by passing in the column names of tables also included in the select statement. For instance, if the function returned three columns (fromdate, todate and totalhours) and required the parameters StartDte and EndDte, could you select from a table containing the StartDte and EndDte where the contractID was some value?

Thanks
Mike

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-13 : 11:44:36

No -- only a scalar function can accept parameters from a table's columns. It sounds like you want to return 3 values from 1 UDF, in which case a single row table with 3 columns might sound ideal, but it doesn't really work that way with UDF's.

A join is most likely your best bet; how about some sample data and what you need to achieve and we'll see if we can help you out.

- Jeff

cobra429
Starting Member

4 Posts

Posted - 2005-07-13 : 13:27:56

Thanks. The function looks like this:

CREATE FUNCTION fnScaleFactor_TOD
(
@From_Dt DateTime,
@To_Dt DateTime,
@LsClass varchar(50),
@Ldc_No varchar(50),
@Usg_Total Decimal(21,9),
@Usg_OnPeak Decimal(21,9),
@Usg_OffPeak Decimal(21,9)

)
RETURNS @res_tbl TABLE (LsClass varchar(50) Not Null Primary Key,
Scale_Factor Decimal(21,9) not null,
OnPeak_Scale_Factor Decimal(21,9) null,
OffPeak_Scale_Factor Decimal(21,9) null)

AS

BEGIN

DECLARE @ScaleFactor Decimal(21,9),
@OnPeak_ScaleFactor Decimal(21,9),
@OffPeak_ScaleFactor Decimal(21,9)
Declare @LoadShape Table (
[DTE] [datetime] NULL ,
[SERV_CLASS_SUBTYPE_NO] [int] NULL ,
[SERVICE_CLASS_SUBTYPE] [varchar] (50) NULL ,
[HrCd] [int] Null,
[HrValue] [decimal](21, 9) NULL
)
/* Load The LoadShape Data into a Normalized Loadshape Table
*/

Insert Into @LoadShape
(DTE , SERV_CLASS_SUBTYPE_NO, SERVICE_CLASS_SUBTYPE, HrCd, HrValue)
Select DTE, SERV_CLASS_SUBTYPE_NO, SERVICE_CLASS_SUBTYPE, S1.seq,
CASE S1.seq
WHEN 1 THEN HR1
WHEN 2 THEN HR2
WHEN 3 THEN HR3
WHEN 4 THEN HR4
WHEN 5 THEN HR5
WHEN 6 THEN HR6
WHEN 7 THEN HR7
WHEN 8 THEN HR8
WHEN 9 THEN HR9
WHEN 10 THEN HR10
WHEN 11 THEN HR11
WHEN 12 THEN HR12
WHEN 13 THEN HR13
WHEN 14 THEN HR14
WHEN 15 THEN HR15
WHEN 16 THEN HR16
WHEN 17 THEN HR17
WHEN 18 THEN HR18
WHEN 19 THEN HR19
WHEN 20 THEN HR20
WHEN 21 THEN HR21
WHEN 22 THEN HR22
WHEN 23 THEN HR23
WHEN 24 THEN HR24
END HrValue
FROM A_LOADSHAPE_TB WITH (NOLOCK)
CROSS JOIN Sequence S1
WHERE SERVICE_CLASS_SUBTYPE = @LsClass AND DTE >= @From_dt AND DTE < @To_Dt

--
-- Get The Sum Of
-- The LoadShape Hourly Values For
-- The Cycle Dates That
-- Are Passed
--

If @Usg_Total IS NOT Null
Begin

SELECT @ScaleFactor = @Usg_Total/Sum(HrValue)
FROM @Loadshape
Where dte >= @From_dt And dte < @To_Dt
End

If (@Usg_OnPeak <> 0 or @Usg_OnPeak IS NOT NULL) AND ((@Ldc_No = 'RGE' or @Ldc_No = 'NYSEG')
and @LsClass IN (1150900, 1150710, 1150711, 1150720,
1150721, 1150730, 1150731, 1150740, 1150741))

Begin

SELECT @OnPeak_ScaleFactor = @Usg_OnPeak/Sum(CASE WHEN (@Ldc_No = 'NYSEG' or @Ldc_No = 'CER') AND HrCd between 7 and 22 THEN HrValue
WHEN @Ldc_No = 'RGE' AND HrCd between 7 and 23 THEN HrValue
END)
FROM @Loadshape
Where dte >= @From_dt And dte < @To_Dt
--AND Datepart(dw,DTE) between 1 and 5
AND ((@@DateFirst+DATEPART(DW,DTE)-2) % 7)+1 between 1 and 5

End
ELSE
IF @Usg_OnPeak = 0

Begin

Select @OnPeak_ScaleFactor = 0

End
ELSE

Begin

Select @OnPeak_ScaleFactor = NULL

END

If (@Usg_OffPeak <> 0 or @Usg_OffPeak IS NOT NULL) AND ((@Ldc_No = 'RGE' or @Ldc_No = 'NYSEG')
and @LsClass IN (1150900, 1150710, 1150711, 1150720,
1150721, 1150730, 1150731, 1150740, 1150741))
Begin

SELECT @OffPeak_ScaleFactor = @Usg_OffPeak/Sum(CASE WHEN @Ldc_No = 'RGE'
AND (((@@DateFirst+DATEPART(DW,DTE)-2) % 7)+1 between 1 and 5
AND (HrCd between 1 AND 6 or HrCd = 24)) THEN HrValue
WHEN (@Ldc_No = 'NYSEG' OR @Ldc_No = 'CER')
AND ((@@DateFirst+DATEPART(DW,DTE)-2) % 7)+ 1 between 1 and 5
AND (HrCd between 1 AND 6 or HrCd Between 23 and 24) THEN HrValue
WHEN ((@@DateFirst+DATEPART(DW,DTE)-2) % 7)+ 1 between 6 and 7 THEN HrValue
END)
FROM @Loadshape
Where dte >= @From_dt And dte < @To_Dt

End
ELSE
IF @Usg_OnPeak = 0

Begin

Select @OffPeak_ScaleFactor = 0

End
ELSE

Begin

Select @OffPeak_ScaleFactor = NULL

END

--Print @CyclePercent

If (@ScaleFactor < 0)

Begin
INSERT INTO @res_tbl ( LsClass,
Scale_Factor,
OnPeak_Scale_Factor,
OffPeak_Scale_Factor
)
select @LsClass, -1, NULL, NULL
Return
End

INSERT INTO @res_tbl ( LsClass,
Scale_Factor,
OnPeak_Scale_Factor,
OffPeak_Scale_Factor
)
select @LsClass,
@ScaleFactor,
@OnPeak_ScaleFactor,
@OffPeak_ScaleFactor
Return

END

The call to the function involves getting multiple rows out of a customer usage table that has data like:

FROM_DTTO_DT LS_CLASS LdcNo USG_TOTAL USG_ON_PEAK USG_OFF_PEAK

12/19/2001 01/22/2002 1150200 NYSEG 1440.0000 NULL NULL
11/16/2001 12/19/2001 1150200 NYSEG 1188.0000 NULL NULL
10/17/2001 11/16/2001 1150200 NYSEG 1116.0000 NULL NULL

cobra429
Starting Member

4 Posts

Posted - 2005-07-13 : 13:31:25

The call to the function involves getting multiple rows out of a customer usage table that has data like:
Sorry I submitted by accident while fixing the data:
FROM_DT TO_DT LS_CLASS LdcNo USG_TOTAL USG_ON_PEAK USG_OFF_PEAK

12/19/2001 01/22/2002 1150200 NYSEG 1440.0000 NULL NULL
11/16/2001 12/19/2001 1150200 NYSEG 1188.0000 NULL NULL
10/17/2001 11/16/2001 1150200 NYSEG 1116.0000 NULL NULL

The idea is that the data needs to be transformed using one of the 3 scale factors based upon internal business rules. Each time the function is called, it would be passed a minimum of 12 rows of data which will be transformed in a later step.

Thanks
Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources