Scalar function syntax error

• saintor1

  Mr or Mrs. 500

  Points: 562

  More actions

  October 12, 2017 at 8:32 am

  #339819

  I have two functions that work.   "A" and "B"   When I tried to modify "A" to have condtiions like "B" (now "C") it doesn't work.  

  "A"

  USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl]  Script Date: 2017-10-12 09:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[QtyOnHandNoExcl](@SC nVarChar(30), @Wh nVarchar(3))
RETURNS Decimal(8,3) 
AS 
-- Returns the on hand without bins starting by MB
BEGIN
  DECLARE @ret Decimal(8,3);
  SELECT @ret = SUM(QtyOnHand1) 
  FROM SysproSStar.dbo.InvMultBin p
  WHERE p.StockCode = @SC
   AND p.Warehouse = @Wh
        AND NOT p.Note Like 'EXCL%';
  RETURN @ret;
END;

  "B"
  
  USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[LastBuyer]  Script Date: 2017-10-12 09:57:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[LastBuyer]
(@SC1 varchar(30), @CIE varchar(1))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3)
SELECT @LB = CASE @CIE WHEN 'Q' THEN

      (SELECT TOP 1 Buyer
     FROM vPurch_Q
     WHERE MStockCode=@SC1
     ORDER BY OrderEntryDate DESC)
WHEN 'I' THEN            
    (SELECT TOP 1 Buyer
     FROM vPurch_I
     WHERE MStockCode=@SC1
     ORDER BY OrderEntryDate DESC)
WHEN 'K' THEN            
    (SELECT TOP 1 Buyer
     FROM vPurch_K
     WHERE MStockCode=@SC1
     ORDER BY OrderEntryDate DESC)

  END;

  RETURN(@LB);
END

  Now the one that doesn't work

  "C"

  USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl]  Script Date: 2017-10-12 09:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[QtyOnHandNoExcl]
(@SC nVarChar(30), @Wh nVarchar(3), @CIE varchar(1))
RETURNS Decimal(8,3) 
AS 
BEGIN
DECLARE @ret Decimal(8,3)
SELECT @ret = CASE @CIE
WHEN 'K' THEN
  (SELECT @ret = SUM(QtyOnHand1) 
  FROM SysproSStar.dbo.InvMultBin p
  WHERE p.StockCode = @SC
   AND p.Warehouse = @Wh
        AND NOT p.Note Like 'EXCL%')
WHEN 'N' THEN
  (SELECT @ret = SUM(QtyOnHand1) 
  FROM SysproSStarNZ.dbo.InvMultBin p
  WHERE p.StockCode = @SC
   AND p.Warehouse = @Wh
        AND NOT p.Note Like 'EXCL%')
WHEN 'O' THEN
  (SELECT @ret = SUM(QtyOnHand1) 
  FROM SysproSStarAust.dbo.InvMultBin p
  WHERE p.StockCode = @SC
   AND p.Warehouse = @Wh
        AND NOT p.Note Like 'EXCL%')
END;
RETURN(@ret);
END;


  I get
  Msg 102, Niveau 15, État 1, Procédure QtyOnHandNoExcl, Ligne 9 [Ligne de départ du lot 7]
  Incorrect syntax near '='.
  Msg 156, Niveau 15, État 1, Procédure QtyOnHandNoExcl, Ligne 20 [Ligne de départ du lot 7]
  Incorrect syntax near the keyword 'WHEN'.
  Msg 156, Niveau 15, État 1, Procédure QtyOnHandNoExcl, Ligne 27 [Ligne de départ du lot 7]
  Incorrect syntax near the keyword 'RETURN'.

  Any assistance would be appreciated,.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 12, 2017 at 8:41 am

  #1963382

  I see what the error is, but I'm not sure what you want to do. Both functions seem unrelated, so having the same structure is illogical.
  Also, check the following article: How to Make Scalar UDFs Run Faster (SQL Spackle) - SQLServerCentral

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Thom A

  SSC Guru

  Points: 99133

  More actions

  October 12, 2017 at 8:43 am

  #1963383

  You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

  (Also, yes, see Luis' link).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• saintor1

  Mr or Mrs. 500

  Points: 562

  More actions

  October 12, 2017 at 9:01 am

  #1963389

  Thom A - Thursday, October 12, 2017 8:43 AM

  You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

  (Also, yes, see Luis' link).

  New "C" (which works).  Awesome.  Many thanks.

  USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl]  Script Date: 2017-10-12 09:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[QtyOnHandNoExcl]
(@SC nVarChar(30), @Wh nVarchar(3), @CIE varchar(1))
RETURNS Decimal(8,3) 
AS 
BEGIN
DECLARE @ret Decimal(8,3)
SELECT @ret = CASE @CIE
WHEN 'K' THEN
  (SELECT SUM(QtyOnHand1) 
  FROM SysproSStar.dbo.InvMultBin p
  WHERE p.StockCode = @SC
   AND p.Warehouse = @Wh
        AND NOT p.Note Like 'EXCL%')
WHEN 'N' THEN
  (SELECT SUM(QtyOnHand1) 
  FROM SysproSStarNZ.dbo.InvMultBin p
  WHERE p.StockCode = @SC
   AND p.Warehouse = @Wh
        AND NOT p.Note Like 'EXCL%')
WHEN 'O' THEN
  (SELECT SUM(QtyOnHand1) 
  FROM SysproSStarAust.dbo.InvMultBin p
  WHERE p.StockCode = @SC
   AND p.Warehouse = @Wh
        AND NOT p.Note Like 'EXCL%')
END;
RETURN(@ret);
END;


• Thom A

  SSC Guru

  Points: 99133

  More actions

  October 12, 2017 at 9:09 am

  #1963395

  saintor1 - Thursday, October 12, 2017 9:01 AM

  Thom A - Thursday, October 12, 2017 8:43 AM

  You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

  (Also, yes, see Luis' link).

  New "C" (which works).  Awesome.  Many thanks.
  

  Great, but did you look at Luis' link?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  October 12, 2017 at 9:13 am

  #1963399

  And be super careful of these scalar functions. They often cause performance issues.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 12, 2017 at 9:15 am

  #1963400

  saintor1 - Thursday, October 12, 2017 9:01 AM

  Thom A - Thursday, October 12, 2017 8:43 AM

  You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

  (Also, yes, see Luis' link).

  New "C" (which works).  Awesome.  Many thanks.
  

  And this is why I didn't want to explain the problem. You are repeating your code 3 times. There's no use for that.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  October 12, 2017 at 12:38 pm

  #1963460

  If you decide you need an inline table-valued function for performance reasons, try this:
  USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl] Script Date: 2017-10-12 09:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION dbo.QtyOnHandNoExcl (
    @SC nvarchar(30),
    @Wh nvarchar(3),
    @CIE varchar(1)
)
RETURNS TABLE WITH SCHEMABINDING
RETURN
    SELECT CONVERT(decimal(8,3), SUM(X.QtyOnHand1)) AS RET
    FROM (
            SELECT 'K' AS CIE, QtyOnHand1
            FROM SysproSStar.dbo.InvMultBin AS p
            WHERE @CIE = 'K'
                AND p.StockCode = @SC
                AND p.Warehouse = @Wh
                AND NOT p.Note Like 'EXCL%'
            UNION ALL
            SELECT 'N', QtyOnHand1
            FROM SysproSStarNZ.dbo.InvMultBin AS p
            WHERE @CIE = 'N'
                AND p.StockCode = @SC
                AND p.Warehouse = @Wh
                AND NOT p.Note Like 'EXCL%'
            UNION ALL
            SELECT 'O', QtyOnHand1
            FROM SysproSStarAust.dbo.InvMultBin AS p
            WHERE @CIE = 'K'
                AND p.StockCode = @SC
                AND p.Warehouse = @Wh
                AND NOT p.Note Like 'EXCL%'
        ) AS X;
GO

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• saintor1

  Mr or Mrs. 500

  Points: 562

  More actions

  October 12, 2017 at 1:07 pm

  #1963462

  I guess that I did something that should be a no-no... lol!    I remember having seen this RETURN TABLE instead of a variable in a SQLCENTRAL article a few months ago, I was interested but honestly I didn't understand some crucial points and didn't push the exercise.   Baby steps for me.

  Steve I tried your code and I got 

  Msg 2010, Niveau 16, État 1, Procédure QtyOnHandNoExcl, Ligne 8 [Ligne de départ du lot 7]
  Cannot perform alter on 'dbo.QtyOnHandNoExcl' because it is an incompatible object type.

  If I get this right, your approach implies an union of the 3 tables, so 200000 rows instead of 40000-70000 and it still will work faster?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 12, 2017 at 1:29 pm

  #1963464

  saintor1 - Thursday, October 12, 2017 1:07 PM

  I guess that I did something that should be a no-no... lol!    I remember having seen this RETURN TABLE instead of a variable in a SQLCENTRAL article a few months ago, I was interested but honestly I didn't understand some crucial points and didn't push the exercise.   Baby steps for me.

  Steve I tried your code and I got 

  Msg 2010, Niveau 16, État 1, Procédure QtyOnHandNoExcl, Ligne 8 [Ligne de départ du lot 7]
  Cannot perform alter on 'dbo.QtyOnHandNoExcl' because it is an incompatible object type.

  If I get this right, your approach implies an union of the 3 tables, so 200000 rows instead of 40000-70000 and it still will work faster?

  You would need to do a DROP and CREATE because you had a scalar function and this is an inline table-valued function.
  The approach shows the UNION ALL among the 3 tables, but would only read one (or none) depending on the parameter as 2 of the queries will have conditions that return false without having to read the table.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  October 12, 2017 at 1:55 pm

  #1963470

  saintor1 - Thursday, October 12, 2017 1:07 PM

  I guess that I did something that should be a no-no... lol!    I remember having seen this RETURN TABLE instead of a variable in a SQLCENTRAL article a few months ago, I was interested but honestly I didn't understand some crucial points and didn't push the exercise.   Baby steps for me.

  Steve I tried your code and I got 

  Msg 2010, Niveau 16, État 1, Procédure QtyOnHandNoExcl, Ligne 8 [Ligne de départ du lot 7]
  Cannot perform alter on 'dbo.QtyOnHandNoExcl' because it is an incompatible object type.

  If I get this right, your approach implies an union of the 3 tables, so 200000 rows instead of 40000-70000 and it still will work faster?

  Yeah, forgot about what Luis indicated.   You can't alter a SCALAR function into an inline TVF, so you'd have to DROP and then CREATE it instead.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• DesNorton

  SSC-Insane

  Points: 24399

  More actions

  October 12, 2017 at 2:02 pm

  #1963472

  Deleted - Code misread

  ---

  How to post data/code on a forum to get the best help.

• saintor1

  Mr or Mrs. 500

  Points: 562

  More actions

  October 12, 2017 at 2:46 pm

  #1963479

  EDIT: Creation didn't work. On an hunch, removed WITH SCHEMABINDING and the creation of the function passed  Is it essential?  
  EDIT 2: But when I try SELECT dbo.QtyOnHandNoExcl('0137090','K0','K') AS TOT ,I get; 
  Msg 4121, Niveau 16, État 1, Ligne 1
  Cannot find either column "dbo" or the user-defined function or aggregate "dbo.QtyOnHandNoExcl", or the name is ambiguous.

  I can figure that as a table, it is not the correct way to call it.

  Here is the original query definition

  SELECT   SysproSStar.dbo.WipMaster.Job, SysproSStar.dbo.WipMaster.Warehouse, SysproSStar.dbo.WipMaster.JobClassification, SysproSStar.dbo.WipMaster.StockCode,
         SysproSStar.dbo.WipMaster.JobDescription, SysproSStar.dbo.WipMaster.StockDescription, SysproSStar.dbo.WipMaster.JobDeliveryDate,
         SysproSStar.dbo.WipMaster.JobStartDate, SysproSStar.dbo.WipMaster.ActCompleteDate, SysproSStar.dbo.WipMaster.Complete,
         SysproSStar.dbo.WipMaster.QtyToMake, SysproSStar.dbo.WipMaster.QtyManufactured,
         SysproSStar.dbo.WipMaster.QtyToMake - SysproSStar.dbo.WipMaster.QtyManufactured AS Outs, SysproSStar.dbo.WipJobAllMat.StockCode AS StockCode1,
         SysproSStar.dbo.WipJobAllMat.Line, SysproSStar.dbo.WipJobAllMat.StockDescription AS Expr4, SysproSStar.dbo.WipJobAllMat.UnitCost,
         SysproSStar.dbo.WipJobAllMat.Uom,
         SysproSStar.dbo.WipJobAllMat.UnitQtyReqd * (SysproSStar.dbo.WipMaster.QtyToMake - SysproSStar.dbo.WipMaster.QtyManufactured) AS QtyReq,
         SysproSStar.dbo.WipJobAllMat.QtyIssued, SysproSStar.dbo.WipJobAllMat.ValueIssued, SysproSStar.dbo.WipJobAllMat.SequenceNum,
         SysproSStar.dbo.WipJobAllMat.PhantomParent, SysproSStar.dbo.WipJobAllMat.UnitQtyReqd, SysproSStar.dbo.WipJobAllMat.AllocCompleted,
         SysproSStar.dbo.WipJobAllMat.KitIssueItem, dbo.QtyOnHandNoExcl(SysproSStar.dbo.InvWarehouse.StockCode, SysproSStar.dbo.InvWarehouse.Warehouse, 'K')
         AS QtyOnHandNoExcl1
FROM    SysproSStar.dbo.WipMaster INNER JOIN
         SysproSStar.dbo.WipJobAllMat ON SysproSStar.dbo.WipMaster.Job = SysproSStar.dbo.WipJobAllMat.Job
WHERE   (SysproSStar.dbo.WipMaster.Complete <> 'Y')

  I usually do my queries with microsoft visual studio query designer.   I guess that I am still stuck in syntax.

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 12, 2017 at 11:53 pm

  #1963493

  Luis Cazares - Thursday, October 12, 2017 9:15 AM

  saintor1 - Thursday, October 12, 2017 9:01 AM

  Thom A - Thursday, October 12, 2017 8:43 AM

  You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

  (Also, yes, see Luis' link).

  New "C" (which works).  Awesome.  Many thanks.
  

  And this is why I didn't want to explain the problem. You are repeating your code 3 times. There's no use for that.

  I thought so too but he's not.  The table in the FROM clause is different based on @CIE.

  --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)
  

• saintor1

  Mr or Mrs. 500

  Points: 562

  More actions

  October 13, 2017 at 6:10 am

  #1963537

  Ok *success*, I think that I figured out the use of table UDF...

  SELECT   I.StockCode, I.Description, I.AlternateKey1 AS Family,
         I.LeadTime, I.Ebq, W.Warehouse, U.RET AS QtyOnHandNoExcl,
         W.QtyInTransit, W.QtyOnOrder, W.QtyAllocatedWip,
         W.QtyAllocated AS QtyAllocatedSales, W.UnitCost, W.DateLastSale,
         W.DateLastPurchase, W.UserField1 AS MINMAXCTRL, W.MinimumQty,
         W.MaximumQty, W.ReOrderQty AS ReOrderPt, W.YtdQtySold,
         W.PrevYearQtySold, I.Supplier, I.Version,
         I.StockOnHold, I.CountryOfOrigin, I.PartCategory
FROM    SysproSStar.dbo.InvMaster I INNER JOIN
         SysproSStar.dbo.InvWarehouse W ON I.StockCode = W.StockCode OUTER 
         APPLY dbo.QtyOnHandNoExcl(W.StockCode, W.Warehouse, 'K') U;