UDF could not be bound

Question

Post reply

UDF could not be bound

Scott745618

SSC Enthusiast

Points: 197

May 16, 2021 at 3:59 am

#3883938

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

May 19, 2021 at 11:30 am

This was removed by the editor as SPAM
July 1, 2021 at 4:24 pm

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

Scott745618 SSC Enthusiast Points: 197 More actions · Answer 1

Scott745618

SSC Enthusiast

Points: 197

May 16, 2021 at 6:14 am

#3883964

Resolved

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

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

missing alias - "a"

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

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)

Scott745618 SSC Enthusiast Points: 197 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

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)