[Feature Toggle Library] Not compatible with Oracle db
Forge component by Experts
Application Type

It looks like most of the Advanced SQL queries are not compatible with Oracle (ie using TOP () and FORMAT etc) and they are returning Errors.

Latest Version 1.0.2 now works great with Oracle - Thanks

Hi Charles,

Do you have SQL issue with the Feature Toggle Library component? This component focuses on the release/deployments.



Hi Swatantra

Both components return sql errors in Oracle. A quick glance through shows CAST, TOP and FORMAT are causing a problem in the Advanced SQL actions.


Latest Version 1.0.2 now works great with Oracle - Thanks

@Charles Hewitt which version of Oracle are you using? I'm stuck with 11g and kept getting the error ORA-00918: column ambiguously defined until I changed the advanced SQL 


adding alias to those IsOn and IsConditionalOn columns

WITH FTWithTags AS ( SELECT {FeatureToggleTag}.[FeatureToggleId] FTId
                    FROM {FeatureToggleTag}
                    INNER JOIN {Tag}
                    ON {Tag}.[Id] = {FeatureToggleTag}.[TagId]
                    WHERE {Tag}.[Name] like '%' || @SearchKeyword || '%'
                    GROUP BY {FeatureToggleTag}.[FeatureToggleId])

    FTC_0.[IsOn] IsOn_0,
    FTC_0.[IsConditionalOn] IsConditionalOn_0,
    FTC_1.[IsOn] IsOn_1, 
    FTC_1.[IsConditionalOn] IsConditionalOn_1,
    FTC_2.[IsOn] IsOn_2, 
    FTC_2.[IsConditionalOn] IsConditionalOn_2, 
    FTC_3.[IsOn] IsOn_3, 
    FTC_3.[IsConditionalOn] IsConditionalOn_3,
    FTC_4.[IsOn] IsOn_4, 
    FTC_4.[IsConditionalOn] IsConditionalOn_4,
    FTC_5.[IsOn] IsOn_5, 
    FTC_5.[IsConditionalOn] IsConditionalOn_5, 
    FTC_6.[IsOn] IsOn_6, 
    FTC_6.[IsConditionalOn] IsConditionalOn_6,
    CASE WHEN @IsAdministrator=1 OR {FeatureToggleTeam}.[Id] IS NULL THEN 1 ELSE LTU_0.[HasChangePermissions] END,
    CASE WHEN @IsAdministrator=1 OR {FeatureToggleTeam}.[Id] IS NULL THEN 1 ELSE LTU_1.[HasChangePermissions] END,
    CASE WHEN @IsAdministrator=1 OR {FeatureToggleTeam}.[Id] IS NULL THEN 1 ELSE LTU_2.[HasChangePermissions] END,
    CASE WHEN @IsAdministrator=1 OR {FeatureToggleTeam}.[Id] IS NULL THEN 1 ELSE LTU_3.[HasChangePermissions] END,
    CASE WHEN @IsAdministrator=1 OR {FeatureToggleTeam}.[Id] IS NULL THEN 1 ELSE LTU_4.[HasChangePermissions] END,
    CASE WHEN @IsAdministrator=1 OR {FeatureToggleTeam}.[Id] IS NULL THEN 1 ELSE LTU_5.[HasChangePermissions] END,
    CASE WHEN @IsAdministrator=1 OR {FeatureToggleTeam}.[Id] IS NULL THEN 1 ELSE LTU_6.[HasChangePermissions] END
