August 20, 2009 at 1:36 pm
For some reason my cursor only reads one record, it does not loop (the TRL file contains 3 recs. Can someone tell me what I am missing?
DECLARE @Run_Date as int
DECLARE @DTL_CNT as int
DECLARE @TRL_CNT as int
DECLARE @File_Cnt as int
DECLARE @Vend_Name as varchar(20)
DECLARE @File_Name as varchar(255)
DECLARE @TRL_Run_Date as int
----***XXX
SET @Run_Date = Convert(Varchar(10),Getdate(),112)
Print @Run_Date
Set @File_Cnt = (Select count(*) FROM Trlr
WHERE TRLR.RunDate = @Run_Date And Trlr.Vendor_Name = 'XXX')
PRINT @File_Cnt
Declare vwCursor CURSOR FOR
SELECT RunDate, Vendor_Name, Vendor_File_Name, Dtl_Count
FROM Trlr
WHERE TRLR.RunDate = @Run_Date And Trlr.Vendor_Name = 'XXX'
open vwCursor
FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT
SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, count(*) as RecCnt
into #tmp_recs_cnt_C
FROM Dtl A
Where A.Vendor_Name = 'XXX' and A.Vendor_File_Name = @File_Name
AND A.RunDate = @Run_Date
group by A.RunDate, A.Vendor_File_Name, A.Vendor_Name
select Vendor_Name from Dtl where Vendor_File_Name = @File_Name
select Vendor_Name from Dtl where Vendor_Name = 'XXX'
select RUndate from Dtl where rundate = @Run_Date
select * from #tmp_recs_cnt_C
INSERT INTO Trlr_Errors (RunDate, Vendor_File_Name, Vendor_Name, dtl_count, T.RecCnt)
SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, A.dtl_count,T.RecCnt
FROM Trlr A
INNER JOIN #tmp_recs_cnt_C T
ON A.RunDate=T.RunDate AND A.Vendor_File_Name=T.Vendor_File_Name AND A.Vendor_Name = T.Vendor_Name
--AND A.dtl_count<>@TRL_CNT
AND A.dtl_count=@TRL_CNT
FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT
CLOSE vwCursor
DEALLOCATE vwCursor
Thanx
August 20, 2009 at 1:46 pm
There isn't a loop in the code. Try adding the 3 lines below.
FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT
WHILE @@FETCH_STATUS = 0 --ADD THIS
BEGIN --ADD THIS
SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, count(*) as RecCnt
into #tmp_recs_cnt_C
FROM Dtl A
Where A.Vendor_Name = 'XXX' and A.Vendor_File_Name = @File_Name
AND A.RunDate = @Run_Date
group by A.RunDate, A.Vendor_File_Name, A.Vendor_Name
select Vendor_Name from Dtl where Vendor_File_Name = @File_Name
select Vendor_Name from Dtl where Vendor_Name = 'XXX'
select RUndate from Dtl where rundate = @Run_Date
select * from #tmp_recs_cnt_C
INSERT INTO Trlr_Errors (RunDate, Vendor_File_Name, Vendor_Name, dtl_count, T.RecCnt)
SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, A.dtl_count,T.RecCnt
FROM Trlr A
INNER JOIN #tmp_recs_cnt_C T
ON A.RunDate=T.RunDate AND A.Vendor_File_Name=T.Vendor_File_Name AND A.Vendor_Name = T.Vendor_Name
--AND A.dtl_count@TRL_CNT
AND A.dtl_count=@TRL_CNT
END --ADD THIS
August 20, 2009 at 3:04 pm
What is the specific reason for using a cursor in this case?
You can join the TRLR table within your insert statement.
Also you should think about replacing the temp table #tmp_recs_cnt_C with a sub-query or a CTE within your insert statement.
To create a temp table multiple times (SELECT INTO within a WHILE loop) would require to drop the temp table before recreating it (each and every run within your loop...).
Over all, that cursor doesn't look like to perform well even when talking about a small-size data volume...
August 20, 2009 at 3:15 pm
First in addition to adding a WHILE (condition) BEGIN END to your code, you will need a second FETCH NEXT just before the END in the WHILE.
However, I may be wrong as I don't have the DDL for the tables or any sample data, but I don't think you need a cursor or a loop. the following code appears to do just what you need in a set based manner:
INSERT INTO Trlr_Errors (RunDate, Vendor_File_Name, Vendor_Name, dtl_count, T.RecCnt)
SELECT
A.RunDate,
A.Vendor_File_Name,
A.Vendor_Name,
A.dtl_count,
T.RecCnt
FROM
Trlr A
INNER JOIN #tmp_recs_cnt_C T
ON (A.RunDate = T.RunDate AND
A.Vendor_File_Name = T.Vendor_File_Name AND
A.Vendor_Name = T.Vendor_Name)
August 20, 2009 at 3:22 pm
oook, I revised the code and it's still reading only the 1st record only now it's reading it over and over and......
Here is what I have:
DECLARE @Run_Date as int
DECLARE @DTL_CNT as int
DECLARE @TRL_CNT as int
DECLARE @File_Cnt as int
DECLARE @Vend_Name as varchar(20)
DECLARE @File_Name as varchar(255)
DECLARE @TRL_Run_Date as int
----***XXX
SET @Run_Date = Convert(Varchar(10),Getdate(),112)
Print @Run_Date
Set @File_Cnt = (Select count(*) FROM Trlr
WHERE TRLR.RunDate = @Run_Date And Trlr.Vendor_Name = 'XXX')
PRINT @File_Cnt
Declare vwCursor CURSOR FOR
SELECT RunDate, Vendor_Name, Vendor_File_Name, Dtl_Count
FROM Trlr
WHERE TRLR.RunDate = @Run_Date And Trlr.Vendor_Name = 'XXX'
open vwCursor
Print 'V Info'
print @Vend_Name
Print @TRL_Run_Date
Print @File_Name
Print @TRL_CNT
Print 'End Info'
FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT
WHILE @@FETCH_STATUS = 0 --ADD THIS
BEGIN --ADD THIS
SET @DTL_CNT = (SELECT count(*)
--into #tmp_recs_cnt_C
FROM Dtl A
Where A.Vendor_Name = 'XXX' and A.Vendor_File_Name = @File_Name
AND A.RunDate = @Run_Date
group by A.RunDate, A.Vendor_File_Name, A.Vendor_Name)
Print @DTL_CNT
select Vendor_Name from Dtl where Vendor_File_Name = @File_Name
select Vendor_Name from Dtl where Vendor_Name = 'XXX'
select RUndate from Dtl where rundate = @Run_Date
--select * from #tmp_recs_cnt_C
INSERT INTO Trlr_Errors (RunDate, Vendor_File_Name,
Vendor_Name, dtl_count, RecCnt)
SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, A.dtl_count, @TRL_CNT
FROM Trlr A
--INNER JOIN #tmp_recs_cnt_C T ON
WHERE A.RunDate= @Run_Date AND A.Vendor_File_Name=@File_Name AND A.Vendor_Name = 'XXX'
--AND A.dtl_count@TRL_CNT
AND A.dtl_count=@TRL_CNT
END
FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT
CLOSE vwCursor
DEALLOCATE vwCursor
August 20, 2009 at 3:29 pm
See above your last post.
August 20, 2009 at 3:33 pm
I don't see why you're even using a cursor in the first place. A cursor is horribly inefficient. You're just not noticing it, because there are so few records being processed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 20, 2009 at 3:51 pm
Thanx to everyone for responding. for those who think using a cursor is a horrible idea you point is well taken. However I am a VBA programmer and very new to SQL and looping thru records is what I know.
IMU92 suggested joining the Trlr table. I assume he means joining the TRLR table to the detail table. When I tried that it worked ok as long as at least one detail rec was present. If the User sent a rec count of 2 and 0 detail records then no error rec was written.
Having said that I am always willing to learn as long as I understand the code. Because I will have to understand it to maintain it.
Thanx again,
August 20, 2009 at 4:00 pm
That would occur if you use an INNER JOIN. If there is a possibility of there not being a detail record, then you would change that to a LEFT OUTER JOIN, that way you get all the header information even when there is no detail information.
August 20, 2009 at 4:14 pm
Not sure what you mean.
This may not be the smartest question I'll ask in my life time. But here goes:
I have 3 trailers, one trl doesn't have any dtl recs. Unless I do an unmatched qry how will I know which trl doesn't have dtl?
Not sure how to write that one.
August 20, 2009 at 4:25 pm
trudye10 (8/20/2009)
Not sure what you mean.This may not be the smartest question I'll ask in my life time. But here goes:
I have 3 trailers, one trl doesn't have any dtl recs. Unless I do an unmatched qry how will I know which trl doesn't have dtl?
Not sure how to write that one.
Can you post the DDL (CREATE TABLE statements) and sample data for the two tables? Also, show us what your result set should be as well. It will be easier to help if we have something to work with.
August 20, 2009 at 5:27 pm
here are the relevant fields
Dtl]
[RunDate] [varchar](8)
[Vendor_File_Name] [varchar](500)
[Vendor_Name] [varchar](20)
[Tran_Type] [varchar](2)
[Filler8] [varchar](150)
Trlr]
[RunDate] [varchar](8)
[Vendor_Name] [varchar](20)
[Rec_Type] [varchar](2)
[Vendor_ID] [varchar](2)
[Dtl_Count] [numeric](5, 0)
[Vendor_File_Name] [varchar](500)
Trlr_Errors]
[RunDate] [varchar](8)
[Vendor_File_Name] [varchar](500)
[Vendor_Name] [varchar](20)
[dtl_count] [numeric](5, 0)
[RecCnt] [int]
After I thought about it I realized the trl that had no record sthe dtl count would be zero. DUH!!
Thank you so much for all your help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply