U
user3385662
Guest
So frustrated here. I'm not a DB Admin but can get around. I'm writing some ODBC queries against a Progress OpenEdge database that we only have view access to. For the longest time there have been no problems until recently they changed the data structure and for who knows why, they moved customer phone numbers into their own table called "contact" whereas before they were in "cif", where the address etc still remain.
Instead of creating the "contact" table with one row for for each customer and fields for each phone number, they use a code of 0-4, number/email, and customer. So if a customer has 4 phone numbers, they have 4 rows with different code, contact fields and customer name repeated.
I'm trying to join the "contact" table with the "cif" table so it returns each mention of customer in "cif" no matter how many times it is listed in "cif", but include all phone numbers associated from "contact" in each line.
Table structure simplified is like so:
Table "contact"
code | contact(#) | customer
--------------------------------
0 | (123)456-7890 | ABC Corp
1 | (123)456-7891 | ABC Corp
0 | (987)654-3210 | CBA Inc
Table "cif"
customer | b_in_low | b_in_high
----------------------------------
ABC Corp | 50.45 | 134.66
ABC Corp | 64.45 | 188.99
CBA Inc | 12.56 | 890.33
What I'm trying to return is a joined row for each row in "cif" but include all numbers from "contact" so the table above would return:
rsRow1) ABC Corp, 0, (123)456-7890, 2, (123)456-7891, 50.45, 134.66
rsRow2) ABC Corp, 0, (123)456-7890, 2, (123)456-7891, 64.45, 188.99
rsRow3) CBA Inc, 0, (987)654-3210,,, 12.56, 890.00
What I do NOT want:
rsRow1) ABC Corp, 0, #, 50.45, 134.66
rsRow2) ABC Corp, 1, #, 50.45, 134.66
rsRow3) ABC Corp, 0, #, 64.45, 188.99
rsRow4) ABC Corp, 1, #, 64.45, 188.99
rsRow5) CBA Inc, 0, #, 12.56 | 890.00
Make sense? I can get it to work by one rs on the "cif" table and during each repeat region, perform another query on "contact" using the "cif.customer" as a WHERE filter but obviously it is extremely slow and would result in potentially thousands of queries.
I can get it to return only 1 line from "cif" but only 1 number from "contact"
or
I can get it to return up to 5 duplicate "cif" lines with the 5 different phone numbers for each.
So in a nutshell, how can I efficiently get 1 row from "cif" while listing all +-5 phone numbers from "contact"?
Continue reading...
Instead of creating the "contact" table with one row for for each customer and fields for each phone number, they use a code of 0-4, number/email, and customer. So if a customer has 4 phone numbers, they have 4 rows with different code, contact fields and customer name repeated.
I'm trying to join the "contact" table with the "cif" table so it returns each mention of customer in "cif" no matter how many times it is listed in "cif", but include all phone numbers associated from "contact" in each line.
Table structure simplified is like so:
Table "contact"
code | contact(#) | customer
--------------------------------
0 | (123)456-7890 | ABC Corp
1 | (123)456-7891 | ABC Corp
0 | (987)654-3210 | CBA Inc
Table "cif"
customer | b_in_low | b_in_high
----------------------------------
ABC Corp | 50.45 | 134.66
ABC Corp | 64.45 | 188.99
CBA Inc | 12.56 | 890.33
What I'm trying to return is a joined row for each row in "cif" but include all numbers from "contact" so the table above would return:
rsRow1) ABC Corp, 0, (123)456-7890, 2, (123)456-7891, 50.45, 134.66
rsRow2) ABC Corp, 0, (123)456-7890, 2, (123)456-7891, 64.45, 188.99
rsRow3) CBA Inc, 0, (987)654-3210,,, 12.56, 890.00
What I do NOT want:
rsRow1) ABC Corp, 0, #, 50.45, 134.66
rsRow2) ABC Corp, 1, #, 50.45, 134.66
rsRow3) ABC Corp, 0, #, 64.45, 188.99
rsRow4) ABC Corp, 1, #, 64.45, 188.99
rsRow5) CBA Inc, 0, #, 12.56 | 890.00
Make sense? I can get it to work by one rs on the "cif" table and during each repeat region, perform another query on "contact" using the "cif.customer" as a WHERE filter but obviously it is extremely slow and would result in potentially thousands of queries.
I can get it to return only 1 line from "cif" but only 1 number from "contact"
or
I can get it to return up to 5 duplicate "cif" lines with the 5 different phone numbers for each.
So in a nutshell, how can I efficiently get 1 row from "cif" while listing all +-5 phone numbers from "contact"?
Continue reading...