September 27, 2019 at 7:09 pm
CREATE VIEW [dbo].[vwstudent]
AS
DECLARE @Date datetime2
SELECT @Date = value
FROM dbo.configuraiton
WHERE text = 'PivotValue'
SELECT ID
, StudentId
, CASE WHEN STD.EfDate <= @Date THEN DATEADD(MONTH,-2,STD.EfDate)
ELSE STD.EfDate
END
, CreatedDate
FROM dbo.Student STD
GO
September 27, 2019 at 7:13 pm
You cannot use variables in a view.
Either re-wrtie it as a single statement, or create a stored procedure.
September 27, 2019 at 7:15 pm
How to rewrite as single statement with out variable?
September 27, 2019 at 7:21 pm
How to rewrite as single statement with out variable?
I don't know your table structures or data types. I also do not have any sample data to work with.
So the following 2 options are merely untested guesses
WITH cteDate AS (
SELECT TheDate = cast(value as datetime2)
FROM dbo.configuraiton
WHERE text = 'PivotValue'
)
SELECT ID
, StudentId
, CASE
WHEN STD.EfDate <= cte.TheDate THEN DATEADD(MONTH, -2, STD.EfDate)
ELSE STD.EfDate
END
, CreatedDate
FROM dbo.Student STD
CROSS JOIN cteDate AS cte;
SELECT ID
, StudentId
, CASE
WHEN STD.EfDate <= D.TheDate THEN DATEADD(MONTH, -2, STD.EfDate)
ELSE STD.EfDate
END
, CreatedDate
FROM dbo.Student STD
CROSS APPLY ( SELECT TheDate = cast(value as datetime2)
FROM dbo.configuraiton
WHERE text = 'PivotValue'
) AS D;
September 27, 2019 at 7:33 pm
thank you, it worked
September 27, 2019 at 7:50 pm
CREATE VIEW [dbo].[vwstudent]
AS
SELECT STD.ID,
STD.StudentId,
CASE WHEN STD.EfDate <= c.value THEN DATEADD(MONTH, -2, STD.EfDate)
ELSE STD.EfDate
END,
STD.CreatedDate
FROM dbo.Student STD
INNER JOIN dbo.configuraiton c
ON c.text = 'PivotValue'
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply