-1

I running a BI Publisher report through SOAP request. the BI Publisher data model contain the following condition :

WHERE PERIOD_NAME IN ( :P_PERIOD_NAME )

when i pass single period like "Jan-21" it works fine but then when i pass "Jan-21, Feb-21" it fails.

how to pass comma separated values ?

MT0
  • 143,790
  • 11
  • 59
  • 117
Shlomo
  • 21
  • 5

2 Answers2

0

You are passing in a single string (that happens to contain commas) and not a comma-separated list of multiple values so your query is effectively:

WHERE period_name IN ( 'Jan-21, Feb-21' )

Instead, surround both the delimited list and the list item in delimiters (so that you do not get a partial match) and then match on the substring:

WHERE ', ' || :P_PERIOD_NAME || ', ' LIKE '%, ' || period_name || ', %'

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Where to add this ? in the data model ? – Shlomo Jul 15 '21 at 13:30
  • Where did you have the previous `WHERE` expression? You tagged the question as Oracle so I gave an Oracle solution. – MT0 Jul 15 '21 at 13:33
  • The SQL failed when changing the following from: WHERE PERIOD_NAME IN ( :P_PERIOD_NAME ) to WHERE ', ' || :P_PERIOD_NAME || ', ' LIKE '%, ' || period_name || ', %' – Shlomo Jul 15 '21 at 13:36
  • moreover the question is tagged as both "SOAP" and "Oracle" and i prefer to modify the SOAP request to accept comma separated parameter values – Shlomo Jul 15 '21 at 13:43
  • What does "failed" mean? The query works in Oracle [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=ac0322ae631f7a323d45c02ffa224410). Also, if you are looking for a specific type of solution then you sould make that explicit in your question and edit your question to include a [MRE] with sufficient detail to replicate your SOAP data/models/setup etc. rather than giving half the information. – MT0 Jul 15 '21 at 13:49
0

Let try this query

SELECT *
FROM TABLE1
WHERE 1=1
AND PERIOD_NAME IN (
select regexp_substr('Jan-21,Feb-21','[^,]+', 1, level) as period from dual
connect by regexp_substr('Jan-21,Feb-21', '[^,]+', 1, level) is not null
);
Hung Le
  • 141
  • 9
  • Hi, My BI Publisher data model is : SELECT PERIOD_NAME FROM GL_PERIODS WHERE 1 = 1 AND PERIOD_NAME IN (:P_GL_PERIOD_NAME ) – Shlomo Jul 15 '21 at 14:40
  • Hi Shlomo, Based on my example you can change the model accordingly – Hung Le Jul 15 '21 at 14:42
  • 1
    SELECT PERIOD_NAME FROM GL_PERIODS WHERE 1 = 1 AND PERIOD_NAME IN ( select regexp_substr(:P_GL_PERIOD_NAME,'[^,]+', 1, level) as period from dual connect by regexp_substr(:P_GL_PERIOD_NAME, '[^,]+', 1, level) is not null ); – Hung Le Jul 15 '21 at 14:43
  • It works only for one period value. but when selecting more then one period i get oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number – Shlomo Jul 15 '21 at 14:50
  • Can you try with parameter 'Jan-21,Feb-21' it's haven't space in comma seperated – Hung Le Jul 15 '21 at 15:05
  • The following : SELECT PERIOD_NAME FROM GL_PERIODS WHERE 1 = 1 AND PERIOD_NAME IN ( select regexp_substr('Jan-21,Feb-21','[^,]+', 1, level) as period from dual connect by regexp_substr('Jan-21,Feb-21', '[^,]+', 1, level) is not null ) return Jan-21 Feb-21 – Shlomo Jul 15 '21 at 15:13
  • I see it as duplicate data, can use DISTINCT to remove duplicate. – Hung Le Jul 15 '21 at 15:15
  • So it's ok or not? any issue from there? – Hung Le Jul 15 '21 at 15:16
  • Can you tell how to change your original query to accept it with space ? 'Jan-21, Feb-21' ? because this is the way the BI Publisher convert the user parameter selection – Shlomo Jul 15 '21 at 15:20
  • Suggest using TRIM to remove space in a string. – Hung Le Jul 15 '21 at 15:24
  • 1
    SELECT PERIOD_NAME FROM GL_PERIODS WHERE 1 = 1 AND PERIOD_NAME IN ( SELECT TRIM(PERIOD) FROM ( select regexp_substr(:P_GL_PERIOD_NAME,'[^,]+', 1, level) as period from dual connect by regexp_substr(:P_GL_PERIOD_NAME, '[^,]+', 1, level) is not null ) ); – Hung Le Jul 15 '21 at 15:24
  • Still not. the way BI Publisher convert the parameter input into XML tag is : P_GL_PERIOD_NAME ([Feb-21,Jan-21]) so i guess there is need to remove also the [ ] – Shlomo Jul 15 '21 at 15:27
  • You can remove character ([]) with function REPLACE – Hung Le Jul 15 '21 at 15:29
  • SELECT PERIOD_NAME FROM GL_PERIODS WHERE 1 = 1 AND period_set_name = 'OTI Calendar' AND PERIOD_NAME IN ( SELECT TRIM(PERIOD) FROM ( select regexp_substr(REPLACE(REPLACE(:P_GL_PERIOD_NAME,'['),']'),'[^,]+', 1, level) as period from dual connect by regexp_substr(REPLACE(REPLACE(:P_GL_PERIOD_NAME,'['),']'), '[^,]+', 1, level) is not null ) ) – Shlomo Jul 15 '21 at 15:34