I am trying to replace a cursor with a CTE. Is it possible to scroll
through the records returned using the CTE?
i.e.
If I get more than one row returned check the values in each row? A bit
like a result set?
I know there is some recursive stuff you can do but I don't think it
will work for this as it is not a Parent/Child i.e. hierarchy
situation.
Thanks for any help offered
JamieYou'll need to post more details of your problem.
If you are interested in recursive CTEs, there is an excellent article
here
http://www.sqlservercentral.com/col...lserver2005.asp|||>I am trying to replace a cursor with a CTE. Is it possible to scroll
> through the records returned using the CTE?
Can you explain exactly what you are trying to accomplish? This description
is a bit vague. http://www.aspfaq.com/5006
> If I get more than one row returned check the values in each row? A bit
> like a result set?
Depending on exactly what you are doing "in each row," a cursor may be the
only way. Again, if you can explain better exactly what you are doing, with
real specs, we may be able to provide more helpful input.
http://www.aspfaq.com/5006|||Thanks for your responses.
The following is what I am attempting.
If I have:
select 1 AS Counter, custid, code from customer where custid IN (8072,
7786)
which returns:
CUSTID CODE
7786 [code]
8072 #
I turn this into a CTE
WITH CustCte(CustID, Code)
AS
(SELECT CustID, Code FROM dbo.Customer WHERE CustID IN (8072, 7786))
SELECT CustID, Code FROM CustCte
GO
Which naturally gives me:
CUSTID CODE
7786 [code]
8072 #
I now want navigate through each row i.e.
For first row if Code = XYZ then do something. else move to next row
If next row Code = XYX then do something.
I came up with the following and various different variations but can't
get the Counter to increment.
WITH CustCte(Counter, CustID, Code)
AS
(SELECT 1 AS Counter, CustID, Code FROM dbo.Customer WHERE CustID IN
(8072, 7786))
SELECT Counter = Counter + 1, CustID, Code FROM CustCte
do you have further thoughts.|||jamie.downs@.risk.sungard.com wrote:
> Thanks for your responses.
> The following is what I am attempting.
> If I have:
> select 1 AS Counter, custid, code from customer where custid IN (8072,
> 7786)
> which returns:
> CUSTID CODE
> 7786 [code]
> 8072 #
> I turn this into a CTE
> WITH CustCte(CustID, Code)
> AS
> (SELECT CustID, Code FROM dbo.Customer WHERE CustID IN (8072, 7786))
> SELECT CustID, Code FROM CustCte
> GO
> Which naturally gives me:
> CUSTID CODE
> 7786 [code]
> 8072 #
> I now want navigate through each row i.e.
> For first row if Code = XYZ then do something. else move to next row
> If next row Code = XYX then do something.
>
This is not a full description of the problem. What we need to know is
what is the operation that you are calling "do something". If "do
something" means "do some data manipulation" then chances are you can
do it without a cursor or a loop.
The best way to specify your problem is to post DDL, sample data and
show your required end results.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If the "do something" part can be written as a function you don't need a
cursor. Even if "do something" returns a set - CROSS APPLY can be used.
The Counter in your CTE would only increment in a recursion - i.e. if there
was a recursive member in addition to the anchor member. And even then the
value would reflect the number of recursions, not the number of rows.
An alternative to cursors may be a table variable, but this would require
you to add the logic to "simulate" a cursor.
ML
http://milambda.blogspot.com/|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
A CTE is like a VIEW, and not anything like a cursor. There is no
scrolling in SQL since it is a declarative language.|||>> I now want navigate through each row.. <<
"Navigate" is a dirty word in RDBMS. In fact, we replaced navigational
DBMS systems with RDBMS to get rid of it.
Since I cannot figure out the "do something" function from what you
posted, my guess would be that you want a CASE expression. something
like this:
UPDATE Customers
SET foobar
= CASE WHEN foo_code = 'xyz'
THEN 42 ELSE foobar END
WHERE cust_id IN (8072, 7786);
But who knows?|||> CREATE TABLE #Cust2 (CustID INT, Code VARCHAR(10))
INSERT #Cust2 SELECT CustID, CASE Code
WHEN 'Cust1' THEN NULL ELSE Code END
FROM #Cust;
SELECT * FROM #Cust2;
-- poof! Cursor be gone
Or better yet, eliminate the need for #Cust2 in the first place:
SELECT CustID, CASE Code
WHEN 'Cust1' THEN NULL ELSE Code END
FROM #Cust;
If we had seen how #Cust was populated in the first place, we can probably
improve the performance even further by eliminating that I/O. But again,
you haven't provided enough details. The line count of your stored
procedure hardly tells us what it is actually doing. In general, stop
trying to do Visual Basic style programming in the database...|||Hi Aaron,
Thanks for your reply. I did say it was a much simplified version of
the original proc. I find it quite amusing that the general comments
you hear about TSQL is to avoid Cursors and Temp tables wherever
possible and to make SQL objects as simple as possible which what I am
trying to achieve. Not VB. You still want more details. Well her is
the full blown proc. No I did not write it.
CREATE procedure DBO.Prc_Get_Checks
@.CURSORSQL VARCHAR(8000),
@.WHERECLAUSE TEXT,
@.CHECKSFORDEALVIOLATIONS BIT,
@.CALCULATEMANUAL BIT,
@.SHOWINACTIVE BIT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.CheckID INT
DECLARE @.PortfolioID INT
DECLARE @.ClassID INT
DECLARE @.MeasureID INT
DECLARE @.ChecksType INT
DECLARE @.Value1 VARCHAR(200)
DECLARE @.Value2 VARCHAR(800)
DECLARE @.Message VARCHAR(200)
DECLARE @.MessageID INT
DECLARE @.Severity INT
DECLARE @.ReviewDate DATETIME
DECLARE @.ExpiryDate DATETIME
DECLARE @.AssetID INT
DECLARE @.TemplateRef INT
DECLARE @.IsConditional BIT
DECLARE @.Active BIT
DECLARE @.Threshold FLOAT
DECLARE @.HasTransfers BIT
DECLARE @.CheckSymbol VARCHAR(20)
DECLARE @.ChecksTypeSymbol VARCHAR(20)
-- template variables
DECLARE @.TemplateMeasureID INT
DECLARE @.TemplateChecksType INT
DECLARE @.TemplateValue1 VARCHAR(200)
DECLARE @.TemplateValue2 VARCHAR(800)
DECLARE @.TemplateMessage VARCHAR(200)
DECLARE @.TemplateMessageID INT
DECLARE @.TemplateSeverity INT
DECLARE @.TemplateReviewDate DATETIME
DECLARE @.TemplateExpiryDate DATETIME
DECLARE @.TemplateAssetID INT
DECLARE @.TemplateActive BIT
DECLARE @.TemplateThreshold FLOAT
DECLARE @.TemplateChecksTypeSymbol VARCHAR(20)
DECLARE @.TemplatePortfSymbol VARCHAR(20)
DECLARE @.Ctrl_Amt_Class_Symbol VARCHAR(20)
DECLARE @.Ctrl_Amt FLOAT
DECLARE @.Ctrl_Active BIT
DECLARE @.Duplicate_CheckCount INT
DECLARE @.CHECKSTAB TABLE
(CheckID INT,
PortfolioID INT,
ClassID INT,
MeasureID INT,
ChecksType INT,
Value1 VARCHAR(200),
Value2 VARCHAR(800),
Message VARCHAR(200),
MessageID INT,
Severity INT,
ReviewDate DATETIME,
ExpiryDate DATETIME,
AssetID INT,
TemplateRef INT,
IsConditional BIT,
Active BIT,
Threshold FLOAT,
HasTransfers BIT)
-- Declare a cursor that represents placeholder/ordinary check rows
from the checks table
-- for portfolios specified in this function's parameter
-- concatenate the two...
EXEC(@.CURSORSQL + @.WHERECLAUSE)
OPEN CHECK_CURSOR
-- Fetch the cursor column values into variables.
-- These some of these variables' values may be overridden for
placeholders
FETCH NEXT FROM CHECK_CURSOR INTO
@.CheckID,
@.PortfolioID,
@.ClassID,
@.MeasureID,
@.ChecksType,
@.Value1,
@.Value2,
@.Message,
@.MessageID,
@.Severity,
@.ReviewDate,
@.ExpiryDate,
@.AssetID,
@.TemplateRef,
@.IsConditional,
@.Active,
@.Threshold,
@.HasTransfers,
@.CheckSymbol,
@.ChecksTypeSymbol
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- IF @.CHECKSYMBOL = 'CHECK_LIMIT_AUTO' OR (@.CHECKSYMBOL =
'CHECK_LIMIT_MANUAL' AND @.CALCULATEMANUAL = 1)
-- then we have a placeholder template, so override some of the
values
-- If we have a placeholder template with a Template Reference,
override some of the values
-- NB: It's now possible to have placeholders without template
references stored in the DB, in which case the template
-- is determined at runtime by the CRS Server software. This is
part of CHG109212, June 2004.
IF ((@.ChecksTypeSymbol = 'CHECKS_AUTO') AND (@.TemplateRef IS
NOT NULL))
BEGIN
-- Get the placeholder's template column values using the
template ref checkID
SELECT
@.TemplateMeasureID = Checks.MeasureID,
@.TemplateChecksType = Checks.ChecksType,
@.TemplateValue1 = Checks.Value1,
@.TemplateValue2 = Checks.Value2,
@.TemplateMessage = Checks.Message,
@.TemplateMessageID = Checks.MessageID,
@.TemplateSeverity = Checks.Severity,
@.TemplateReviewDate = Checks.ReviewDate,
@.TemplateExpiryDate = Checks.ExpiryDate,
@.TemplateAssetID = Checks.AssetID,
@.TemplateActive = Checks.Active,
@.TemplateThreshold = Checks.Threshold,
@.TemplatePortfSymbol = DefnPortfolio.Symbol,
@.TemplateChecksTypeSymbol = C2.Symbol
FROM
Checks,Portfolio, DefnPortfolio, Class C2
WHERE Checks.CheckID = @.TemplateRef
AND Checks.PortfolioID = Portfolio.PortfolioID
AND DefnPortfolio.PfolDefnID = Portfolio.ClassID
AND C2.ClassID = Checks.ChecksType
-- Override placeholder's MESSAGE, SEVERITY, VALUE1,
VALUE2, REVIEWDATE, EXPIRYDATE, MEASUREID, and MESSAGEID values with
those
-- of its template
SET @.MeasureID = @.TemplateMeasureID
SET @.Value1 = @.TemplateValue1
SET @.Value2 = @.TemplateValue2
SET @.Message = @.TemplateMessage
SET @.MessageID = @.TemplateMessageID
SET @.Severity = @.TemplateSeverity
SET @.ReviewDate = @.TemplateReviewDate
SET @.ExpiryDate = @.TemplateExpiryDate
-- If the Template Limit is 'unlimited' then set the
CheckType in the returned (placeholder) row to 'unlimited' as well
IF (@.TemplateChecksTypeSymbol = 'CHECKS_UNLIMITED')
BEGIN
SET @.ChecksType = @.TemplateChecksType
END
-- Find out which control amount class (customer,parent or
ultimate) the template uses (e.g. match PFOL_CUSTOMER)
SELECT @.Ctrl_Amt_Class_Symbol =
dbo. GET_CTRL_AMT_SYMBOL(@.TemplatePortfSymbol
)
-- override the VALUE of the placeholder using the template
(and in certain cases the control amt)
-- " " ASSETID of the placeholder using either the
template's ASSETID (or in certain cases the ASSETID of the control
amount)
-- " " ACTIVE column of the placeholder so that if the
template is inactive the placeholder is also INACTIVE (or in certain
cases the ACTIVE column
IF @.Ctrl_Amt_Class_Symbol IS NULL --then there isn't a
control amount, so return the template value as actual values (i.e.
non-perecentages)
BEGIN
-- ...then return the template's value column as the
result
SET @.Value1 = @.TemplateValue1
SET @.Value2 = @.TemplateValue2
SET @.AssetID = @.TemplateAssetID
IF @.TemplateActive = 0 SET @.Active=0
END
ELSE
-- VALUE is the template's percentages * control
currency,
-- ASSETID is that of the control currency
-- ACTIVE is only true if the control currency,
template and placeholder are all active
BEGIN
-- first get information from the CustControlAmounts
table
SELECT @.Ctrl_Amt=Ctrl.Amount,
@.AssetID=Ctrl.AssetID,@.Ctrl_Active=Ctrl.Active
FROM dbo.CustControlAmounts Ctrl, dbo.Portfolio
PlaceholderPortf, dbo.Checks C, dbo.Class CL
WHERE --join conditions for getting the control
amount from CUSTCONTROLAMOUNTS.
--a row in CUSTCONTROLAMOUNTS is
idenitifed with a HOSTID,CUSTID and CLASSID
Ctrl.HostID = PlaceholderPortf.HostID
AND Ctrl.CustID = PlaceholderPortf.CustID
AND Ctrl.TypeID = CL.ClassID
AND CL.Symbol = @.Ctrl_Amt_Class_Symbol
AND PlaceholderPortf.PortfolioID =
C.PortfolioID
AND C.CheckID = @.CheckID
-- fix for BUG111446: if there is no control amount,
use values from the template
IF (@.AssetID IS NULL) OR (@.Ctrl_Amt IS NULL) OR
(@.Ctrl_Active IS NULL)
BEGIN
SET @.Value1 = @.TemplateValue1
SET @.Value2 = @.TemplateValue2
SET @.AssetID = @.TemplateAssetID
IF @.TemplateActive = 0 SET @.Active=0
END
ELSE
BEGIN
-- we do have a control amount, so act on it
accordingly
IF (@.Ctrl_Active = 0) OR (@.TemplateActive = 0)
SET @.Active = 0
-- if the placeholder is still considered active,
check for duplicate checks of the same measure
-- on the same portfolio
IF @.Active = 1
BEGIN
-- look for active ordinary/manual checks that
cover the same measure and portfolio
-- although there really shouldn;t be any of
these!!
SELECT @.DUPLICATE_CHECKCOUNT = COUNT(CheckID)
FROM dbo.Checks
WHERE DBO.GET_CHECKMEASUREID(CheckID) =
@.MeasureID
AND PortfolioID =
@.PortfolioID
AND CheckID <>
@.CheckID
AND Active =
1
-- if we found an active ordinary/manual check,
use that instead of the placeholder
IF @.DUPLICATE_CHECKCOUNT > 0
SET @.Active = 0
END
IF (@.Active = 1) OR (@.ShowInActive=1)
SELECT @.Value1 =
DBO. GET_PLACEHOLDERVALUE(@.TemplateChecksType
Symbol, @.TemplateValue1,
@.Ctrl_Amt)
END
END
END
-- If it's a 'dynamic' placeholder check (with no template)
then NULL out the limit value as an indication to the
-- calling softwatre that the value neds computing and has not
been done by this routine. This is part of CHG109212, June 2004.
IF ((@.ChecksTypeSymbol = 'CHECKS_AUTO') AND (@.TemplateRef IS
NULL))
BEGIN
SET @.Value1 = NULL
SET @.Value2 = NULL
END
-- Don't allow templates or inactive checks to appear in the
result set for deal violations
-- for browsing, all checks will appear
IF (@.ChecksForDealViolations=1 AND @.CheckSymbol <>
'CHECK_LIMIT_TEMPLATE' AND (@.Active=1 OR @.ShowInActive=1)) OR
(@.ChecksForDealViolations=0 AND (@.Active=1 OR @.ShowInActive=1))
BEGIN
-- Insert into result table the values (which have been
overriden in the case of placeholder checks)
INSERT INTO @.CHECKSTAB
SELECT @.CheckID, @.PortfolioID, @.ClassID, @.MeasureID,
@.ChecksType, @.Value1, @.Value2, @.Message, @.MessageID, @.Severity,
@.ReviewDate,
@.ExpiryDate, @.AssetID, @.TemplateRef,
@.IsConditional, @.Active, @.Threshold, @.HasTransfers
END
FETCH NEXT FROM CHECK_CURSOR INTO
@.CheckID, @.PortfolioID, @.ClassID, @.MeasureID, @.ChecksType,
@.Value1, @.Value2, @.Message, @.MessageID, @.Severity, @.ReviewDate,
@.ExpiryDate,
@.AssetID, @.TemplateRef, @.IsConditional, @.Active,
@.Threshold, @.HasTransfers, @.CheckSymbol, @.ChecksTypeSymbol
END -- while
CLOSE CHECK_CURSOR
DEALLOCATE CHECK_CURSOR
IF @.ChecksForDealViolations = 1
SELECT * FROM @.CHECKSTAB ORDER BY PortfolioID
ELSE --return a formatted view for browsing checks
SELECT
DefnPortfolio.PortfolioName AS Portfolio,
Checks.MESSAGE AS Legend, -- DummyColumn
Checks.CheckID,
Class.Name AS Type,
ChecksType,
DefnPortfolioMeasureClass.Name AS Measure,
-- Checks.Message Description, -- DummyColumn
Checks.Severity,
Checks.REVIEWDATE AS Review,
Checks.EXPIRYDATE AS Expiry,
Checks.PortfolioID,
Checks.ClassID,
Checks.Value1,
Checks.Value2,
Checks.Active,
Checks.Threshold,
Checks.HasTransfers,
dbo.Asset.Code AS Currency,
(SELECT COUNT(1)
FROM dbo.Violation
WHERE Violation.CheckID = Checks.CheckID) AS
Violations,
DefnPortfolioMeasureClass.MeasureID AS MeasureID,
Checks.AssetID,
IsConditional Conditional,
Checks.Message Details
FROM @.CHECKSTAB Checks
INNER JOIN dbo.Class
ON Checks.CLASSID = Class.ClassID
LEFT OUTER JOIN dbo.Asset
ON Checks.ASSETID = Asset.AssetID
LEFT OUTER JOIN dbo.DefnPortfolio
INNER JOIN dbo.Portfolio
ON DefnPortfolio.PfolDefnID = Portfolio.ClassID
ON Checks.PORTFOLIOID = Portfolio.PortfolioID
LEFT OUTER JOIN dbo.DefnPortfolioMeasureClass
ON Checks.MEASUREID =
DefnPortfolioMeasureClass.MeasureID
END
