June 24, 2010 at 10:58 am
Hello,
I need to be able to loop through a "primary table" and pull out the "cases" that have been completed in the last 24 hours. Using the CaseNumber that is associated with that table I need to loop through another table which has a one to many relationship and pull out the correct cases. In the end it I need an output that looks like this:
Primary Table
Secondary Table
*
*
Primary
Secondary Table
*
*
I have not used cursors before but have one set up that will pull the primary tables but am not sure how to pass the CaseNumber to the second loop. Any help would be great.
The code I am using is as follows but am not sure if this is the right way to go about this:
DECLARE @CaseNumber varchar(50)
DECLARE @RowNum int
DECLARE DataDump CURSOR FOR
SELECT CaseNumber from CityCollection.dbo.FileInfo
WHERE CityCollection.dbo.FileInfo.FileDone = -1 AND CityCollection.dbo.FIleinfo.DateFinished between Getdate() and Getdate() -1
OPEN DataDump
FETCH NEXT FROM DataDump
INTO @CaseNumber
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + @CaseNumber
FETCH NEXT FROM DataDump
INTO @CaseNumber
END
CLOSE DataDump
DEALLOCATE DataDump
June 24, 2010 at 11:09 am
george.greiner (6/24/2010)
...I have not used cursors before...
And I believe, you don't need to use the cursor in this case as well 😀
Please provide some DDL script to setup tables and some data.
Why do you want to PRINT something there? Why not returning the formatted resultset to the client/caller?
June 24, 2010 at 11:26 am
Eugene Elutin (6/24/2010)
george.greiner (6/24/2010)
...I have not used cursors before...And I believe, you don't need to use the cursor in this case as well 😀
Please provide some DDL script to setup tables and some data.
Why do you want to PRINT something there? Why not returning the formatted resultset to the client/caller?
Well it needs to be in a text file but I was just screwing around with some code to get some results as I have not had to do much outside of using TSQL to format data. Currently I use Access for this function but it is much too slow as the loop I had written takes 20min at min as I only provided 2 tables of the 8 that all have 1 to many relationships as after I figure out how to pass the first one the other ones should be easy =).
June 24, 2010 at 11:43 am
george.greiner (6/24/2010)
Eugene Elutin (6/24/2010)
george.greiner (6/24/2010)
...I have not used cursors before...And I believe, you don't need to use the cursor in this case as well 😀
Please provide some DDL script to setup tables and some data.
Why do you want to PRINT something there? Why not returning the formatted resultset to the client/caller?
Well it needs to be in a text file but I was just screwing around with some code to get some results as I have not had to do much outside of using TSQL to format data. Currently I use Access for this function but it is much too slow as the loop I had written takes 20min at min as I only provided 2 tables of the 8 that all have 1 to many relationships as after I figure out how to pass the first one the other ones should be easy =).
1. There is no problem to place results from resultset into a file
2. Loop takes 20 min to run? That why, I am telling you that you don't need a cursor!
As you too lazy to provide what asked (setup DDL and data scripts), I cannot test what I will give you, may be you will understand a concept:
;with cte
as
(
select p.CaseNumber
,null as s1SomeColumn
,null as s2SomeColumn
,0 as ord
from PrimaryTable p
union all
select p.CaseNumber, s1.SomeColumn, null, 1 as ord
from PrimaryTable p
left join SecondaryTable1 s1 on s1.keyCol = p.keyCol
union all
select p.CaseNumber, null, s2.SomeColumn, 2 as ord
from PrimaryTable p
left join SecondaryTable2 s2 on s2.keyCol = p.keyCol
)
select CASE WHEN ord = 0 then 'Primary Table Case Number: ' + CaseNumber
WHEN ord = 1 then 'Secondary Table 1 Some Column: ' + s1SomeColumn
WHEN ord = 2 then 'Secondary Table 2 Some Column: ' + s2SomeColumn
ELSE NULL
END as FormattedOutput
from cte
order by CaseNumber, ord
June 24, 2010 at 11:45 am
Eugene Elutin (6/24/2010)
george.greiner (6/24/2010)
Eugene Elutin (6/24/2010)
george.greiner (6/24/2010)
...I have not used cursors before...And I believe, you don't need to use the cursor in this case as well 😀
Please provide some DDL script to setup tables and some data.
Why do you want to PRINT something there? Why not returning the formatted resultset to the client/caller?
Well it needs to be in a text file but I was just screwing around with some code to get some results as I have not had to do much outside of using TSQL to format data. Currently I use Access for this function but it is much too slow as the loop I had written takes 20min at min as I only provided 2 tables of the 8 that all have 1 to many relationships as after I figure out how to pass the first one the other ones should be easy =).
1. There is no problem to place results from resultset into a file
2. Loop takes 20 min to run? That why, I am telling you that you don't need a cursor!
As you too lazy to provide what asked (setup DDL and data scripts), I cannot test what I will give you, may be you will understand a concept:
;with cte
as
(
select p.CaseNumber
,null as s1SomeColumn
,null as s2SomeColumn
,0 as ord
from PrimaryTable p
union all
select p.CaseNumber, s1.SomeColumn, null, 1 as ord
from PrimaryTable p
left join SecondaryTable1 s1 on s1.keyCol = p.keyCol
union all
select p.CaseNumber, null, s2.SomeColumn, 2 as ord
from PrimaryTable p
left join SecondaryTable2 s2 on s2.keyCol = p.keyCol
)
select CASE WHEN ord = 0 then 'Primary Table Case Number: ' + CaseNumber
WHEN ord = 1 then 'Secondary Table 1 Some Column: ' + s1SomeColumn
WHEN ord = 2 then 'Secondary Table 2 Some Column: ' + s2SomeColumn
ELSE NULL
END as FormattedOutput
from cte
order by CaseNumber, ord
It has nothing to do with laziness I did not know what DDL script meant and was researching it =0. Thank you for your help.
June 24, 2010 at 12:54 pm
george.greiner (6/24/2010)
I did not know what DDL script meant and was researching it =0. Thank you for your help.
George, please see the first link in my signature. If you read and follow it, and post not only what this requests, but also what your want you results to be like based upon the sample data then there will be many folks here that will be willing to assist you in doing your task without resorting to any loops or cursors.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 24, 2010 at 1:00 pm
WayneS (6/24/2010)
george.greiner (6/24/2010)
I did not know what DDL script meant and was researching it =0. Thank you for your help.George, please see the first link in my signature. If you read and follow it, and post not only what this requests, but also what your want you results to be like based upon the sample data then there will be many folks here that will be willing to assist you in doing your task without resorting to any loops or cursors.
Thanks!
June 24, 2010 at 3:21 pm
You could also do something like this:
CREATE TABLE combined_table_for_export (
id int identity(1,1) not null,
case# <datatype> not null,
sec_seq# int not null,
data varchar(500) not null
)
INSERT INTO combined_table_for_export
(case#, sec_seq#, data)
SELECT Case#, 0 AS sec_seq#, <other_cols_you_need_from_primary_table_concat_into_one_string> AS data
FROM [Primary Table]
UNION ALL
SELECT Case#, sec_table_id_or_seq_#
<other_cols_you_need_from_primary_table_concat_into_one_string>
FROM [Secondary Table]
ORDER BY Case#, sec_seq#
Then export the Case# and data columns from that table to a flat file, ordered by id.
Not pretty, but it should work for a quick-and-dirty method.
Scott Pletcher, SQL Server MVP 2008-2010
June 29, 2010 at 8:47 am
scott.pletcher (6/24/2010)
You could also do something like this:CREATE TABLE combined_table_for_export (
id int identity(1,1) not null,
case# <datatype> not null,
sec_seq# int not null,
data varchar(500) not null
)
INSERT INTO combined_table_for_export
(case#, sec_seq#, data)
SELECT Case#, 0 AS sec_seq#, <other_cols_you_need_from_primary_table_concat_into_one_string> AS data
FROM [Primary Table]
UNION ALL
SELECT Case#, sec_table_id_or_seq_#
<other_cols_you_need_from_primary_table_concat_into_one_string>
FROM [Secondary Table]
ORDER BY Case#, sec_seq#
Then export the Case# and data columns from that table to a flat file, ordered by id.
Not pretty, but it should work for a quick-and-dirty method.
If I am not mistaken if using UNION ALL the tables would have to have the same amount of fields? In this instance this is not the case an example of what an output would look like is as follows:
A|09-0403|062195300|ALL THAT CERTAIN lot or piece of ground with the buildings and improvements thereon erected, lot or piece of ground with the buildings and improvements thereon erected, described according to a sruvey thereof made by Joseph Johnson, Esq., Surveyor and Regulator of the 11th District as follows, to wit:
SITUATE on the Northeasterly sside of Lediy Avenue at the distance of 310 feet 2-5/8 inches Northwestwardly from the point of intersection of the said Northeastwardly side of Leidy Avenue with the North side of Girard Avenue.
CONTAINING in front or breadth on the said Leidy Avenue 17 feet and extending of that breadth in length or depth Northeastwardly between lines at right angles to the said Leidy Avenue, 160 feet to Viola Street.
BEING the same premises which Robert H. Mitchell and O'Kella E. Mitchell, h/w by Indenture bearing the date 10/21/1974 and recorded 02/14/1975 at Philadelphia, PA in Deed Book DCC 803 - 221, granted and conveyed to Christ Community Baptist Church, a PA Non-Profit Corporation.
Known By BRT as: 4131 LEIDY AVE
06-2-195300|03272009|Vacant land -- address not adequate for notice of foreclosure.
Possible City Transfer Tax due if Grantee was not granted exemption claimed in 1974 Deed.
Comm of PA to be notified of foreclosure for possible outstanding tax liability (corporate or fiduciary record owner).[1]098 N 01 - 059[2][3]-[4][5]310'2 5/8"" W GIRARD" 17'X160' KITCHEN 2 FAMILY 17'X160'[6]-[7]0[8]|
A1||00050002|375.77|03312000||
A1||02011152|372.80|12182001||
A1||03050708|640.09|04242003||
B|O|||||Christ Community Baptist Church, a PA Non-Profit Corporation|||||||1224 North 41st Street||Philadelphia|PA|19104||DCC 803 - 221|117|
B3|Deed|1800|10211974|02141975|DCC 803 - 221|Christ Community Baptist Church, a PA Non-Profit Corporation|51|
C|G|Robert H. Mitchell and O'Kella E. Mitchell, h/w|
C|RB|CHRIST COMMUNITY BAPTIST CHURCH|
C|TA||
Each "Line" is a dataset. This output is from Access and the way I run it is by creating datasets, opening the first and looping through them all. Each "FILE" starts with A and end with D (there were no records in that recordset for this particular file in dataset D). So if there was second "FILE" that needed to be distributed it would appear right after the last C line and that line would start with an A and so on.
This was not my idea and it is the way our client wants us to distribute flat files to them and it is so slow in Access and the reason why I need it converted to T SQL and then into SSIS so I can run this automatically every night.
June 29, 2010 at 9:29 am
george.greiner (6/24/2010)
...
It has nothing to do with laziness I did not know what DDL script meant and was researching it =0. Thank you for your help.
...
Sorry George, I might sounded a bit rude.
But, you know the setup scripts would really help us.
I can asure you, what you want to do is achievable without using loop or cursor even when having different number of columns of different datatypes in different tables:
DECLARE @TableA TABLE (AID int, ColA1 int, ColA2 varchar(25))
DECLARE @TableB TABLE (BID int, AID int, ColB1 char(50), ColB2 datetime, ColB3 money)
DECLARE @TableC TABLE (CID int, AID int, ColC1 varchar(10))
insert into @TableA
select 1, 100, 'AA row 1'
union select 11, 110, 'AA row 2'
insert into @TableB
select 21, 1, 'BB row 1A1', GETDATE(), 22.22
union select 22, 1, 'BB row 2A1', GETDATE() - 1, 222.44
union select 210, 11, 'BB row 1A2', GETDATE() - 2, 2222.66
union select 220, 11, 'BB row 2A2', GETDATE() - 4, 22222.88
insert into @TableC
select 31, 1, 'CC row 1A1'
union select 32, 1, 'CC row 2A1'
union select 310, 11, 'CC row 1A2'
union select 320, 11, 'CC row 2A2'
;WITH outpQ
AS
(
SELECT 'A' AS Ord, AID, 'A|' + CAST(ColA1 AS VARCHAR(11)) + '|' + ColA2 AS Extract
FROM @TableA
UNION ALL
SELECT 'B', A.AID, 'B|' + CAST(B.BID AS VARCHAR(11)) + '|' + RTRIM(B.ColB1) + '|' + CONVERT(VARCHAR(30), B.ColB2) + '|' + CAST(ColA2 AS VARCHAR(20)) AS Extract
FROM @TableA A
JOIN @TableB B ON B.AID = A.AID
UNION ALL
SELECT 'C', A.AID, 'C|' + CAST(C.CID AS VARCHAR(11)) + '|' + C.ColC1 AS Extract
FROM @TableA A
JOIN @TableC C ON C.AID = A.AID
)
SELECT Extract FROM outpQ ORDER BY AID, Ord
The above may not be exact way you should follow but it should give you an idea how you can achieve your extract without using cursor.
May I call you stubborn? Sorry for rudeness again 😀
June 29, 2010 at 9:34 am
Eugene Elutin (6/29/2010)
george.greiner (6/24/2010)
...
It has nothing to do with laziness I did not know what DDL script meant and was researching it =0. Thank you for your help.
...
Sorry George, I might sounded a bit rude.
But, you know the setup scripts would really help us.
I can asure you, what you want to do is achievable without using loop or cursor even when having different number of columns of different datatypes in different tables:
DECLARE @TableA TABLE (AID int, ColA1 int, ColA2 varchar(25))
DECLARE @TableB TABLE (BID int, AID int, ColB1 char(50), ColB2 datetime, ColB3 money)
DECLARE @TableC TABLE (CID int, AID int, ColC1 varchar(10))
insert into @TableA
select 1, 100, 'AA row 1'
union select 11, 110, 'AA row 2'
insert into @TableB
select 21, 1, 'BB row 1A1', GETDATE(), 22.22
union select 22, 1, 'BB row 2A1', GETDATE() - 1, 222.44
union select 210, 11, 'BB row 1A2', GETDATE() - 2, 2222.66
union select 220, 11, 'BB row 2A2', GETDATE() - 4, 22222.88
insert into @TableC
select 31, 1, 'CC row 1A1'
union select 32, 1, 'CC row 2A1'
union select 310, 11, 'CC row 1A2'
union select 320, 11, 'CC row 2A2'
;WITH outpQ
AS
(
SELECT 'A' AS Ord, AID, 'A|' + CAST(ColA1 AS VARCHAR(11)) + '|' + ColA2 AS Extract
FROM @TableA
UNION ALL
SELECT 'B', A.AID, 'B|' + CAST(B.BID AS VARCHAR(11)) + '|' + RTRIM(B.ColB1) + '|' + CONVERT(VARCHAR(30), B.ColB2) + '|' + CAST(ColA2 AS VARCHAR(20)) AS Extract
FROM @TableA A
JOIN @TableB B ON B.AID = A.AID
UNION ALL
SELECT 'C', A.AID, 'C|' + CAST(C.CID AS VARCHAR(11)) + '|' + C.ColC1 AS Extract
FROM @TableA A
JOIN @TableC C ON C.AID = A.AID
)
SELECT Extract FROM outpQ ORDER BY AID, Ord
The above may not be exact way you should follow but it should give you an idea how you can achieve your extract without using cursor.
May I call you stubborn? Sorry for rudeness again 😀
Sorry I have a lot on my plate as I have far more responsibilities than 1 person should have lol. I will get that script to you all later this week as I read through the directions on how to produce it but I am far from educated in tSQL and its nuances.
I just knew the union all would not work and was making sure I was correct and the output was easy to grab since it was on my desktop =).
Thank you again for that quick response. 😎
June 29, 2010 at 10:54 am
Okay here is the data and tables you asked for assuming I did this correct.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#FileInfo','U') IS NOT NULL
DROP TABLE #FileInfo
--===== Create the test table with
CREATE TABLE #FileInfo
(
CaseNumber varchar(50) PRIMARY KEY CLUSTERED,
BRTNumber varchar(50),
ParcelNumber varchar(50),
Premises varchar(50),
Client varchar(50)
)
--populate table with data
INSERT INTO #FileInfo
(CaseNumber, BRTNumber, ParcelNumber, Premises, Client)
SELECT 'PHI0001','0123456789','13N12-139','1234 Market Street','Linebarger' UNION ALL
SELECT 'PHI0002','1234567890','4N8-193','234 Chestnut Street','Linebarger' UNION ALL
SELECT 'PHI0003','2345678901','6N10-235','1600 JFK Blvd','Linebarger'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#municipalLiens','U') IS NOT NULL
DROP TABLE #municipalLiens
--===== Create the test table with
CREATE TABLE #municipalLiens
(
MuniKey int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
LienNumber varchar(50),
LienDate datetime,
LienReason varchar(50),
LienAmount money
)
--populate table with data
INSERT INTO #municipalLiens
(MuniKey, CaseNumber, LienNumber, LienDate, LienReason, LienAmount)
SELECT '1','PHI0001','05263565','07/29/1982','Replace Curb Stop', 256.32 UNION ALL
SELECT '2','PHI0001','98765436','05/06/1989','Turn off water meter', 34.00 UNION ALL
SELECT '3','PHI0002','69874963','08/28/2008','Replace Curb Stop', 400.65 UNION ALL
SELECT '4','PHI0003','98767655','08/30/1984','Replace Curb Stop', 256.32 UNION ALL
SELECT '5','PHI0003','44444444','05/06/1995','Turn off water meter', 34.00 UNION ALL
SELECT '6','PHI0003','23423521','08/28/2010','Replace Curb Stop', 400.65
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Documents','U') IS NOT NULL
DROP TABLE #Documents
--===== Create the test table with
CREATE TABLE #Documents
(
documentID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
recDate datetime,
docDate datetime,
docAmount varchar(50),
interestID varchar(50)
)
--populate table with data
INSERT INTO #Documents
(documentID, CaseNumber, recDate, docDate, docAmount, interestID)
SELECT '1','PHI0001','07/28/1982','07/29/1982',100000.00, '1' UNION ALL
SELECT '2','PHI0001','05/06/1989','05/06/1989',50000.00, '2' UNION ALL
SELECT '3','PHI0002','08/28/2008','09/15/2008',25000.00, '3' UNION ALL
SELECT '4','PHI0002','08/28/2008','08/30/2008',10000.00, '4' UNION ALL
SELECT '5','PHI0002','05/10/1995','05/06/1995',5000.00, '5' UNION ALL
SELECT '6','PHI0003','08/28/2010','08/28/2010',2500.00, '6'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#eInterests','U') IS NOT NULL
DROP TABLE #eInterests
--===== Create the test table with
CREATE TABLE #eInterests
(
interestID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
eInterestFirst varchar(50),
eInterestMiddle varchar(50),
eInterestLast varchar(50),
eInterestAlias varchar(50)
)
--populate table with data
INSERT INTO #eInterests
(interestID, CaseNumber, eInterestFirst, eInterestMiddle, eInterestLast, eInterestAlias)
SELECT '1','PHI0001','George','Q','Greiner', '' UNION ALL
SELECT '2','PHI0001','Joe','Z','Smith', 'JZS' UNION ALL
SELECT '3','PHI0002','Lynne','','Tymonko', 'Lynn T. Tymonko' UNION ALL
SELECT '4','PHI0002','Bob','P','Pablano', '' UNION ALL
SELECT '5','PHI0002','Aaron','B','Cohen', '' UNION ALL
SELECT '6','PHI0003','Orbit','T','Trommer', ''
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#addresses','U') IS NOT NULL
DROP TABLE #addresses
--===== Create the test table with
CREATE TABLE #addresses
(
AddressID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
interestID int,
iAddress varchar(50),
iCity varchar(50),
iState varchar(50),
izip varchar(10)
)
INSERT INTO #addresses
(AddressID, CaseNumber, interestID, iAddress, iCity, iState, iZip)
SELECT '1','PHI0001','2','234 Walnut Street','Philadelphia', 'PA', '19103' UNION ALL
SELECT '2','PHI0003','6','5722 Filbert STreet','Philadelphia', 'PA', '19115'
--===== If the test table already exists, drop it -- note that this is from another database
IF OBJECT_ID('TempDB..#judgments','U') IS NOT NULL
DROP TABLE #judgments
--===== Create the test table with
CREATE TABLE #judgments
(
judgmentID int PRIMARY KEY CLUSTERED,
CaseNumber varchar(50),
JAmt varchar(50),
CaseID nvarchar(50),
Jdate datetime,
plaintiff_info varchar(255),
defendant_info varchar(255)
)
--populate table with data
INSERT INTO #judgments
(judgmentID, CaseNumber, JAmt, CaseID, Jdate, plaintiff_info, defendant_info)
SELECT '1','PHI0001','789.58','05632132','11/02/2002', 'Jimmy Fallon 1234 Market Street, Philadelphia, PA 19115', 'Philadelphia Traffic Court' UNION ALL
SELECT '2','PHI0001','75211.21','95125154','05/15/2007', 'Smith Bob 56 Trenton STreet', 'IRS' UNION ALL
SELECT '3','PHI0001','123123.25','85214125','03/25/2008', 'George Greiner 265 Felton Street, Glenside, PA 19038', 'Bank of America' UNION ALL
SELECT '4','PHI0002','345345.23','98761232','07/15/2009', 'Cohen Aaron B 345 Lincoln Parkway, Baltimore, MD', 'Wells Fargo' UNION ALL
SELECT '5','PHI0003','98765.36','75855552','08/17/2006', 'Michelle Smith Z', 'City of Philadelphia' UNION ALL
SELECT '6','PHI0003','12311.98','11111111','12/13/2005', 'Donavan McNabb 567 Richmond Lane, Marlton, NJ', 'Internal Revenue Service'
July 2, 2010 at 10:05 am
Thank you so much for your help Eugene.
Got this thing working. One question though am I going to have to convert ALL of my fields to Varchar to create the string? If so will money / date fields convert to varchar okay?
Also would it be best to best to create a sub data set as I am only going to need to query files done within the last 24 hours when this is all set up or just use a a where statement when pulling from the "master table". Just want this to be as fast as possible =).
George
July 2, 2010 at 3:35 pm
Yes, you need to convert everything into varchar (actually, you would need to do it anyway even using your cursors). Yes you can do it with money, numerics and datetimes. Have a look CONVERT function in BoL to see which formats you can get.
Using this T-SQL technique will be defenetly faster than using cursors.
You can filter your queries when building this extract with no problem. I don't beleive moving data into separate datasets will bring any benefits to the performance. But you can/should try.
Have you thought about using SSIS to do such extract? Actually, it is especially designed to do this sort of things 😀
July 6, 2010 at 9:38 am
Eugene Elutin (7/2/2010)
Yes, you need to convert everything into varchar (actually, you would need to do it anyway even using your cursors). Yes you can do it with money, numerics and datetimes. Have a look CONVERT function in BoL to see which formats you can get.Using this T-SQL technique will be defenetly faster than using cursors.
You can filter your queries when building this extract with no problem. I don't beleive moving data into separate datasets will bring any benefits to the performance. But you can/should try.
Have you thought about using SSIS to do such extract? Actually, it is especially designed to do this sort of things 😀
I have thought about using SSIS but was under the impression that I still needed to do this in T SQL. I assume that I would create the record set which will be based on files finished in the last 24 hours and then query that data using the T SQL and use a flat file destination?
Is that correct?
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply