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

Navigation
1

I want to make a query involving RDB $ Character_Sets , RDB $ Collations and RDB $ Fields to get a list of "Fields" with their 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, you have one row 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? Thankful.

    
asked by anonymous 02.06.2016 / 14:19

1 answer

0

After analyzing the tables RDB$Character_Sets , RDB$Collations and RDB$Fields , I found that the unique identifier between "Character Set" and "Collation" is in the table " RDB$Collations ", " RDB$Character_Set_Id " and " RDB$Collation_Id ". In turn, the " RDB$Fields " table also has these columns, so the " Where " clause must be limited by these columns by associating " RDB$Fields " with " RDB$Collations " in RDB $ Character_Set_Id and " RDB$Collation_Id " RDB$Collation_Name ". The " RDB$Collations " is obtained from the table " RDB$Character_Set_Name " and the " RDB$Character_Sets " is obtained from the table " RDB$Fields.RDB$Character_Set_Id " where " RDB$Character_Sets.RDB$Character_Set_Id "=" Where ".

So, the " %code% " 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 have collaborated.

    
02.06.2016 / 17:35
Form creation sometimes stays behind main form Task with parameter in vraptor-tasks