How to list each Collation name with its charset

Question:

I want to make a query involving RDB$Character_Sets , RDB$Collations and RDB$Fields in order to get a list of "Fields" with the 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 one line for each RDB$Collation_Name. How to make each RDB$Character_Set_Name, have the corresponding RDB$Collation_Name and only it, reducing to a single line for each RDB$Field_Name? Grateful.

Answer:

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

So the " Where " clause is:

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 collaborated.

Post Views: 0