I have a Clarion database that I'm trying to pull data from to insert into a sql server database. I'm trying to generate the best query for the job and ran into a snag because of the way the schema is created on the Clarion side. I have two tables and here are their schemas:
Columns: Account, Contact, Address
Columns: Account, Contact, Contact2
The problem is, when they created the Customer table, they made an assumption they would only ever need a max of 2 contacts (Contact, Contact2). Well it turns out, they needed more so they created the Contacts table for 3..N contacts. Seems simple right? Create a join and associate the Customer data with each contact in the Contacts table. The issue is, how can I break the Contact and Contact2 fields into their own row?
The way my application is setup is that I can only use one query at a time so I was hoping someone had some insight on how I could accomplish this. Otherwise, I have to run a couple of processes, to make sure I get all of the contact information like so:
1. Make call to join the Customer and Contacts table and load that data.
2. Make a call to get Contact and load that data.
3. Make a call to get Contact2 and load that data.
Was hoping there was a way to do a fancy join or something to get what I need.
Any insight would be greatly appreciated, thanks.