Vertical Concatenation 1

[DrSmyth]

As usually happens, when you figure out one problem another immediately arises....
My report looks at two tables. The first table is a person table with a 'Person Code' (unique), this links to a 'Person Code' in the 'Notes' table from which i want to retrieve the latest note (text field) based on the 'Last Maintained Date'. Now for some reason, if the note is over 250 characters then it's split over multiple text fields (each with same 'Last Maintained Date') so multiple entries are being returned.

E.G if person A has 3 notes and the latest note is 300 characters long then only the last nore is being returned but it is being displayed as 2 notes, so the person is shown twice in the resultant report.

What I want is to concatenate any multiple note fields for a person into a single note field, so that each person is only occuring on one row...

Any suggestions?

P.S. I've just read through this post and think I may have gone a very long and complicated way around explaining this problem.... Words seem to be failing me today..... Think i need to get more sleep!!!!!

 

[blom0344]

DrSmyth,

This should be possible with the use of OLAP functions.

Something like:

select A.Person,NotesID,Note,Rank() Over (Partition by Person Order by Last_Maintained_date),Length(Note)
from Person A, Notes B
Where A.Person=B.Person and
Rank() Over (Partition by Person Order by Last_Maintained_date) = 1

This will return 1 or 2 rows for each person, cause the rank() function will return equal values for identical Last_Maintain_Date.

Length(Note) will either be =250 or <>250

With 2 DP's you can then easily link over Person and concatenate the notes.

Somehow, I cannot get it to work within one DP....

Ties Blom
Information analyst

 

[DrSmyth]

Hi Blom, How's it going? Hope you are well....

I've actually put this through Access and used some vba to tie the various notes fields together, which works, but is an extra step in the process that we could do without.

I have some questions about your response (excuse my lack of understanding)

1) I've heard a few people talk about OLAP functions, but I don't really understand what they are and how they work, can you offer any information on this or suggest a good source?

 

[blom0344]

DrSmyth,

I recall that you work with DB2 too?

OLAP functions rank() and dense_rank() allow you to rank the rows returned from a query, making it easier to fetch just the top x for a given set. I believe that both DB2 and ORACLE RDBMS offer them, while they are still not included with SQL server. You won't find them in designer, but if you use them, they will parse okay.

For DB2 it is all explained in VERY good detail in Birchall's SQL Cookbooks:

http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM

You may run into trouble with very large quantities of data, cause these are pretty demanding functions...

If you work with mainframe/ AS400 though you are out of luck



Ties Blom
Information analyst

 

[DrSmyth]

Thanks Blom, that's really useful.

Have already downloaded Birchalls sql cookbook, but been a bit put off in the past because of it's size. However, have strted reading a few of the sections and they do explain things really well...

Have a star for your useful tip