March 5, 2013 at 4:32 pm
Hi, looking for some help or advice if possible, been searching this all night with no luck, im probably not using the correct search terms. I am loading Mainframe data into SQL Server 2008.
There is a master record Table which I need to join to a notes table. The Master Record might have name, address, job title etc, and the Notes Table contains a note.
The thing that makes this a bit unusual is as this has come off the mainframe due to file space considerations the notes field was split over multiple rows.
So you having something like this
Master Table
ID, Name, Address, Job Desc
1, Bob", "New York","Sports Reporter"
2,"Chris","Washington","Doctor"
Notes Table
ID, EmpID,Note
1,2,"Chris has been"
2,2,"a doctor in the"
3,2,"USA for 5 years."
I want a select statment to output rows for reporting purposes (using SSRS) which would say something like this..
2,"Chris","Washington","Doctor","Chris has been a doctor in the USA for 5 years"
Can anyone point me in the right direction? I hope I have explained it clearly.
Thanks
March 5, 2013 at 4:50 pm
Ugh, not a fun process, particularly when row order matters. Don't feel bad about struggling with it, it's neither obvious nor simple.
Basically you're looking for the quirky update or serial update to deal with this, but that gets involved. Using that search term will bring up an article on this site that'll discuss it at length. Is there a reasonable limit to how the mainframe split these or is it something like 30+ lines?
Also, if you can setup the data for us to consume, testable code is easier to produce by the volunteers here. Check out the first link in my sig for what we'd prefer to see when we're building code solutions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 5, 2013 at 5:02 pm
HI, thanks for the quick reply. Im about to get off to bed in a moment (midnight). Not at work so cant set up sample data to be consumed. I think it was limited to 30 or 40 characters per line, so most of the time the note is split over between 2 and say 5 lines. I will try those search terms now (or until my gf gets fed up of me having the light on)
Been thinking about this all night as didnt really get anywhere with it this afternoon, I'm building a set of reports at the moment for data cleansing purposes, up until now I have been knocking them out really quickly but have got stuck on this on. In a few months time I have to get the data into new systems.
Its been a real eye openner working with this Mainframe Data, I have never worked with the mainframe myself, each file is of a certain length only and they have to try and squeeze as much into it as possible. They build layouts (i pretend they are like tables) that include spare "columns" which they later go back and use a few years down the line. Apparently its really difficult to just add the extra data in if you didnt allow for it at design time.
March 5, 2013 at 5:10 pm
How about something like this:
/*
Master Table
ID, Name, Address, Job Desc
1, Bob", "New York","Sports Reporter"
2,"Chris","Washington","Doctor"
Notes Table
ID, EmpID,Note
1,2,"Chris has been"
2,2,"a doctor in the"
3,2,"USA for 5 years."
*/
create table dbo.MasterTable(
ID int,
EmpName varchar(16),
EmpAddress varchar(16),
JobDesc varchar(16)
);
create table dbo.Note (
ID int,
EmpID int,
Note varchar(16)
);
go
insert into dbo.MasterTable
values (1,'Bob','New York','Sports Reporter'),
(2,'Chris','Washington','Doctor');
insert into dbo.Note
values (1,2,'Chris has been'),
(2,2,'a doctor in the'),
(3,2,'USA for 5 years.');
go
select
mt.ID,
mt.EmpName,
mt.EmpAddress,
mt.JobDesc,
Note = isnull(stuff((select '' + n.Note
from dbo.Note n
where n.EmpID = mt.ID
order by n.ID
for xml path(''),TYPE).value('.','varchar(max)'),1,0,''),'')
from
dbo.MasterTable mt;
go
drop table dbo.MasterTable;
drop table dbo.Note;
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply