How to list each Collation name with its charset - Firebird 2.5

Asked

Viewed 662 times

3

I want to make an appointment involving RDB$Character_sets, RDB$Collations and RDB$Fields in order to obtain a list of "Fields" with their respective RDB$Character_set_name and RDB$Collation_name.

SQL statement used:

 select distinct
        Fields    .RDB$Field_Name        ,
        CharSets  .RDB$Character_Set_Name,
        Collations.RDB$Collation_Name
 from
        RDB$Character_Sets as CharSets  ,
        RDB$Collations     as Collations,
        RDB$Fields         as Fields
 where
     CharSets  .RDB$Character_Set_Id = Collations.RDB$Character_Set_Id and
     Fields    .RDB$Character_Set_Id = CharSets  .RDB$Character_Set_Id 
 order by
                        Fields    .RDB$Field_Name

Result obtained:

 RDB$Field_Name   RDB$Character_Set_Name   RDB$Collation_Name
 --------------   ----------------------   ------------------
 RDB$10           UTF8                     UCS_BASIC
 RDB$10           UTF8                     UNICODE
 RDB$10           UTF8                     UNICODE_CI
 RDB$10           UTF8                     UNICODE_CI_AI
 ...              ...                      ...

That is, there is a line for each RDB$Collation_name. How to make each RDB$Character_set_name, has the corresponding RDB$Collation_name and only it, reducing to a single row for each RDB$Field_name? Grateful.

firebird

  • An effective way would be to use group by, but in the tests I did here the results were not satisfactory.

  • Rapha, display visually how you want your results to appear. The problem is that each charset has multiple 'collations';

  • Hello Fabrício. This was done in the body of the question: it has a visual representation of how ñ I want to appear in "Obtained result". Below is a text explaining how I want q to appear (each Field_name with its respective and unique Char_set_name and Collation_name). See tbm the answer I gave to the question. In my test it worked.

1 answer

1

After better analyzing the tables RDB$Character_Sets, RDB$Collations and RDB$Fields found that the univocal identification between the "Character Set" and the "Collation" is found in the table "RDB$Collations", where the fields appear "RDB$Character_Set_Id" and "RDB$Collation_Id". In turn the table "RDB$Fields", has also these columns, therefore the clause "Where" has to limit by these linking columns "RDB$Fields" to "RDB$Collations" in RDB$Character_set_id and "RDB$Collation_Id". The "RDB$Collation_Name" is obtained from the table "RDB$Collations" and the "RDB$Character_Set_Name" is obtained from the table "RDB$Character_Sets" where the "RDB$Fields.RDB$Character_Set_Id" = "RDB$Character_Sets.RDB$Character_Set_Id".

Thus the clause "Where" stays:

CharSets.RDB$Character_Set_Id = Collations.RDB$Character_Set_Id and
Fields  .RDB$Character_Set_Id = Collations.RDB$Character_Set_Id and
Fields  .RDB$Collation_Id     = Collations.RDB$Collation_Id

I hope I’ve cooperated.

Browser other questions tagged firebird

You are not signed in. Login or sign up in order to post.