Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


variable in a view


variable in a view

Author
Message
phingers
phingers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 214
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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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
phingers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 214
Thanks I tried that but I can't use the subquery (SELECT nomcode FROM ctlfil WHERE recid = 24) in the GROUP BY clause
phingers
phingers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 214
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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39008
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
phingers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 214
Thanks but when I try this it returns 'mutipart identifier MyCTE.RetainedVariable could not be bound'?
phingers
phingers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 214
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search