At 03:09 PM 27-05-01 +0100, you wrote:

>Thanks for the feedback Helen, it's very helpful and the pointers much appreciated.
>
>BTW, my notion that rdb$types.rdb$type and rdb$fields.rdb$field_type are related was based on http://www.ibphoenix.com/doc0259.html "Retrieving Information from the System tables".
>
>The relationship is used here in at least three separate examples, so perhaps there's a documentation update opportunity waiting to be grasped.

Well, those examples are OK, since they are querying metadata types....domains, etc.

but...

Oh-oh-oh-OK! Sorry, wrong explanation for your funny results and misplaced understanding of your expectations. Also, my conclusion from the description of RDB$Types was wrong. It's *meant* to be used for the enumeration of the datatypes and so the last row in your result set wasn't anomalous, it was correct. (You can read the doc as meaning it's NOT currently used for that, and that's how I read it. Apologies! I offer this for you to try instead (to attempt to sort out any anomalies arising from the combination of scrambled join syntax and unwanted left outer joins):

select
r.rdb$field_name r_field, /* actual column name */
f.rdb$field_length f_len, /* length of the actual column referred to */
f.rdb$field_type f_type, /* enumerated datatype of the actual column referred to */
t.rdb$type t_type, /* should be 8 in all cases (all integers) */
t.rdb$type_name t_type_name /* should return name of datatype, i.e. INT64, INT64 and LONG resp. */
from
rdb$relation_fields r
join rdb$fields f
on f.rdb$field_name = r.rdb$field_source
join rdb$types t
on f.rdb$field_type = t.rdb$type
where t.rdb$field_name = 'RDB$FIELD_TYPE'
and r.rdb$relation_name = 'ATABLE';

I can't test it on v. 6 right now but it gives the expected results in 5.6. If you get any nulls then something aint being stored as it should...and it would look like an inconstency.

Regards,
Helen



All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________