April 15, 2019 at 8:34 pm
I am trying to use a CTE (Common Table Expression) within a ScalarFunction . Am I able to do this. I am get several red squiggly line errors right now. ("Incorrect syntax near Declare" ,"Invalid column name DateEffective","Invalid Object Name inceptionCTE",
"Invalid column name PriorPolicy") Is there a way to do this and make SQL SERVER happy ? Can anybody tell me what I would need to do with this function ? Any direction or advice would be greatly appreciated.
--Jason
USE [Premdat]
GO
/****** Object: UserDefinedFunction [dbo].[fnJasonCreate] Script Date: 4/15/2019 3:24:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnJasonCreate]
(-- Add the parameters for the function here
@pol VARCHAR(10)
)
RETURNS Datetime
AS
BEGIN
;WITH InceptionCTE AS (
SELECT p.[Policy], p.PolicyID, p.DateEffective, p.DateExpiration, p.QuoteNum, p.PriorPolicy
FROM PolicyData p WITH(NOLOCK)
WHERE p.[PolicyID] = @pol
UNION ALL
SELECT p.[Policy], p.PolicyID, p.DateEffective, p.DateExpiration, p.QuoteNum, p.PriorPolicy
FROM PolicyData p WITH(NOLOCK)
INNER JOIN InceptionCTE cd ON cd.PriorPolicy = p.[Policy]
)
DECLARE @InceptionDate DATETIME
SET @InceptionDate = (SELECT DateEffective FROM InceptionCTE WHERE PriorPolicy = 'New')
RETURN @InceptionDate
END
April 15, 2019 at 8:45 pm
Couldn't test it, but give this a try:
USE [Premdat]
GO
/****** Object: UserDefinedFunction [dbo].[fnJasonCreate] Script Date: 4/15/2019 3:24:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnJasonCreate]
(-- Add the parameters for the function here
@pol VARCHAR(10)
)
RETURNS Datetime
AS
BEGIN
DECLARE @InceptionDate DATETIME;
WITH InceptionCTE AS (
SELECT
p.[Policy]
, p.PolicyID
, p.DateEffective
, p.DateExpiration
, p.QuoteNum
, p.PriorPolicy
FROM
PolicyData p
WHERE
p.[PolicyID] = @pol
UNION ALL
SELECT
p.[Policy]
, p.PolicyID
, p.DateEffective
, p.DateExpiration
, p.QuoteNum
, p.PriorPolicy
FROM
PolicyData p
INNER JOIN InceptionCTE cd
ON cd.PriorPolicy = p.[Policy]
)
SELECT @InceptionDate = DateEffective FROM InceptionCTE WHERE PriorPolicy = 'New';
RETURN @InceptionDate
END
April 15, 2019 at 8:48 pm
Keep in mind if that CTE returns more than 1 record you might get some unexpected results.
April 15, 2019 at 9:33 pm
This helped . Thank you very much
April 17, 2019 at 1:24 pm
To clarify why you got an error:
After the CTE definition, you had a DECLARE variable statement. You can only use a CTE within the context of DML language (SELECT, INSERT, UPDATE, DELETE, MERGE).
January 30, 2022 at 9:57 am
Semicolon is missing before the WITH, ohterwise ok
January 30, 2022 at 5:12 pm
Semicolon is missing before the WITH, ohterwise ok
semi-colon is a statement terminator. Why would you need a semi-colon 'before' the WITH?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2022 at 6:40 am
Instead of worrying about where the bloody semi-colon goes, someone needs to convince this fellow that this absolutely needs to be converted to a single value iTVF or "iSF" and how to call it because I know how this "policy number" stuff works in real life and it's going to beat the crap out of the OP's server.
@jason... as Lynn said, he wrote some code but hasn't tested it because he has no test data. Please see the article at the first link in my signature line below for one way to provide the "Readily Consumable" test data that would help us help you a whole lot more.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply