August 30, 2004 at 8:10 am
I have been tasked with creating a query that will extract the credit score received for an account from a string of data for a specific date.
The table that I am working with (CreditScoreQueue) has the following columns:
ID, Account#, Queuetime, CCReceive (this column contains the string of data which holds the creditscore for each account)
I can run a query on this table to get an idea of the accounts that were put in the queue for a specific date. Example query code below:
select c.*
from CreditScoreQueue c
where queuetime between '2004-08-26' and '2004-08-27'
-----------------------------------------------------
However, extracting an account’s score is a bit more difficult because it is imbedded in a big string of data. I already have a stored Procedure which can extract the credit score for a specific account if you know that account’s ID (which is a field that can be acquired from the Credit_Score_Queue table). Example of Stored Procedure and how to get one accounts score if you type in a specific ID:
Declare @CCRecv varchar(4000)
Declare @CreditScore nvarchar(5)
Declare @ErrorText nvarchar(1000)
select @CCRecv = CCReceive
From CreditScoreQueue
--where ID = 210
where ID = 12471
exec ieuser.ecsp_DecodeCCOutput @CCRecv, @CreditScore output, @ErrorText output
Print @CreditScore + ' - ' + @ErrorText
-----------------------------------------------------
I’ve been told that if I know how to write a cursor, I could run the first select query above to get all the ID’s for accounts with a QueueTime on
August 30, 2004 at 8:57 am
This should work:
Declare @CCRecv varchar(4000)
Declare @CreditScore nvarchar(5)
Declare @ErrorText nvarchar(1000)
DECLARE my_cursor CURSOR FOR
select c.CCReceive
from CreditScoreQueue c
where queuetime between '2004-08-26' and '2004-08-27'
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @CCRecv
WHILE @@FETCH_STATUS = 0
BEGIN
exec ieuser.ecsp_DecodeCCOutput @CCRecv, @CreditScore output, @ErrorText output
Print @CreditScore + ' - ' + @ErrorText
FETCH NEXT FROM my_cursor
INTO @CCRecv
END
CLOSE my_cursor
DEALLOCATE my_cursor
August 30, 2004 at 10:05 am
Server: Msg 16924, Level 16, State 1, Line 40
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
I received the above error message. I modified the cursor to do the following:
Declare @TransUnionRecv varchar(4000)
Declare @CreditScore nvarchar(5)
Declare @ErrorText nvarchar(1000)
Declare my_cursor CURSOR FOR
Select i.accountnumber, t.infiniteid, rtrim(ltrim(ISNULL (c.Business, '')))
+ltrim(rtrim(ISNULL(c.[first],'')))
+' '
+rtrim(ISNULL (c.[last], '')) 'Customer_Name',+ltrim(rtrim(ISNULL(streetnumber, '')))
+' '
+ltrim(rtrim(ISNULL(StreetDirection,'')))
+' '
+ltrim(rtrim(ISNULL(StreetName, '')))
+' '
+ltrim(rtrim(ISNULL(q.name, '')))
+' '
+ltrim(rtrim(ISNULL(StreetSuffix, '')))
+' '
+ltrim(rtrim(ISNULL(Structure, '')))
+', '
+ltrim(rtrim(ISNULL(City, '')))
+', '
+ltrim(rtrim(ISNULL(y.name, '')))
+' '
+ltrim(rtrim(ISNULL(cast(ZipCode as varchar(5)),'')))'Service_Address', t.QueueTime, TransUnionRecv
from TUCreditScoreQueue t
left join infiniteaccount i on t.infiniteid=i.systemid
left join xr_infiniteaccount_aglaccount x on (x.infiniteaccountid= i.systemid)
left join AGLaccount a on (a.systemid= x.aglaccountid)
left join customername c on (c.systemid= a.customername)
left join serviceaddress v on (v.systemid=a.serviceaddress)
left join lu_thoroughfare q on q.value=v.thoroughfare
left join lu_state y on y.value=v.state
where QueueTime between '2004-08-27' and '2004-08-28'
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @TransUnionRecv
WHILE @@FETCH_STATUS = 0
BEGIN
exec ieuser.ecsp_DecodeCCOutput @TransUnionRecv, @CreditScore output, @ErrorText output
Print @CreditScore + ' - ' + @ErrorText
FETCH NEXT FROM my_cursor
INTO @TransUnionRecv
END
CLOSE my_cursor
DEALLOCATE my_cursor
I can't seem to figure out where my mistake was...
August 30, 2004 at 2:40 pm
In your declare cursor statement, your select statement is returning many fields. For each field in this select list, you must supply a variable in the FETCH NEXT statement to hold the value of the field in the same order as they appear in the select list.
For your code above, BOTH of your fetch next statements would be
FETCH NEXT FROM my_cursor
INTO @AccountNumber, @Infiniteid, @Address, @QueueTime, @TransUnionRecv
August 31, 2004 at 9:08 am
If you are trying to develop a single comprehensive select list with all the data, the cursor approach above would require using a temporary table to gather all the data one line at a time (instead of the print statement) and then a select * from the temp table and the end of the procedure. If that type of a resultset is your objective you may consider creating a function version of your stored procedure and calling it directly from a SELECT. If the existing SP is still needed for other purposes and you don't want to duplicate the code you could call the function from that SP as well (although a smidge more overhead).
Note that although understanding how to use a cursor is important (they definately have their time and place) they can have a tendancy to impose unneccessary performance issues when there is a better approach available.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply