June 20, 2017 at 9:56 am
My code is as follow
ALTER FUNCTION [dbo].[LastBuyer]
(@SC1 varchar(30))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3)
SELECT TOP 1 @LB=Buyer
FROM vPurch_Q
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC
RETURN(@LB)
END
I added a parameter @CIE to change the source of data but my syntax must be wrong. What needs to be changed?
ALTER FUNCTION [dbo].[LastBuyer]
(@SC1 varchar(30), @CIE varchar(1))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3)
CASE @CIE
WHEN 'Q' THEN
SELECT TOP 1 @LB=Buyer
FROM vPurch_Q
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC
END
RETURN(@LB)
END
June 20, 2017 at 10:02 am
saintor1 - Tuesday, June 20, 2017 9:56 AMMy code is as follow
ALTER FUNCTION [dbo].[LastBuyer]
(@SC1 varchar(30))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3)
SELECT TOP 1 @LB=Buyer
FROM vPurch_Q
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC
RETURN(@LB)
ENDI added a parameter @CIE to change the source of data but my syntax must be wrong. What needs to be changed?
ALTER FUNCTION [dbo].[LastBuyer]
(@SC1 varchar(30), @CIE varchar(1))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3)
CASE @CIE
WHEN 'Q' THEN
SELECT TOP 1 @LB=Buyer
FROM vPurch_Q
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC
ENDRETURN(@LB)
END
CASE statements are "expressions". They can't stand alone without being SELECTed or being part of some other statement or other part of a query. The question, however, is that given the logic of your code, what do you plan to return from the function if that new parameter is anything other than 'Q' ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2017 at 10:06 am
You're using your CASE expression incorrectly.
Try: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) END;
RETURN(@LB);
END
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 20, 2017 at 11:18 am
Thom A - Tuesday, June 20, 2017 10:06 AMYou're using your CASE expression incorrectly.
So I thought. Thank you!
Instead of doing this and while being there, out of curiosity I also tried to use the @CIE as the last letter of the recordset
ALTER FUNCTION [dbo].[LastBuyer]
(@SC1 varchar(30), @CIE varchar(1))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3)
SELECT TOP 1 @LB=Buyer
FROM 'vPurch_' & @CIE
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC
RETURN(@LB)
END
Probably another syntax no-no, but is there a way to get this result without using CASE WHEN END?
June 20, 2017 at 12:15 pm
you can't concatenate the name of the table in the FROM clause like that, if the CASE statement and separate subqueries isn't workable for some reason, you'd have to use dynamic SQL and sp_executesql to run the different source tables:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql
June 20, 2017 at 12:29 pm
Further, using scalar functions to select from a table is going to cause a performance problem. The function code needs to be converted to an iTVF.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2017 at 12:50 pm
Jeff Moden - Tuesday, June 20, 2017 12:29 PMFurther, using scalar functions to select from a table is going to cause a performance problem. The function code needs to be converted to an iTVF.
It is a killer, at the best the overhead for each execution is 5-6 ms but that would be the best case, couple it up with a select and you'll have a gnat in a molasses, "a bit slow" 😉
😎
June 20, 2017 at 1:19 pm
I read stuff about CROSS_APPLY that might be relevant (is it?)
June 21, 2017 at 2:02 am
As Jeff suggested, a TVF would be a much better option than a Scalar funciton for performance. So you could do something like this:CREATE FUNCTION dbo.LastBuyer_tvf (@SC1 varchar(30), @CIE varchar(1))
RETURNS TABLE AS
RETURN
(
SELECT TOP 1 Q.Buyer
FROM vPurch_Q Q
WHERE @CIE = 'Q'
AND Q,MStockCode = @sc1
ORDER BY OrderEntryDate DESC;
)
GO
--You can use your new TVF by using a CROSS APPLY
SELECT YT.*,
LB.Buyer
FROM YourTable YT
CROSS APPLY dbo.LastBuyer_tvf (YT.SC1, YT.CIE) LB --I've made assumptions on your JOIN criteria
WHERE ...;-- Your WHERE criteria
GO
What you can't achieve with a function is dynamic SQL. You have to use a StoredProcedure, like the others pointed out. To do this you'd need to create your whole query using "D-SQL", rather than just the part to get the LastBuyer, and then execute your whole query string.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 21, 2017 at 9:18 am
Awesome (and learning)
Now getting the best of both worlds, I tried to alter the last tvf function (that was working) with other examples I found. Basically a table function with conditions.
USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[LastBuyer_tvf] Script Date: 2017-06-21 10:46:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[LastBuyer_tvf] (@SC1 varchar(30), @CIE varchar(1))
RETURNS @Result TABLE
AS
BEGIN
If @CIE= 'Q'
BEGIN
Insert into @Result SELECT TOP 1 Q.Buyer FROM vPurch_Q Q
WHERE @CIE = 'Q'
AND Q.MStockCode = @sc1
ORDER BY OrderEntryDate DESC
END
Else
If @CIE= 'I'
BEGIN
Insert into @Result SELECT TOP 1 Q.Buyer FROM vPurch_I Q
WHERE @CIE = 'I'
AND Q.MStockCode = @sc1
ORDER BY OrderEntryDate DESC
END
RETURN
END
I keep getting "Incorrect syntax near the keyword 'AS'."
Any idea of what is wrong
June 21, 2017 at 10:29 am
Thom A - Tuesday, June 20, 2017 10:06 AMYou're using your CASE expression incorrectly.Try:
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) END;RETURN(@LB);
END
GO
Your approach to SQL is completely wrong. You are writing FORTRAN and BASIC in SQL! You’re used to having in line simple functions. These were supported by the prefix “FN-†in versions of those languages.
Did you know the GTIN code for global trade items is only 15 characters long? And that all identifiers for stock items are fixed length? Your VARCHAR(30) Looks like someone invented a local SKU code and didn’t know what he was doing. It takes a lot of time to properly designed and encoding scheme, but the ISO guides are to use only digits, Latin letters, and a few simple punctuation marks in fixed length strings. The fixed length strings guarantee they will fit on paper forms and video displays.
Also, VARCHAR(1) is absurd; think about it.
Good SQL programmers do not depend on proprietary features like the “TOP(n)..ORDER BY..†in Microsoft SQL Server. The procedural programmers understand sorting, so they use it
Basic SQL forum etiquette requires that you post DDL, which you failed to do. This means we have to guess and write code for you, and provide sample data for you. Please remember that were working for free before you are rude to us.
Here are my guesses without any help from you. I’m scared stiff it that “V_†prefix on “vPurch_Q†might have been metadata marking something as a view. I hope not. That would be a really serious design flaw.
CREATE TABLE Purchases
(po_nbr CHAR(10) NOT NULL PRIMARY KEY,
stock_gtin CHAR(15) NOT NULL
REFERENCES Stock(stock_gtin),
buyer_id CHAR(3) NOT NULL
REFERENCES Buyers(buyer_id),
order_entry_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
..);
CREATE VIEW Last_Buyers
AS
WITH X
AS
(SELECT stock_gtin, buyer_id, order_entry_date,
MAX(order_entry_date)
OVER(PARTITION BY buyer_id) AS last_order_entry_date
FROM Purchases)
SELECT stock_gtin, buyer_id, order_entry_date
FROM X
WHERE order_entry_date = last_order_entry_date;
SQL is based on tables; tables are sets. Unlike FORTRAN and BASIC, we prefer to work with a completed set and not one row (actually records in their data model) at a time with function calls.
>> I added a parameter @in_cie to change the source of data but my syntax must be wrong. What needs to be changed? <<
Once again we can see how your mindset is wrong. The choice of the word “source†implies flow and we don’t have any data flow in declarative languages like SQL. This is probably why you think the CASE expression is a control flow statement!
Get a book on declarative programming and un-learn your current mindset.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 21, 2017 at 10:38 am
saintor1 - Wednesday, June 21, 2017 9:18 AMI keep getting "Incorrect syntax near the keyword 'AS'."Any idea of what is wrong
It looks like you've partially turned it back into a scalar function. To make it a inline table valued function maybe something like this:
ALTER FUNCTION [dbo].[LastBuyer_tvf] (@SC1 varchar(30), @CIE varchar(1))
RETURNS TABLE
AS
RETURN (
SELECT Buyer FROM
(SELECT TOP 1 Q.Buyer FROM vPurch_Q Q
WHERE @CIE = 'Q'
AND Q.MStockCode = @sc1
ORDER BY OrderEntryDate DESC) sqQ
UNION ALL
SELECT Buyer FROM
(SELECT TOP 1 Q.Buyer FROM vPurch_I Q
WHERE @CIE = 'I'
AND Q.MStockCode = @sc1
ORDER BY OrderEntryDate DESC) sqI
)
GO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply