Returning Data - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Returning Data

Author

Topic

the ase in database

9 Posts

Posted - 2007-09-06 : 09:59:17

I could do with some help, this might be a rookie question but i'm in need of some guidance.

i have to pull mortgage agreements and each agreement can have 1, 2 ,3 or 4 borrowers. I need to return all the named borrower details of the agreements under a column called [Borrower Name(s)] whether it is populated with 1, 2 ,3 , 4 names. Currently i have a query that returns the borrowers names in said column, but the problem is that if 'adam smith' is stored as borrower 1 (but is part of an agreement with more borrowers, for example, his wife mary) then i get an entry for him as a sole borrower and then get an entry for [adam smith, mary smith] and if he was part of an agreement that had three borrowers he'd appear as [adam smith, mary smith, joe smith], with each agreement having the same number agreemnet number. perhaps this will illustrate my point:

agreement number borrower

1234 adam smith
1234 adam smith, mary smith
1234 adam smith, mary smith, joe smith

does anyone have any idea how to remedy this because i'm stuck.

thanks in advance

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 10:37:26

Use a User Defined Function to concatenate the names of the borrowers

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647#45135

Kristen

the ase in database

9 Posts

Posted - 2007-09-06 : 10:52:00

Don't really understand what's going on here. do you kow of any "friendlier" solutions, i feel like i've come in half-way through a conversation.

cheers

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 10:59:09

unfortunatly there is no friendlier solution.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 10:59:30

"do you kow of any "friendlier" solutions"

No, you need to make a User Defined function to do this in SQL 2000 (or use a more complicated Stored Procedure route). The post I linked to has a worked example, if you get stuck post the code you are having trouble with and I'll try to help, but I don't have the time to write the solution for you - well, I will if you want to pay me of course

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 11:03:38

For mre informations, refer
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 11:06:10

quote:
Originally posted by Kristen

Use a User Defined Function to concatenate the names of the borrowers

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647 color="red">#45135

Kristen

I am interested to know how you found that number to set required reply on top

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 11:08:25

"I am interested to know how you found that number to set required reply on top"

Its the same as the REPLY_ID, which is shown in the "Reply with quote" icon

You have to manually extra it from there and append it with a "#" separator

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 11:15:37

quote:
Originally posted by Kristen

"I am interested to know how you found that number to set required reply on top"

Its the same as the REPLY_ID, which is shown in the "Reply with quote" icon

You have to manually extra it from there and append it with a "#" separator

Kristen

Thanks Kristen. Really you did lot of Tests

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 11:19:41

I think Peso or KHTan told me, actually ....

... but I'll take the credit if you insist!

the ase in database

9 Posts

Posted - 2007-09-06 : 11:37:56

does anyone know of any "user defined functions" tutorials because i'm out my depth and need to do some reading.

kindest regards
mr suzuki

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 11:44:28

Books Online:
http://msdn2.microsoft.com/en-us/library/ms130214.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 11:44:32

Taking Adam's example from the link Madhi posted above:


CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SET @Output = ''

	SELECT @Output =	CASE @Output 
				WHEN '' THEN au_lname 
				ELSE @Output + ', ' + au_lname 
				END
	FROM Authors
	WHERE State = @State
	ORDER BY au_lname

	RETURN @Output
END
GO

can you just change the table name, column names, and column datatypes to match what you want?

Kristen

the ase in database

9 Posts

Posted - 2007-09-06 : 11:48:45

i'll give it a go
cheers

the ase in database

9 Posts

Posted - 2007-09-06 : 11:50:35

by the way ....i've only being doing this for 6 weeks! and didn't study sql so it's a bit of a learning curve i'm on. thanks for the links to tutorials

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 11:57:08

By the by, if you can be bothered it would help if you could re-register as the double-quote in your Username is causing JavaScript errors on the pages that contain your UserName, thanks.

Kristen

the ase in database

9 Posts

Posted - 2007-09-07 : 05:01:38

double quotes are gone

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 05:57:48

"double quotes are gone"

And so are the JavaScript errors, cheers!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-07 : 08:25:11

quote:
Originally posted by the ase in database

double quotes are gone

and post count has come

Madhivanan

Failing to plan is Planning to fail

the ase in database

9 Posts

Posted - 2007-09-11 : 05:51:37

I've been awol but i am back now.....the problem continues. I'm not sure that i stated the problem correctly. It is possible that one borrower agreement can have 3 borrowers and another can have 2 and 1 etc, yet they all need to be returned in the same query i.e not one query returning only agreements with 3 borrowwers and another query returning agreements with 2 borrowers.

I have a common or garden select query (nested a few times) that returns the appropriate records - see original posting, but i want to filter so as to get rid of the duplicates, rather than join adam smith, mary smith and joe smith, i want to take the agreement with 3 borrowers, keep that one, but get rid of the agreement where it's just adam smith and adam smith and mary smith.

does this help at all. I had a look at kristen's udf and wasn't quite sure how to alter it to make it discern ebtween 1,2,3,4 borrowers, and i'm not sure, although my experience is limited, that this is what i need, but will yield to a higher authroity on such matters.

here's the query
-----------------
SELECT DISTINCT alphaFields.daba1_alpha_3 AS [Account Number],
+',,,,'+(RTRIM(borrower.fullname+','+borrower2.fullname+''+borrower3.fullname+''+borrower4.fullname))AS [Borrower Name],
(RTRIM(borrowerAddress.building_name) +' '+ RTRIM(borrowerAddress.number)
+' '+ RTRIM(borrowerAddress.line1) +' '+ RTRIM(borrowerAddress.line2)
+' '+ RTRIM(borrowerAddress.line3)+' '+ RTRIM(borrowerAddress.town)
+' '+ RTRIM(borrowerAddress.county)+' '+ RTRIM(borrowerAddress.postcode)) AS [Property (Including PostCode)],
moneyFields.dabm1_money_2 AS [Price stated in transfer],
damatters.matter_ref AS [Conveyancer Reference],
' ' AS [Date of Instructions],
'N/A' AS [Details of Lease],
'N/A' AS [Ground Rent Payable (if any)],
'N/A' AS [Name and Address of Landlord],
'N/A' AS [Is Lanlord Arranging Buildings Insurance Cover?],
'N/A ' AS [Subject to HMO]

FROM damatters
LEFT OUTER JOIN dapdf_section
ON damatters.worktype = dapdf_section.section

INNER JOIN daba1_usrf alphaFields ON alphaFields.daba1_entity_id = damatters.entity_id
AND alphaFields.daba1_pdf_id = dapdf_section.pdf_id
INNER JOIN daenttonam borrowerEntityToName ON borrowerEntityToName.matter_id = damatters.entity_id
INNER JOIN daenttonam borrower2EntityToName ON borrower2EntityToName.matter_id = damatters.entity_id
INNER JOIN daenttonam borrower3EntityToName ON borrower3EntityToName.matter_id = damatters.entity_id
INNER JOIN daenttonam borrower4EntityToName ON borrower4EntityToName.matter_id = damatters.entity_id

INNER JOIN daname borrower ON borrower.entity_id = borrowerEntityToName.main_entity
AND borrowerEntityToName.code2_description = 'BORROWER'
AND (borrowerEntityToName.old_relationship IS NULL
OR borrowerEntityToName.old_relationship= ' ' )
INNER JOIN daname borrower2 ON borrower2.entity_id = borrower2EntityToName.main_entity
AND borrower2EntityToName.code2_description = 'BORROWER2'
AND (borrower2EntityToName.old_relationship IS NULL
OR borrower2EntityToName.old_relationship= ' ' )

INNER JOIN daname borrower3 ON borrower3.entity_id = borrower3EntityToName.main_entity
AND borrower3EntityToName.code2_description = 'BORROWER3'
AND (borrower3EntityToName.old_relationship IS NULL
OR borrower3EntityToName.old_relationship= ' ' )

INNER JOIN daname borrower4 ON borrower4.entity_id = borrower4EntityToName.main_entity
AND borrower4EntityToName.code2_description = 'BORROWER4'
AND (borrower4EntityToName.old_relationship IS NULL
OR borrower4EntityToName.old_relationship= ' ' )
INNER JOIN daaddress borrowerAddress ON borrowerAddress.address_id = borrower.main_address_id
INNER JOIN dabm1_usrf moneyFields ON moneyFields.dabm1_entity_id = damatters.entity_id
AND moneyFields.dabm1_pdf_id = dapdf_section.pdf_id
INNER JOIN dabd1_usrf dateFields ON dateFields.dabd1_entity_id = damatters.entity_id
AND dateFields.dabd1_pdf_id = dapdf_section.pdf_id
WHERE damatters.worktype = '44' AND dapdf_section.pdf_id = '95'

