UDF could not be bound

  • Hi everyone,

    I have created a sum UDF and I cannot call  it. The variable being called cannot be bound.

    CREATE FUNCTION UDF_datasummary
    --Define input variables
    (@HS_code VARCHAR(20))
    --Define output table
    RETURNS @sums TABLE (MonthFLOAT,
    HS_codeVARCHAR(20),
    HS_DescVARCHAR(255),
    [Country]VARCHAR(255),
    Total_qantityINT,
    Total_amountDECIMAL(18,2)
    )


    AS BEGIN

    INSERT INTO @sums
    SELECT DISTINCT Month,
    [Harmonised System Code] AS HS_code,
    [Harmonised System Description] AS HS_Desc,
    [Country],
    SUM([Imports Qty])as Total_amount,
    SUM([Imports ($NZD vfd)])as Total_amount
    FROM [master].[dbo].[Aug-2020-Imports-HS10-by-Countr]
    GROUP BY
    Month,[Harmonised System Code],[Harmonised System Description], [Country]

    RETURN
    END;

    SELECT TOP (1000) [Month]
    ,[Harmonised System Code]
    ,[Harmonised System Description]
    ,[Unit Qty]
    ,[Country]
    ,[Imports ($NZD vfd)]
    ,[Imports ($NZD cif)]
    ,[Imports Qty]
    ,[Status]
    FROM [master].[dbo].[Aug-2020-Imports-HS10-by-Countr]

    DROP TABLE IF EXISTS #Imports;
    SELECTCONVERT(VARCHAR, [Harmonised System Code]) AS HS_code
    ,[Harmonised System Description]
    ,[Unit Qty]
    ,[Country]
    ,[Imports ($NZD vfd)]
    ,[Imports ($NZD cif)]
    ,[Imports Qty]
    ,[Status]
    INTO #Imports
    FROM [master].[dbo].[Aug-2020-Imports-HS10-by-Countr]

    Select b.* from #Imports
    CROSS APPLY dbo.UDF_datasummary(a.HS_code) b

    The error I get is this: Msg 4104, Level 16, State 1, Line 26

    The multi-part identifier "a.HS_code" could not be bound.

    Can anyone help me call my function?

    Thanks

  • Resolved

  • Scott745618 wrote:

    Resolved

    Scott745618 wrote:

    Resolved

    So, what was the problem?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Select b.* from #Imports
    CROSS APPLY dbo.UDF_datasummary(a.HS_code) b

    missing alias - "a"

  • frederico_fonseca wrote:

    Select b.* from #Imports
    CROSS APPLY dbo.UDF_datasummary(a.HS_code) b

    missing alias - "a"

    frederico_fonseca wrote:

    Select b.* from #Imports
    CROSS APPLY dbo.UDF_datasummary(a.HS_code) b

    missing alias - "a"

    Thanks.  I didn't even look at the code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Is there a way to delete posts when you figure out your own mistake?

  • No.  And deleting a mistake that you found on your own would be depriving someone of some information that might help them in the future.  I'm glad you found it and was curious about what it was but didn't have the time to dig for it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply