Rather than attempt to fix it, they’ve decided to scrap it and migrate all the data to a new, clean, properly-designed db. All well and good, but I’m having nightmares when it comes to extracting the data.

The company responsible for the form-building software gave us query examples for getting out some information, but once we adapt them for our purposes they become far too large.

Example:

Select obstable.nhi_number, obstable.taskname as taskname, obstable.observation_report_id, obstable.stream_id,
Title.Title, Details.Details, Forename. Forename, Surname.Surname, PreferredName.PreferredName, OtherNoTbl.OtherNoTbl, AddressLine2.AddressLine2, PostCode.PostCode, kcid.kcid from (select p.nhi_number, tt.name as taskname, orr.observation_report_id, pp.stream_id
from
patient p, patientprogramme pp, programme pr, observationreport orr, taskrequest tr, tasktype tt
where p.patient_id = pp.patient_id
and pp.programme_id = pr.programme_id
and pr.name = 'CarenapE'
and pp.patient_programme_status = 'ACTIVE'
and p.record_status = 'A'
and p.patient_id = orr.patient_id
and p.patient_id = tr.patient_id
and orr.record_status = 'A'
and orr.taskrequest_id = tr.taskrequest_id
and tr.tasktype_id = tt.tasktype_id
and tt.name = 'CNE_BasicInfo'
and pp.stream_id = tr.stream_id
)as obstable
left outer join
(select ao.observation_report_id, dt.name, ao.value as Title
from
atomicobservation ao
inner join datatype dt
on dt.datatype_id=ao.datatype_id
where dt.name = 'Title')
as Title
on Title.observation_report_id=obstable.observation_report_id
left outer join
(select ao.observation_report_id, dt.name, ao.value as Details
from
atomicobservation ao
inner join datatype dt
on dt.datatype_id=ao.datatype_id
where dt.name = 'Details')
as Details
on Details.observation_report_id=obstable.observation_report_id

etc etc.

I’ve had to split the queries into _Part1 and _Part2 because I was getting the “too many field/tables names (greater than 256)” error message.

My question is this: how can I merge the two parts of the query together so that I get a one-lump large result? I wasn’t sure that I could use Union to join them since they don’t really hold the same fieldnames, or tablenames.

Any ideas?

Thanks,
-Mossie-