UNION
(SELECT alphaFields.daba1_alpha_3 AS [Account Number],
+',,,'+(RTRIM(borrower.fullname+'&'+borrower2.fullname+''+borrower3.fullname)) AS [Borrower Name],
(RTRIM(borrowerAddress.building_name) +' '+ RTRIM(borrowerAddress.number)
+' '+ RTRIM(borrowerAddress.line1) +' '+ RTRIM(borrowerAddress.line2)
+' '+ RTRIM(borrowerAddress.line3)+' '+ RTRIM(borrowerAddress.town)
+' '+ RTRIM(borrowerAddress.county)+' '+ RTRIM(borrowerAddress.postcode)) AS [Property (Including PostCode)],
moneyFields.dabm1_money_2 AS [Price stated in transfer],
damatters.matter_ref AS [Conveyancer Reference],
' ' AS [Date of Instructions],
'N/A' AS [Details of Lease],
'N/A' AS [Ground Rent Payable (if any)],
'N/A' AS [Name and Address of Landlord],
'N/A' AS [Is Lanlord Arranging Buildings Insurance Cover?],
'N/A ' AS [Subject to HMO]
FROM damatters
LEFT OUTER JOIN dapdf_section
ON damatters.worktype = dapdf_section.section

INNER JOIN daba1_usrf alphaFields ON alphaFields.daba1_entity_id = damatters.entity_id
AND alphaFields.daba1_pdf_id = dapdf_section.pdf_id
INNER JOIN daenttonam borrowerEntityToName ON borrowerEntityToName.matter_id = damatters.entity_id

INNER JOIN daenttonam borrower2EntityToName ON borrower2EntityToName.matter_id = damatters.entity_id
INNER JOIN daenttonam borrower3EntityToName ON borrower3EntityToName.matter_id = damatters.entity_id

INNER JOIN daname borrower ON borrower.entity_id = borrowerEntityToName.main_entity
AND borrowerEntityToName.code2_description = 'BORROWER'
AND (borrowerEntityToName.old_relationship IS NULL
OR borrowerEntityToName.old_relationship= ' ' )
INNER JOIN daname borrower2 ON borrower2.entity_id = borrower2EntityToName.main_entity
AND borrower2EntityToName.code2_description = 'BORROWER2'
AND (borrower2EntityToName.old_relationship IS NULL
OR borrower2EntityToName.old_relationship= ' ' )

INNER JOIN daname borrower3 ON borrower3.entity_id = borrower3EntityToName.main_entity
AND borrower3EntityToName.code2_description = 'BORROWER3'
AND (borrower3EntityToName.old_relationship IS NULL
OR borrower3EntityToName.old_relationship= ' ' )
INNER JOIN daaddress borrowerAddress ON borrowerAddress.address_id = borrower.main_address_id
INNER JOIN dabm1_usrf moneyFields ON moneyFields.dabm1_entity_id = damatters.entity_id
AND moneyFields.dabm1_pdf_id = dapdf_section.pdf_id
INNER JOIN dabd1_usrf dateFields ON dateFields.dabd1_entity_id = damatters.entity_id
AND dateFields.dabd1_pdf_id = dapdf_section.pdf_id
WHERE damatters.worktype = '44' AND dapdf_section.pdf_id = '95' )
UNION
(SELECT alphaFields.daba1_alpha_3 AS [Account Number],
+',,'+(RTRIM(borrower.fullname+'&'+borrower2.fullname)) AS [Borrower Name],
(RTRIM(borrowerAddress.building_name) +' '+ RTRIM(borrowerAddress.number)
+' '+ RTRIM(borrowerAddress.line1) +' '+ RTRIM(borrowerAddress.line2)
+' '+ RTRIM(borrowerAddress.line3)+' '+ RTRIM(borrowerAddress.town)
+' '+ RTRIM(borrowerAddress.county)+' '+ RTRIM(borrowerAddress.postcode)) AS [Property (Including PostCode)],
moneyFields.dabm1_money_2 AS [Price stated in transfer],
damatters.matter_ref AS [Conveyancer Reference],
' ' AS [Date of Instructions],
'N/A' AS [Details of Lease],
'N/A' AS [Ground Rent Payable (if any)],
'N/A' AS [Name and Address of Landlord],
'N/A' AS [Is Lanlord Arranging Buildings Insurance Cover?],
'N/A ' AS [Subject to HMO]
FROM damatters
LEFT OUTER JOIN dapdf_section
ON damatters.worktype = dapdf_section.section
INNER JOIN daba1_usrf alphaFields ON alphaFields.daba1_entity_id = damatters.entity_id
AND alphaFields.daba1_pdf_id = dapdf_section.pdf_id
INNER JOIN daenttonam borrowerEntityToName ON borrowerEntityToName.matter_id = damatters.entity_id
INNER JOIN daenttonam borrower2EntityToName ON borrower2EntityToName.matter_id = damatters.entity_id
INNER JOIN daname borrower ON borrower.entity_id = borrowerEntityToName.main_entity
AND borrowerEntityToName.code2_description = 'BORROWER'
AND (borrowerEntityToName.old_relationship IS NULL
OR borrowerEntityToName.old_relationship= ' ' )
INNER JOIN daname borrower2 ON borrower2.entity_id = borrower2EntityToName.main_entity
AND borrower2EntityToName.code2_description = 'BORROWER2'
AND (borrower2EntityToName.old_relationship IS NULL
OR borrower2EntityToName.old_relationship= ' ' )
INNER JOIN daaddress borrowerAddress ON borrowerAddress.address_id = borrower.main_address_id
INNER JOIN dabm1_usrf moneyFields ON moneyFields.dabm1_entity_id = damatters.entity_id
AND moneyFields.dabm1_pdf_id = dapdf_section.pdf_id
INNER JOIN dabd1_usrf dateFields ON dateFields.dabd1_entity_id = damatters.entity_id
AND dateFields.dabd1_pdf_id = dapdf_section.pdf_id
WHERE damatters.worktype = '44' AND dapdf_section.pdf_id = '95'
UNION
(SELECT alphaFields.daba1_alpha_3 AS [Account Number],
+','+(RTRIM(borrower.fullname)) AS [Borrower Name],
(RTRIM(borrowerAddress.building_name) +' '+ RTRIM(borrowerAddress.number)
+' '+ RTRIM(borrowerAddress.line1) +' '+ RTRIM(borrowerAddress.line2)
+' '+ RTRIM(borrowerAddress.line3)+' '+ RTRIM(borrowerAddress.town)
+' '+ RTRIM(borrowerAddress.county)+' '+ RTRIM(borrowerAddress.postcode)) AS [Property (Including PostCode)],
moneyFields.dabm1_money_2 AS [Price stated in transfer],
damatters.matter_ref AS [Conveyancer Reference],
' ' AS [Date of Instructions],
'N/A' AS [Details of Lease],
'N/A' AS [Ground Rent Payable (if any)],
'N/A' AS [Name and Address of Landlord],
'N/A' AS [Is Lanlord Arranging Buildings Insurance Cover?],
'N/A ' AS [Subject to HMO]
FROM damatters
LEFT OUTER JOIN dapdf_section
ON damatters.worktype = dapdf_section.section
INNER JOIN daba1_usrf alphaFields ON alphaFields.daba1_entity_id = damatters.entity_id
AND alphaFields.daba1_pdf_id = dapdf_section.pdf_id
INNER JOIN daenttonam borrowerEntityToName ON borrowerEntityToName.matter_id = damatters.entity_id
INNER JOIN daname borrower ON borrower.entity_id = borrowerEntityToName.main_entity
AND borrowerEntityToName.code2_description = 'BORROWER'
AND (borrowerEntityToName.old_relationship IS NULL
OR borrowerEntityToName.old_relationship= ' ' )
INNER JOIN daaddress borrowerAddress ON borrowerAddress.address_id = borrower.main_address_id
INNER JOIN dabm1_usrf moneyFields ON moneyFields.dabm1_entity_id = damatters.entity_id
AND moneyFields.dabm1_pdf_id = dapdf_section.pdf_id
INNER JOIN dabd1_usrf dateFields ON dateFields.dabd1_entity_id = damatters.entity_id
AND dateFields.dabd1_pdf_id = dapdf_section.pdf_id
WHERE damatters.worktype = '44' AND dapdf_section.pdf_id = '95'))
ORDER BY alphaFields.daba1_alpha_3 ASC

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 07:27:45

"I had a look at kristen's udf and wasn't quite sure how to alter it to make it discern ebtween 1,2,3,4 borrowers"

The function doesn't care how many borrows, it will just concatenate them all together - with the upper limit of anything more than 8,000 characters getting chopped off!

What you need to do is constrain your Select Query to only finding Agreements with [if I have understood correctly] between 1 and 3 borrowers

The basic way to do that would be to add to your WHERE clause something like


...
WHERE Agreement.ID IN
(
    SELECT Agreement.ID 
    FROM Agreements AS A 
         JOIN Borrowers AS B
             ON B.AgreementID = A.AgreementID
    GROUP BY Agreement.ID
    HAVING COUNT(*) BETWEEN 1 AND 3
)

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources