How to place conditions in CURSOR
-
What I'm trying to do with these table examples is place conditions on the rows. I've tried using IF THEN, CASE, and just recently tried to understand using CURSOR to get the job done. After reading about CURSOR move I think it is what I will need to get the conditions I'm looking for. Basically what I'm trying to do is build conditions on a row that will refer to another row if the conditions = True.
For example: With Ace Hardware there are four rows ordered by the date descending, I'm trying to write a condition that would look at the first row, and if it is 'SaleInTerm' I want the statement to loop through that particular group of Ace Hardware and look for a Cancellation. If it finds a Cancellation I want that row to be the only one selected and displayed.
Ace Hardware SaleInTerm 2004-04-12 00:00:00.000
Ace Hardware SaleInTerm 2004-06-30 00:00:00.000
Ace Hardware SaleInTerm 2004-07-26 00:00:00.000
Ace Hardware Cancellation 2004-07-26 00:00:00.000
For A & H I would want the condition to state that IF it finds 'SaleInTerm' I want the statement to
A & H SaleInTerm 2003-11-11 00:00:00.000
A & H RenewInTerm 2004-03-18 00:00:00.000
For Western Plains I would be Looking for a Condition to state that IF it finds 'SaleExpired' to loop through and look for 'RenewInTerm' and then if it doesn't find that, to loop through and look for 'SaleInTerm' and display the result that = True ELSE display the 'SaleExpired' Row.
Western Plains SaleExpired 1999-06-30 00:00:00.000
Western Plains RenewInTerm 2003-12-11 00:00:00.000
Western Plains SaleInTerm 2004-01-21 00:00:00.000
Back to Ace Hardware, minus the Cancellation, I'm trying to come up with a statement that would state that IF 'SaleInTerm' to loop through and look for 'RenewInTerm' and if it can't find a 'RenewInTerm' to select the 'SaleInTerm' with the "Oldest Date" and only display that Row.
Ace Hardware SaleInTerm 2004-04-12 00:00:00.000
Ace Hardware SaleInTerm 2004-06-30 00:00:00.000
Ace Hardware SaleInTerm 2004-07-26 00:00:00.000
I've tried using CASE and IF THEN statements, but those do not look row by row, only at the current row. Furthermore, I'm quite new to SQL Server and have tried to use CURSOR with IF THEN statements, but I can't seem to build it correctly and the query often fails. I'm resorting to advise because I've searched for examples on the Internet, and have read the SQL Cursors chapter over and over in the "SQL Server 2000 Programming" book written by Robert Vieira from WROX and just can't seem to get a handle on it. Any advice or information would be greatly appreciated thanks.
-
I use a GOTO for the next record. There are better ways, I am just stuck in the mud - not that big a deal.
This "may" help as an outline.... I put the records I want into a #TempTable to be passed back as a recordset. I assumed that is what you wanted...
If your tables have Primary Keys you may be better off with a WHILE loop instead of the overhead of CURSOR (unless you have tons of data).
I can post an outline of that as well if you would like.
DECLARE @Field1 datatype,
@Field2 datatype,
...
DECLARE GetResults FAST_FORWARD CURSOR FOR
SELECT Field1, Field2...
FROM TablesOfChoice
WHERE ConstraintsYouNeed
OPEN GetResults
next_record:
FETCH NEXT FROM ihe_scores INTO
@Field1, @Field2,...
IF @@FETCH_STATUS = 0
BEGIN
IF 'Condition of Interest' -- this should work for your criterion....
BEGIN
INSERT INTO #TempTable( Field1, Field2,... )
SELECT
CASE
WHEN @Field1 = 'Condition of Interest'
THEN 'New Value'
ELSE @Field1
END,
CASE
WHEN @Field2 = 'Condition of Interest'
THEN 'New Value'
ELSE @Field2
END,
.....
GOTO next_record
END
CLOSE GetResults
DEALLOCATE GetResults
I wasn't born stupid - I had to study.
-
Well, I tried to keep the syntax that same as how you had it, but ended up having to modify it quite a bit to get it to work. this is the query I used in order to get it to run:
USE SurgeDB
DECLARE @CustName varchar(255),
@TranTypeName varchar(255),
@TranDate datetime
DECLARE SurgeCursor CURSOR
FAST_FORWARD
FOR
SELECT CustName, TranTypeName, TranDate
FROM TotalCustTran
WHERE CustName = '1 Contractors Solutions'
OPEN SurgeCursor
FETCH NEXT FROM SurgeCursor INTO
@CustName, @TranTypeName, @TranDate
IF @@FETCH_STATUS = 0
BEGIN
IF @TranTypeName = 'SaleExpired'
BEGIN
INSERT INTO TempTable( CustName, TranTypeName, TranDate )
SELECT @CustName, CASE
WHEN @TranTypeName = 'SaleExpired'
THEN 'RenewInTerm'
ELSE @TranTypeName
END,
@TranDate
END
FETCH NEXT FROM SurgeCursor INTO
@CustName, @TranTypeName, @TranDate
END
CLOSE SurgeCursor
DEALLOCATE SurgeCursor
I did this on a single "CustName" with name of 1 Contractors Solutions, just to use as a testing record. This is what the actual data looks like for this particular Record set:
1 Contractors Solutions SaleExpired 2001-03-30 00:00:00.000
1 Contractors Solutions RenewInTerm 2003-10-20 00:00:00.000
The query Returned a single Row which is what i'm trying to get, but not quite the information i'm looking for basically what it did is what a CASE statement is supose to do just changing the value on the current row, seems that i'm close but no cigar, I'll continue to ponder this problem.
1 Contractors Solutions RenewInTerm 2001-03-30 ...
I glad your helping through and I appreciate it.
-
I may not be clear, but it looks like you wanted the most recent record.
Try SELECT TOP 1 Field1, Field2... FROM Table ORDER BY Field2
You have no WHERE condition and that may be what is needed....
Glad you dumped the GOTO....
I wasn't born stupid - I had to study.
-
well, not really looking for the most current record, I can get that with a sime MAX(column) and GROUP BY function, what i'm trying to build is a condition that will look at a row, and based on that row is either going to select it, or go find another row and select and display that one, let me give another sample of the table i'm working with:
CustName TranTypeName TranDate 1 Contractors Solutions SaleExpired 3/30/2001 1 Contractors Solutions RenewInTerm 10/20/2003 A & H Turf SaleExpired 1/3/2002 A & H Turf SaleInTerm 11/11/2003 A & H Turf RenewInTerm 3/18/2004 Ace Hardware SaleInTerm 4/12/2004 Ace Hardware SaleInTerm 6/30/2004 Ace Hardware SaleInTerm 7/26/2004 Coldwell Banker SaleExpired 2/10/2000 Coldwell Banker SaleExpired 4/12/2000 Coldwell Banker RenewInTerm 10/18/2004 Coldwell Banker SaleNoTerm 2/3/2005 The Rows in Blue are the only ones I want to be selected and displayed, notice Ace Hardware and Coldwell Banker for example.
here is some Theoretical Code that i've come up with, Just getting it to work is the problem.
IF TranTypeName = 'SaleExpired'
THEN SELECT CustName, TranTypeName, TranDate
FROM TotalCustTran
WHERE TranTypeName = 'RenewInTerm'
ELSE SELECT CustName, TranTypeName, TranDate
FROM TotalCustTran
WHERE TranTypeName = 'SaleInTerm'
END
--//Here is were my sutto code gets tough
IF TranTypeName = 'SaleInTerm'
THEN SELECT CustName, TranTypeName, TranDate
FROM TotalCustTran
WHERE TranTypeName = 'RenewInTerm'
--//This is where I get really stumped, Trying to get Oldest TranDate
--//with TranTypeName of 'SaleInTerm': Ace Hardware, for example.
ELSE SELECT CustName, TranTypeName, TranDate
FROM TotalCustTran
WHERE TranTypeName = 'SaleInTerm' and TranDate = MIN(TranDate)
GROUP BY CustName, TranTypeName, TranDate
FETCH NEXT --//IF using CURSOR
--//Then I want it to loop through each group and row some how
Call me an newbie, but I just can't figure how to get this thing to work, any help would be great, thanks
-
To return the oldest trandate with TranTypeName of 'SaleInTerm', try this:
SELECT CustName, TranTypeName, TranDate
FROM TotalCustTran A
WHERE TranTypeName = 'SaleInTerm' AND TranDate = (SELECT MIN(TranDate) FROM TotalCustTran B WHERE A.CustName = B.CustName AND B.TranTypeName = 'SaleInTerm' )
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply