Limited to 255 comparisons in CASE statement. Each WHEN … THEN pair is considered 2 comparisons. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is return_expr can itself be a CASE expression The maximum number of components that you can have in a decode function is 255. This includes the expression, search, and result arguments. In the above two statements, for CASE Statements there is solution that CASE can be nested to avoid exceeding the limit of 128 choices. Is there any solution for DECODE Function to avoid exceeding the limit of 255? Thanks in advance

well i think you can use decode statements within decode, that is nest them. Some more knowledgable members can give examples perhaps

Sadik i have posted under this...Oracle Forums > Queries and Discussions > Oracle Database > SQL PL/SQL is that worng, please let me know. Thanks

@Rajavu, Is there any solution for DECODE Function to avoid exceeding the limit of 255? Thanks in advance

Did u check this section of the link ? Code (SQL): SELECT somecolumn, CASE WHEN somecolumn LIKE 'FRA/0001%' THEN CASE WHEN somecolumn LIKE 'FRA/00011%' THEN 'its a 11' WHEN somecolumn LIKE 'FRA/00012%' THEN 'its a 12' WHEN somecolumn LIKE 'FRA/00013%' THEN 'its a 13' WHEN somecolumn LIKE 'FRA/00014%' THEN 'its a 14' WHEN somecolumn LIKE 'FRA/00015%' THEN 'its a 15' WHEN somecolumn LIKE 'FRA/00016%' THEN 'its a 16' WHEN somecolumn LIKE 'FRA/00017%' THEN 'its a 17' WHEN somecolumn LIKE 'FRA/00018%' THEN 'its a 18' WHEN somecolumn LIKE 'FRA/00019%' THEN 'its a 19' END END FROM sometable WHERE somecolumn LIKE 'FRA/0001%'