variable in a view

Question

Post reply

variable in a view

phingers

Ten Centuries

Points: 1041
More actions
April 4, 2014 at 6:53 am

#396542

I would like to create a view based on the following sql statement. I understand that I cannot declare a variable for use in a view but how do I go about it? Otherwise I have to hard code in the value from ctlfil where recid=24
declare @retained varchar(16)
select @retained=nomcode from ctlfil where recid=24
SELECT dbo.nomfil.nomcode, ISNULL(calc.val, 0) AS ytdper, calc.costcentre
FROM dbo.nomfil LEFT OUTER JOIN
(SELECT CASE WHEN nomfil_1.type IN (3, 4) THEN nomtrn.nomcode ELSE @retained END AS nomcode, SUM(dbo.nomtrn.val * (1 - 2 * dbo.nomtrn.crddbt)) AS val,
dbo.nomtrn.costcentre
FROM dbo.nomtrn INNER JOIN
dbo.nomfil AS nomfil_1 ON dbo.nomtrn.nomcode = nomfil_1.nomcode
WHERE (dbo.nomtrn.period <
(SELECT glpernum / 100 * 100 AS Expr1
FROM dbo.perfil))
GROUP BY CASE WHEN nomfil_1.type IN (3, 4) THEN nomtrn.nomcode ELSE @retained END, dbo.nomtrn.costcentre) AS calc ON calc.nomcode = dbo.nomfil.nomcode

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

This should work as long as you don't have multiple rows being returned from that subquery

SELECT dbo.nomfil.nomcode ,

ISNULL(calc.val, 0) AS ytdper ,

calc.costcentre

FROM dbo.nomfil

LEFT OUTER JOIN ( SELECT CASE WHEN nomfil_1.type IN ( 3, 4 )

THEN nomtrn.nomcode

ELSE ( SELECT nomcode

FROM ctlfil

WHERE recid = 24

)

END AS nomcode ,

SUM(dbo.nomtrn.val * ( 1 - 2 * dbo.nomtrn.crddbt )) AS val ,

dbo.nomtrn.costcentre

FROM dbo.nomtrn

INNER JOIN dbo.nomfil AS nomfil_1 ON dbo.nomtrn.nomcode = nomfil_1.nomcode

WHERE ( dbo.nomtrn.period < ( SELECT

glpernum / 100 * 100 AS Expr1

FROM

dbo.perfil

) )

GROUP BY CASE WHEN nomfil_1.type IN ( 3, 4 )

THEN nomtrn.nomcode

ELSE ( SELECT nomcode

FROM ctlfil

WHERE recid = 24

)

END ,

dbo.nomtrn.costcentre

) AS calc ON calc.nomcode = dbo.nomfil.nomcode

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

phingers Ten Centuries Points: 1041 More actions · Answer 2

Thanks I tried that but I can't use the subquery (SELECT nomcode FROM ctlfil WHERE recid = 24) in the GROUP BY clause

phingers Ten Centuries Points: 1041 More actions · Answer 3

I can create a user defined function to get the value but not sure how I can use that either?

CREATE FUNCTION dbo.udf_retained_nomcode()

RETURNS @ret TABLE (col VARCHAR (16))

AS

BEGIN

DECLARE @retained varchar(16);

SELECT @retained=nomcode from ctlfil where recid=24;

INSERT INTO @ret SELECT @retained;

RETURN;

END;

GO

SELECT * FROM dbo.udf_retained_nomcode();

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

phingers (4/4/2014)
Thanks I tried that but I can't use the subquery (SELECT nomcode FROM ctlfil WHERE recid = 24) in the GROUP BY clause

Why not?

The scalar function will do the same thing the subquery does, just much, much slower.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 5

this is basically the same solution as Gails in a different format; it's just a CROSS JOINED CTE to get the variable instead of a direct sub select.

CREATE VIEW myVIEW

AS

WITH MyCTE

AS (SELECT TOP 1

nomcode AS RetainedVariable

FROM ctlfil

WHERE recid = 24

ORDER BY

nomcode)

SELECT

dbo.nomfil.nomcode,

ISNULL(calc.val, 0) AS ytdper,

calc.costcentre

FROM dbo.nomfil

LEFT OUTER JOIN (SELECT

CASE

WHEN nomfil_1.type IN ( 3, 4 )

THEN

nomtrn.nomcode

ELSE

MyCTE.RetainedVariable

END AS nomcode,

SUM(dbo.nomtrn.val * ( 1 - 2 * dbo.nomtrn.crddbt )) AS val,

dbo.nomtrn.costcentre

FROM dbo.nomtrn

INNER JOIN dbo.nomfil AS nomfil_1

ON dbo.nomtrn.nomcode = nomfil_1.nomcode

CROSS JOIN MyCTE --there better be only one row!

WHERE ( dbo.nomtrn.period < (SELECT

glpernum / 100 * 100 AS Expr1

FROM dbo.perfil) )

GROUP BY

CASE

WHEN nomfil_1.type IN ( 3, 4 )

THEN

nomtrn.nomcode

ELSE

MyCTE.RetainedVariable

END,

dbo.nomtrn.costcentre) AS calc

ON calc.nomcode = dbo.nomfil.nomcode

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

phingers Ten Centuries Points: 1041 More actions · Answer 6

Thanks but when I try this it returns 'mutipart identifier MyCTE.RetainedVariable could not be bound'?

phingers Ten Centuries Points: 1041 More actions · Answer 7

I finally got this to create the view successfully with the following code:

WITH MyCTE AS (SELECT TOP (1) nomcode AS Retained

FROM dbo.ctlfil

WHERE (recid = 24)

ORDER BY Retained)

SELECT dbo.nomfil.nomcode, ISNULL(calc.val, 0) AS ytdper, calc.costcentre

FROM dbo.nomfil LEFT OUTER JOIN

(SELECT CASE WHEN nomfil_1.type IN (3, 4) THEN nomtrn.nomcode ELSE MyCTE_1.Retained END AS nomcode,

SUM(dbo.nomtrn.val * (1 - 2 * dbo.nomtrn.crddbt)) AS val, dbo.nomtrn.costcentre

FROM dbo.nomtrn INNER JOIN

dbo.nomfil AS nomfil_1 ON dbo.nomtrn.nomcode = nomfil_1.nomcode CROSS JOIN

MyCTE AS MyCTE_1

WHERE (dbo.nomtrn.period <

(SELECT glpernum / 100 * 100 AS Expr1

FROM dbo.perfil))

GROUP BY CASE WHEN nomfil_1.type IN (3, 4) THEN nomtrn.nomcode ELSE MyCTE_1.Retained END, dbo.nomtrn.costcentre) AS calc ON

calc.nomcode = dbo.nomfil.nomcode

Thanks for the help everyone