Fit query from Postgres in Jasper Studio

  Kiến thức lập trình

When I run my query in DBeaver(Postgres), it works. But when I run it in Jasper Studio it gives error. This is my query:

WITH initial_query AS (
    SELECT 
        rrs.description,
        jssof.services_id,
        jssof.dynamic_table_json_obj,
        CASE 
            WHEN jssof.dynamic_table_json_obj LIKE '%c1%' <-----
            THEN jssof.dynamic_table_json_obj::jsonb  <-----
            ELSE null::jsonb
        END AS js_obj
    FROM 
        report.mission_mms_mission mmm 
    LEFT JOIN 
        report.jlo_s2_ss1_own_forces jssof ON jssof.mms_mission_id = mmm.id 
    LEFT JOIN
        report.reference_ref_services rrs ON jssof.services_id = rrs.id 
    WHERE 
        mmm.id = $P{mission_id} 
)

SELECT 
    description,
    services_id,
    obj->>'c1' AS c1_test,
    obj->>'c2' AS c2,
    obj->>'c3' AS c3,
    CASE 
        WHEN obj->'c1'->>'id' IS NOT NULL 
        THEN (obj->'c1'->>'label')::TEXT
        ELSE NULL
    END AS c1_label,
    CASE 
        WHEN obj->>'c1' LIKE '%{%' 
        THEN (obj->'c1'->>'label')::TEXT
        ELSE obj->>'c1'
    END AS c1
FROM 
    initial_query
CROSS JOIN LATERAL jsonb_array_elements(js_obj) AS obj;

I marked ‘<—-‘ on the line that gives out the error:

  1. Multiple markers at this line
  • mismatched input ”%c1%” expecting ‘END’
  • mismatched input ‘LIKE’ expecting ‘THEN’Multiple markers at this line
  1. mismatched input ‘.’ expecting ‘FROM’

I’ve tried using str.contains() and asked ChatGPT. But ChatGPT keeps giving out the same query.

LEAVE A COMMENT