0

sql is not my strong point

upgrading from postgres 9.6 to 10.6 and I get an error from this query:

select 
    CASE WHEN wlh.status_reason_code like '%DR%' THEN
            concat('RR',jsonb_array_elements_text(replace(wlh.status_reason_code,'DR','')::jsonb))      
    ELSE
        ''
    END 
from workflow.workflow_log_history wlh

error: set returning functions are not allowed in CASE

result i get back (from v 9.6):

Concat
RR9
RR8

any help please been stuck on this for a while

Improve this question
6
  • What part of you can't return a set in a CASE isn't clear? Commented Feb 24, 2023 at 4:15
  • Does the array have more than one element? Commented Feb 24, 2023 at 6:35
  • It's unclear to me what the input looks like (the content of status_reason_code). Can you please edit your question (by clicking on the edit link below it) and add some sample input data as formatted text. See here for some tips on how to create nice looking text tables. (edit your question - do not put code or additional information in comments) Commented Feb 24, 2023 at 6:40
  • jsonb_array_elements_text() returns SQL table (SETOF text) - which is not a suitable argument for concat(). You should probably move it to the FROM clause. Commented Feb 24, 2023 at 7:45
  • Your example output isn't much use without also knowing the example input which lead to it. Commented Feb 24, 2023 at 15:46
Related questions
1
.NET/MySQL Connector: Certificate Store Location
14
PHP ignores ssl certificates when connecting to MySql
7
Integrating ssl with mysql - Access Denied
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.