Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

variable in a view Expand / Collapse
Author
Message
Posted Friday, April 4, 2014 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:29 AM
Points: 36, Visits: 175
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

Post #1558475
Posted Friday, April 4, 2014 7:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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 2008, MVP
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

Post #1558485
Posted Friday, April 4, 2014 7:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:29 AM
Points: 36, Visits: 175
Thanks I tried that but I can't use the subquery (SELECT nomcode FROM ctlfil WHERE recid = 24) in the GROUP BY clause
Post #1558486
Posted Friday, April 4, 2014 7:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:29 AM
Points: 36, Visits: 175
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

Post #1558496
Posted Friday, April 4, 2014 7:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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 2008, MVP
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

Post #1558497
Posted Friday, April 4, 2014 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1558540
Posted Friday, April 4, 2014 8:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:29 AM
Points: 36, Visits: 175
Thanks but when I try this it returns 'mutipart identifier MyCTE.RetainedVariable could not be bound'?
Post #1558556
Posted Wednesday, April 9, 2014 5:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:29 AM
Points: 36, Visits: 175
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
Post #1559883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse