Is this the correct method or cursors? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is this the correct method or cursors?

Author

Topic

george.greiner
Starting Member

19 Posts

Posted - 2010-07-07 : 14:59:50

Hello,

I was trying to use the code below to create a .txt file to send completed cases to our clients. It "almost" works as I want it to. The issue is that I need to pass a second parameter 'ineterestID' from dbo.eInterest (line B) to lines B1 and B2 to pull the corresponding addresses and bankruptcies if any exist.

Currently the output looks as such:
A (Case Information)
B (defendant1)
B (defendant2)
B (defendant3)
B1 (address for defendant1)
B1 (address for defendant2)
B2 (bankruptcy for defendant3)
C
C
C

I need it to look like :
A (Case Information)
B (defendant1)
B1 (Address for defendant 1)
B (defendant2)
B1 (Address for defendant 2)
B (defendant3)
B2 (bankruptcy for defendant 3)
C
C
C

(I left the C lines there as they are in the code below but are not relevant to the issue at hand.

Do I have to do this a different way (using cursors) or is there a way to get the desired result from this method?

Any help would be appreciated.

Thank you,

George


;WITH outpQ
AS
(
SELECT 'A' AS Ord, CaseNumber, 'A|' + CaseNumber + '|' + CAST(BRTNumber AS VARCHAR(11)) + '|' + IsNull(LegalDescription, '') + '|' + IsNull(replace(convert(char(10), CoverDate, 101), '/', ''), '') + '|' + IsNull(SumOut, '')  AS Extract
FROM   CityCollection.dbo.FileInfo              
UNION ALL 
SELECT 'A1', A.CaseNumber, 'A1|' + '|' + IsNull(B.LienInstrument, '') + '|' + B.LienNumber + '|' +  Cast(B.LienAmount AS VARCHAR(20)) + '|' + IsNull(replace(LienDate, '/', ''), '') + '|' + IsNull(B.LienReason, '') AS Extract
FROM   CityCollection.dbo.FileInfo A
JOIN   CityCollection.dbo.municipalLiens B ON B.CaseNumber = A.CaseNumber
UNION ALL 
SELECT 'B', A.CaseNumber, 'B|' + '|' + IsNull(C.defTypeCode, '') + '|' + IsNull(C.eInterestFirst, '') + '|' +  IsNull(C.eInterestMiddle, '') + '|' + IsNull(C.eInterestLast, '') + '|' + IsNull(C.eInterestAlias, '') + '|' + IsNull(C.eInterestCom, '') + '|' + '|' + '|' + '|' + '|' + IsNull(C.eInterestAdd, '') + '|' + IsNull(C.eInterestAdd2, '') + '|' + IsNull(C.eInterestCity, '') + '|' + IsNull(C.eInterestState, '') + '|' + IsNull(C.eInterestZip, '') + '|' + IsNull(C.eInterestCountry, '') + '|' + IsNull(C.eInterestDoc, '') + '|' + Cast(interestID AS VARCHAR(MAX)) AS Extract
FROM   CityCollection.dbo.FileInfo A
JOIN   CityCollection.dbo.eInterest C ON C.CaseNumber = A.CaseNumber
UNION ALL  
SELECT 'B1', A.CaseNumber, 'B1|' + '|' + IsNull(D.address, '') + '|' + '|' +  IsNull(D.city, '') + '|' + IsNull(D.state, '') + '|' + IsNull(D.zip, '') + '|' + IsNull(D.country, '') AS Extract
FROM   CityCollection.dbo.FileInfo A
JOIN   CityCollection.dbo.addresses D ON D.CaseNumber =  A.CaseNumber 
UNION ALL 
--FROM authors a 
--INNER JOIN titleauthor ta ON a.au_id = ta.au_id 
--JOIN titles t ON ta.title_id = t.title_id
SELECT 'B2', A.CaseNumber, 'B2|' + '|' + IsNull(E.suiteNumber, '') + '|' +  Cast(E.BDate AS VARCHAR(11)) + '|' + IsNull(E.Chapter, '') + '|' + IsNull(E.vs, '') AS Extract
FROM   CityCollection.dbo.FileInfo A
JOIN   CityCollection.dbo.banks E ON E.CaseNumber = A.CaseNumber
UNION ALL
SELECT 'B3', A.CaseNumber, 'B3|' + '|' + F.docType + '|' + IsNull(Cast(F.DocAmount AS VARCHAR(50)), '') + '|' + IsNull(replace(convert(char(10), docDate, 101), '/', ''), '') + '|' + IsNull(replace(convert(char(10), recDate, 101), '/', ''), '') + '|' + IsNull(F.bkpage, '') + '|' + F.grantee + Cast(F.documentID AS VARCHAR(Max)) AS Extract
FROM   CityCollection.dbo.FileInfo A                                                                                                                                                                                                                                                                                                                            
JOIN   CityCollection.dbo.Documents F ON F.CaseNumber = A.CaseNumber                                         
UNION ALL
SELECT 'C', A.CaseNumber, 'C|' + IsNull(G.grantorDef,'') + '|' + IsNull(G.grantor, '') AS Extract
FROM   CityCollection.dbo.FileInfo A 
JOIN   CityCollection.dbo.documents G ON G.CaseNumber = A.CaseNumber
UNION ALL
SELECT 'C', A.CaseNumber, 'C|' + IsNull(H.TADef,'') + '|' + IsNull(H.TaxAccts, '') AS Extract
FROM   CityCollection.dbo.FileInfo A 
JOIN   CityCollection.dbo.FileInfo H ON H.CaseNumber = A.CaseNumber
UNION ALL
SELECT 'C', A.CaseNumber, 'C|' + IsNull(I.RegDef,'') + '|' + IsNull(I.Registry, '') AS Extract
FROM   CityCollection.dbo.FileInfo A 
JOIN   CityCollection.dbo.FileInfo I ON I.CaseNumber = A.CaseNumber
UNION ALL
SELECT 'C', A.CaseNumber, 'C|' + IsNull(J.CType, '') + '|' + IsNull(J.plaintiffName,'') + '|' + IsNull(J.plaintiffAdd1, '') + '|' + IsNull(J.plaintiffCity, '') + '|' + IsNull(J.plaintiffState, '') + '|' + IsNull(J.plaintiffZip, '') + '|' + '|' + IsNull(J.defendantName, '') + '|' + IsNull(J.defendantAdd1, '') + '|' + IsNull(J.defCity, '') + '|' + IsNull(J.defState, '') + '|' + IsNull(J.defZip, '') + '|' +  '|' + IsNull(J.Court, '') + '|' + IsNull(J.CaseID, '') + '|' + IsNull(J.JAmt, '') + '|' + IsNull(replace(convert(VarChar(10), JDate, 101), '/', ''), '') + '|' + IsNull(replace(convert(VARCHAR(10), revivedDate, 101), '/', ''), '') AS Extract
FROM   CityCollection.dbo.FileInfo A 
JOIN   Acme.new_judgment_system.dbo.selected_compiled_clean J ON J.CaseNumber = A.CaseNumber
)
SELECT Extract FROM outpQ ORDER BY CaseNumber, Ord

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources