DB2 Error executing sql statement: [SQL0104] Token ) was not valid-CodePudding

I am getting the following error from DB2, using custom driver: com.ibm.as400.access.AS400JDBCDriver

Error executing sql statement: [SQL0104] Token ) was not valid. Valid tokens: , FROM INTO.; Caused by: [SQL0104] Token ) was not valid. Valid tokens: , FROM INTO.

Narrowed it down to this bit which works in SQL server, we are a bit stumped as syntax appears fine.

SELECT CASE
          WHEN Abs(ho.ohmisc) > 0 
          THEN
               (
                  SELECT LEFT(s, Translate('%[^0-9]%', s) - 1)
                  FROM (
                     SELECT Substr(
                         ohunpo, 
                         Translate('%[0-9]%', ohunpo), 
                         Len(ohunpo)
                     ) AS s 
                  ) t 
               )
          ELSE odrord
       END ODRORD
FROM   qs36f.hoehead HO
QS36F.hoehead  HO

CodePudding user response：

You are missing a FROM

SELECT CASE
          WHEN Abs(ho.ohmisc) > 0 
          THEN
               (
                  SELECT LEFT(s, Translate('%[^0-9]%', s) - 1)
                  FROM (
                     SELECT Substr(ohunpo, 
                                   Translate('%[0-9]%', ohunpo), 
                                   Len(ohunpo)
                                   ) AS s 
                        -- **You need a FROM here**
                        ) t 
               )
          ELSE odrord
       END ODRORD
FROM   qs36f.hoehead HO
QS36F.hoehead  HO

But that's not your only issue...

This is not valid either, are you trying to join the table to itself?

FROM   qs36f.hoehead HO
QS36F.hoehead  HO

Your embedded selects likely aren't right either....but without knowing your table structure and seeing some example source data and what you're wanting as output, I can't say for sure.