如何调用用户定义的函数表并创建视图？ [英] 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屋！