FROM {FeatureToggle}
    LEFT JOIN {FeatureToggleConfiguration} FTC_0 ON FTC_0.[FeatureToggleId] = {FeatureToggle}.[Key] AND FTC_0.[EnvironmentKey] = @Env0_Key 
    LEFT JOIN {FeatureToggleConfiguration} FTC_1 ON FTC_1.[FeatureToggleId] = {FeatureToggle}.[Key] AND FTC_1.[EnvironmentKey] = @Env1_Key 
    LEFT JOIN {FeatureToggleConfiguration} FTC_2 ON FTC_2.[FeatureToggleId] = {FeatureToggle}.[Key] AND FTC_2.[EnvironmentKey] = @Env2_Key 
    LEFT JOIN {FeatureToggleConfiguration} FTC_3 ON FTC_3.[FeatureToggleId] = {FeatureToggle}.[Key] AND FTC_3.[EnvironmentKey] = @Env3_Key 
    LEFT JOIN {FeatureToggleConfiguration} FTC_4 ON FTC_4.[FeatureToggleId] = {FeatureToggle}.[Key] AND FTC_4.[EnvironmentKey] = @Env4_Key 
    LEFT JOIN {FeatureToggleConfiguration} FTC_5 ON FTC_5.[FeatureToggleId] = {FeatureToggle}.[Key] AND FTC_5.[EnvironmentKey] = @Env5_Key 
    LEFT JOIN {FeatureToggleConfiguration} FTC_6 ON FTC_6.[FeatureToggleId] = {FeatureToggle}.[Key] AND FTC_6.[EnvironmentKey] = @Env6_Key 
    LEFT JOIN {FeatureToggleTeam} ON {FeatureToggleTeam}.[FeatureToggleId] = {FeatureToggle}.[Key]
    LEFT JOIN {LifetimeTeam} ON {LifetimeTeam}.[GUID] = {FeatureToggleTeam}.[LifetimeTeamId]
    LEFT JOIN {LifetimeTeamUser} LTU_0 ON LTU_0.[LifetimeTeamId] = {LifetimeTeam}.[GUID] AND LTU_0.[UserId] = (SELECT {User}.[External_Id] FROM {User} WHERE {User}.[Id]=@UserId) AND LTU_0.[EnvironmentKey] = @Env0_Key
    LEFT JOIN {LifetimeTeamUser} LTU_1 ON LTU_1.[LifetimeTeamId] = {LifetimeTeam}.[GUID] AND LTU_1.[UserId] = (SELECT {User}.[External_Id] FROM {User} WHERE {User}.[Id]=@UserId) AND LTU_1.[EnvironmentKey] = @Env1_Key
    LEFT JOIN {LifetimeTeamUser} LTU_2 ON LTU_2.[LifetimeTeamId] = {LifetimeTeam}.[GUID] AND LTU_2.[UserId] = (SELECT {User}.[External_Id] FROM {User} WHERE {User}.[Id]=@UserId) AND LTU_2.[EnvironmentKey] = @Env2_Key
    LEFT JOIN {LifetimeTeamUser} LTU_3 ON LTU_3.[LifetimeTeamId] = {LifetimeTeam}.[GUID] AND LTU_3.[UserId] = (SELECT {User}.[External_Id] FROM {User} WHERE {User}.[Id]=@UserId) AND LTU_3.[EnvironmentKey] = @Env3_Key
    LEFT JOIN {LifetimeTeamUser} LTU_4 ON LTU_4.[LifetimeTeamId] = {LifetimeTeam}.[GUID] AND LTU_4.[UserId] = (SELECT {User}.[External_Id] FROM {User} WHERE {User}.[Id]=@UserId) AND LTU_4.[EnvironmentKey] = @Env4_Key
    LEFT JOIN {LifetimeTeamUser} LTU_5 ON LTU_5.[LifetimeTeamId] = {LifetimeTeam}.[GUID] AND LTU_5.[UserId] = (SELECT {User}.[External_Id] FROM {User} WHERE {User}.[Id]=@UserId) AND LTU_5.[EnvironmentKey] = @Env5_Key
    LEFT JOIN {LifetimeTeamUser} LTU_6 ON LTU_6.[LifetimeTeamId] = {LifetimeTeam}.[GUID] AND LTU_6.[UserId] = (SELECT {User}.[External_Id] FROM {User} WHERE {User}.[Id]=@UserId) AND LTU_6.[EnvironmentKey] = @Env6_Key
WHERE {FeatureToggle}.[IsActive] = 1
    AND ({FeatureToggle}.[Name] like '%' || @SearchKeyword || '%'
    OR {FeatureToggle}.[Key] like '%' || @SearchKeyword || '%'
    OR {FeatureToggle}.[Key] IN (SELECT FTWithTags.FTId FROM FTWithTags))
    AND (@Env0_ActiveFilter = 2 OR FTC_0.[IsOn] = @Env0_ActiveFilter )
    AND (@Env1_ActiveFilter = 2 OR FTC_1.[IsOn] = @Env1_ActiveFilter )
    AND (@Env2_ActiveFilter = 2 OR FTC_2.[IsOn] = @Env2_ActiveFilter )
    AND (@Env3_ActiveFilter = 2 OR FTC_3.[IsOn] = @Env3_ActiveFilter )
    AND (@Env4_ActiveFilter = 2 OR FTC_4.[IsOn] = @Env4_ActiveFilter )
    AND (@Env5_ActiveFilter = 2 OR FTC_5.[IsOn] = @Env5_ActiveFilter )
    AND (@Env6_ActiveFilter = 2 OR FTC_6.[IsOn] = @Env6_ActiveFilter )
    AND (@IsAdministrator=1 OR {FeatureToggleTeam}.[Id] IS NULL OR EXISTS 
        (SELECT 1 FROM {LifetimeTeamUser} WHERE {LifetimeTeamUser}.[UserId] = (SELECT {User}.[External_Id] FROM {User} WHERE {User}.[Id]=@UserId) AND {LifetimeTeamUser}.[LifetimeTeamId] = {LifetimeTeam}.[GUID])) 

ORDER BY @TableSort
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.