How to pass the values dynamically in a in query when using connect sql

General
, , ,
1

SELECT “PZINSKEY” AS “pzInsKey”, “CASEID” AS “CaseId” from Data.TABLENAME WHERE “PZINSKEY” IN ‘{ASIS:.pyDescription}’

Tried multiple ways :

Commas separated

Value list property

Comma separated with "

Comma separated with '

Can somebody tell me if it’s possible to pass the values inside a in query dynamically from a property.

In my use case I have to take union of 4/5 tables and hence I don’t want to run a report definition.

2

@nikhilk7251 please tell me if my understanding is correct. If you are hardcoding ASIS with comma seperated values it’s working but you are not able to pass this value dynamically right?

3

@nikhilk7251 can you try calling connect sql within a page context. On the page set commas seperated values and refer the same within the query. Also please refer below URL for reference where instead of IN they are using equal.

https://support.pega.com/question/how-pass-parameter-value-connect-sql-inserting-or-selecting-data

4

Hi @nikhilk7251,

Select * from data.pr_abc_paymentg_data_card where CardNumber = ‘{ASIS:.CardNumber}’ is working properly

but when I tried using IN it is not working properly.

Thanks

5

@UdaySimhaReddy Are you passing multiple values in CardNumber property, if so can u please share how are you setting the value and the query the system in generating in the backend.

I tried hardcoding the value in a text property as “"value1", "value2", "value3"” and used the same as equal to instead of in but not working.

6

@Anoop Krishna Did you try this solution because when I tried hardcoding the value in a text property as “'value1', 'value2', 'value3'” and used the same as equal to instead of in it’s pulling 0 results.

7

I am able to pass the values dynamically using in .

8

@nikhilk7251 great. Did you add anything else? Can please post here, what changes have you done to make this work? So that will help others.

9

@nikhilk7251 can you try by keeping just comma seperated values with equals.

equals a, b, c

10

Split the comma‑separated values in {QueryPage.pyNote} and return rows where pyID matches any of them.

Use string_to_array({QueryPage.pyNote}, ‘,’). This is a PostgreSQL SQL construct used to filter rows using a comma‑separated list passed as a single string.

Which converts a comma‑separated string into an array

example:
‘D-1,D-2,D-3’ → {‘D-1’,‘D-2’,‘D-3’}

Query:

SELECT pzInsKey, pyID
FROM worktable
WHERE pyID = ANY (string_to_array({QueryPage.pyNote}, ','))

1 Like