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 ( Month FLOAT,
    HS_code VARCHAR(20),
    HS_Desc VARCHAR(255),
    [Country] VARCHAR(255),
    Total_qantity INT,
    Total_amount DECIMAL(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;
    SELECT CONVERT(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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • And deleting a mistake that you found on your own would be depriving someone of some information that might help them in the future

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

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