[OutSystems UI Style Guide Preview] A fix for Oracle database
Forge component by OutSystems R&D
Application Type
Reactive

Hi Team

When publishing this component to Environment that use Oracle Database.
I got following error when access Patterns Screen.
Error executing query. Error in advanced query Get_MenuCategories in GetMenuCategories in Patterns in MainFlow in StyleGuidePreview (SELECT {MenuCategories}.* FROM {MenuCategories} WHERE (@SearchKeyword = '' OR ( {MenuCategories}.[Caption] LIKE '%' + @SearchKeyword + '%' OR EXISTS (SELECT 1 FROM {MenuSubCategory} WHERE {MenuSubCategory}.[MenuCategoryId] = {MenuCategories}.[Id] AND {MenuSubCategory}.[Label] LIKE '%' + @SearchKeyword + '%' )) )): ORA-01722: invalid number

It seems Advanced Query not compatible with Oracle Database, hope you can fix it.
BTW, following are my fix and it worked.
1. Add Input Parameter HasSearchKeyword


2. Edit Query

SELECT {MenuCategories}.*
FROM {MenuCategories}
WHERE (@HasSerachKeyword  = 0 OR (
        {MenuCategories}.[Caption] LIKE @SearchKeyword OR
        EXISTS (SELECT 1 
                FROM {MenuSubCategory}
                WHERE {MenuSubCategory}.[MenuCategoryId] = {MenuCategories}.[Id]
                AND {MenuSubCategory}.[Label] LIKE @SearchKeyword ))
)


3.Set Value as following
- HasSearchKeyword: SearchKeyword <> ""
- SearchKeyword : If(SearchKeyword = "", "", "%" + SearchKeyword + "%")



Regards
Wei

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