The replacement for double colon to set the value in postgresql-CodePudding

with recursive tree_Gy_Department as(
    select PreGD.*, 1::integer recursion_level
    from GY_DEPARTMENT PreGD
    where PreGD.dept_id = :deptId
    union all
    select NextGD.*, recursion_level  1
    from GY_DEPARTMENT NextGD
             join tree_Gy_Department treeGD on treeGD.parent_id = NextGD.dept_id)
select recursion_level, a.dept_name,
       case
           when recursion_level = 1 then REGEXP_replace(initcap(a.DEPT_NAME), '\\s', '')
           else REGEXP_replace(initcap(a.DEPT_NAME), '[[:lower:]]|\\s', '', 'g') END
           AS Result
from tree_Gy_Department a;

I'm trying to run this query and it works in the console query of PostgreSQL but when I put it in the repository it got an error: ERROR: syntax error at or near ":". I think the error occurred when I set the value for recursion_level "1::level recursion_level", maybe a conflict with hibernate. Does anyone have a replacement for this double colon? thanks.

CodePudding user response：

Use the standard cast() syntax instead. Hibernated gets confused by the ::

cast(1 as integer) as recursion_level

But I don't think you need the cast at all. A simple 1 as recursion_level will work just as well.