如何调用用户定义的函数表并创建视图? [英] How to call a user defined function table and create a view ?
问题描述
我的功能如下 -
'p> CREATE FUNCTION [DBO] [GetMinCalendarClaimRecord](
          @ aInsurer   &NBSP ;   TFieldValue,
          @ aProductCode     TFieldValue,
          @ aClaimNumber     TFieldValue,
     &NBSP ;    @aRecordSourceGroupID TGroupID,
          @ aRecordSourceGMID   TGMID的
  ; )退货表
              AS
返回 < b $ b 选择
&NBSP; &NBSP; RECORD_BATCH_GRP_ID,RECORD_BATCH_GM_ID,RECORD_SOURCE_GRP_ID,RECORD_SOURCE_GM_ID,保险公司PRODUCT_CODE,CLAIM_NUMBER,CLAIM_DATE_RECORDED,RB_CALENDAR_PERIOD
&NBSP; &NBSP;来自
&NBSP; &NBSP; &NBSP; DWUQV_CLAIM CI
&NBSP;在哪里
CI.INSURER &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; = @aInsurer AND
&NBSP; CI.PRODUCT_CODE&NBSP; &NBSP; &NBSP; &NBSP; = @aProductCode AND
&NBSP; CI.CLAIM_NUMBER&NBSP; &NBSP; &NBSP; &NBSP; = @aClaimNumber和
&NBSP; CI.RECORD_SOURCE_GRP_ID = @aRecordSourceGroupID AND
&NBSP; CI.RECORD_SOURCE_GM_ID&NBSP; = @aRecordSourceGMID AND
CI.RB_CALENDAR_PERIOD = (SELECT MIN(C.RB_CALENDAR_PERIOD)
来自DWUQV_CLAIM C
WHERE
;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; C.INSURER&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; = @aInsurer AND
&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; C.PRODUCT_CODE&NBSP;&NBSP;&NBSP;&NBSP; = @aProductCode AND
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; C.CLAIM_NUMBER&NBSP;&NBSP;&NBSP;&NBSP; = @aClaimNumber和
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; C.RECORD_SOURCE_GRP_ID = @ aRecordSourceGroupID AND
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; C.RECORD_SOURCE_GM_ID&NBSP; = @aRecordSourceGMID
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; )
$
GO
它返回一个表,如何通过调用上面的函数创建一个视图并从中传递参数查看'DWUQV_CLAIM'
谢谢,
Suvechha
一种方法是使用CROSS APPLY来调用表值函数从视图返回的每一行。例如:
SELECT *
FROM dbo.DWUQV_CLAIM AS claim
CROSS适用dbo.GetMinCalendarClaimRecord(
claim.Insurer,
claim.ProductCode,
claim.ClaimNumber,
claim.RecordSourceGroupID,
claim.RecordSourceGMID
)作为min_claim_record;
如果需要,可以将此查询封装在视图中。请注意,我仅使用SELECT *作为示例;你应该指定一个你需要的列名的明确列表。
Hi ,
I have a function like below-
CREATE FUNCTION [dbo].[GetMinCalendarClaimRecord](
@aInsurer TFieldValue,
@aProductCode TFieldValue,
@aClaimNumber TFieldValue,
@aRecordSourceGroupID TGroupID,
@aRecordSourceGMID TGMID
) RETURNS Table
AS
RETURN
SELECT
RECORD_BATCH_GRP_ID,RECORD_BATCH_GM_ID,RECORD_SOURCE_GRP_ID,RECORD_SOURCE_GM_ID,INSURER,PRODUCT_CODE,CLAIM_NUMBER,CLAIM_DATE_RECORDED,RB_CALENDAR_PERIOD
FROM
DWUQV_CLAIM CI
WHERE
CI.INSURER = @aInsurer AND
CI.PRODUCT_CODE = @aProductCode AND
CI.CLAIM_NUMBER = @aClaimNumber AND
CI.RECORD_SOURCE_GRP_ID = @aRecordSourceGroupID AND
CI.RECORD_SOURCE_GM_ID = @aRecordSourceGMID AND
CI.RB_CALENDAR_PERIOD = (SELECT MIN(C.RB_CALENDAR_PERIOD)
FROM DWUQV_CLAIM C
WHERE
C.INSURER = @aInsurer AND
C.PRODUCT_CODE = @aProductCode AND
C.CLAIM_NUMBER = @aClaimNumber AND
C.RECORD_SOURCE_GRP_ID = @aRecordSourceGroupID AND
C.RECORD_SOURCE_GM_ID = @aRecordSourceGMID
)
GO
It returns a table, How can I create a view by calling the above function and pass the parameter from view 'DWUQV_CLAIM '
Thanks,
Suvechha
One method is using CROSS APPLY to invoke the table-valued function for each row returned from the view. For example:
SELECT * FROM dbo.DWUQV_CLAIM AS claim CROSS APPLY dbo.GetMinCalendarClaimRecord( claim.Insurer, claim.ProductCode, claim.ClaimNumber, claim.RecordSourceGroupID, claim.RecordSourceGMID ) AS min_claim_record;This query can be encapsulated in a view, if desired. Note that I used SELECT * only as an example; you should specify an explict list of the column names you need.
这篇关于如何调用用户定义的函数表并创建视图?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!