Hi Helen and thanks for the second look.

Running your kindly re-jigged code returns one row. Although there are no nulls, neither are there the three rows that 'ATABLE'
(having three fields) warrants:

r_field f_len f_type t_type t_type_name
--------- ----- ------ ------ -----------
DATAFIELD2 4 8 8 LONG


However, on reflection, it would probably have been much more direct for my original post to have asked 'where is INT64 in the
following?'

select rdb$type, rdb$type_name from rdb$types where rdb$field_name = 'RDB$FIELD_TYPE'

RDB$TYPE RDB$TYPE_NAME
======== ================================
14 TEXT
7 SHORT
8 LONG
9 QUAD
10 FLOAT
27 DOUBLE
35 TIMESTAMP
37 VARYING
261 BLOB
40 CSTRING
45 BLOB_ID
12 DATE
13 TIME

Thanks, Andy.

----- Original Message -----
From: Helen Borrie
To: ib-support@yahoogroups.com
Sent: Sunday, May 27, 2001 5:42 PM
Subject: Re: [ib-support] System Table Inconsistency?


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
_______________________________________________________

Yahoo! Groups Sponsor



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.