none
How to call a user defined function table and create a view ?

    Question

  • 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

    Thursday, May 23, 2019 12:14 PM

All replies

  • 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.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, May 23, 2019 12:39 PM
    Moderator