>Hi all

Hi Daniel!

>I need some direction? I'm converting a progress database(BDE ODBC) to
>IB60(firebird) and come across a huge table 100 fields.

>I figure it's better to break it up in 3 tables. Now this may sound silly

but

>how do I put it back together with SQL?? create a key to link together them
>and use SQL selects? 3 IBOqueries because all the data needs to display on
>2-20in monitors at the same time. would I gain or lose from doing this? or

is

>100 fields too much for a table. Maybe I'm better to leave it that size ??

I don't think 100 fields should be any problem (except for the persons
viewing your dataset, how would anyone consume information from 100 fields
at the same time?), but joining three tables should work as well. The only
drawback is if you want to update more than one of the tables in your
select, you may have to write a stored procedure (I'm not familiar enough
with views to exclude them, but I think that they cannot be used to update
multiple tables).

First: Yes, you must have keys to link the tables together (in my examples
I've assumed FIELD1 is a unique key for all three tables).

For read-only selects or selects updating one table only, you can use
something like

SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2, C.FIELD1, C.FIELD2
FROM A
JOIN B ON B.FIELD1=A.FIELD1
JOIN C ON C.FIELD1=A.FIELD1
WHERE A.FIELD2 = 'DANIEL'

This would get you all records that has DANIEL in A.FIELD2 and matching
records in B and C. If you want records which doesn't have entries in B or
C, you'd have to use a different approach and things get a little more
difficult, like:

SELECT A.FIELD1, A.FIELD2,
(SELECT B.FIELD1 FROM B WHERE B.FIELD1=A.FIELD1) AS FIELD3,
(SELECT B.FIELD2 FROM B WHERE B.FIELD1=A.FIELD1) AS FIELD4
FROM A
WHERE A.FIELD2 = 'DANIEL'
(this will only work if B.FIELD1 uniquely matches A.FIELD1 for 'DANIEL')

I've only tried to answer your question HOW. I suggest you also ask
yourself the question WHY if you haven't already. If the three tables are
distinct, why get all the information into the same query rather than
having three queries? There are many good reasons for doing so, but you
ought to consider it each time. I also wonder why you use IBOQuery. Again,
you may have good reasons for doing so, but the native IB_Cursor and
IB_Query do have advantages like better control and useful components that
are simple to use.

Set