nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Replacing condition in where clause dynamically

Neelz

Hi All,
 
I am a beginner in BIRT trying to use Javascript to replace (using beforeOpen in script) a condition in the where clause of my query.
 
Query looks like:
 
select * from Table
 where column1 = 'something'
 and ( coalesce(column2,'n') = 'n'
 or coalesce(column3,'n') = 'n'
 )
 
Based on the parameters I choose the number of columns to be mentioned inside the braces of the where clause differs. I shall be using if else condition to mention which set of columns to be passed to the query based on the parameter. I want to know if there a possibility that I can substitute the conditions inside braces with a variable and assigning it values in the beforeOpen script like below:
 
select * from Table
 where column1 = 'something'
 and ( V )
 
and in the beforeOpen script as :
 
this.queryText = this.queryText.replace("V","coalesce(column2,'n') = 'n' or coalesce(column3,'n') = 'n' ");
 
 
I end up getting error while updating this in the Dataset (which is obvious) due to syntax error. Please let me know what would help me achieve this.
 
Thanks!
Neelz

Find more posts tagged with

Comments

micajblock

Instead of V, put something that is syntactically correct like 1=2, and replace that.

Neelz

Thanks for your quick response! 
 
I tried doing that as well with no luck. 
 
Query:
 
select * from Table
where column1 = 'something'
 and ( V = 'Null')
 
Script:
this.queryText.replace("V","COALESCE(U_ENVIRONMENT,'Null')");
 
This throws an error too as attached.

micajblock

Is there V anywhere else in your SQL statement?
 
Your query should look like this:
<pre class="_prettyXprint">
select * from Table
where column1 = 'something'
and ( 1=2)</pre>
and your script should be something like this:
<pre class="_prettyXprint">
this.queryText.replace("1=2","V=COALESCE(U_ENVIRONMENT,'Null')");</pre>

Neelz

It worked! Thank you very much for your time on this.
 
I have now done this:
 
select * from Table 
where column1 = 'something' 
and ( 1=2)
 
and in the script :
 
this.queryText.replace("1=2","COALESCE(U_ENVIRONMENT,'Null') = 'Null' ");
 
Regards,
Neelz

Neelz

Hello Mica,
 
I have another question related to the same report. On further working on it I got a question in mind if I can pass a whole query to the dataset instead of just column names or statement as in the above example.
 
Query is:
 
WITH T1 () 
AS 
( 
SELECT columns FROM table1 
), 
T2 AS 
( 
select * from Table 
 
 where column1 = 'something' 
 
 and ( coalesce(column2,'n') = 'n' 
 
 or coalesce(column3,'n') = 'n' 
 
 )
) 
select * from T2, T1 
where T1.COLUMN = T2.COLUMN

So in the above query how can I replace the whole query in the T2 table instead of just the Coalesce statements? I would be having all the queries that I need to be passing on to this table, in the script area.
Kindly excuse my ignorance!
 
Thanks
Neel

micajblock

Same way. Make the SQL with legal syntax that is unique so that it can be replaced. 
 
I am curious as to why you are doing all this replacement. What is the use case?

Neelz

In my report the no. of columns to be checked in the query differs based on the parameter that is selected. So what I am trying to do is to have a switch statement in place in the beforeOpen where I will be building my queries(to be used in T1 in above example) and pass the respective query alone into the dataset based on the parameter selection.
 
If there a better way of doing it, please do let me know so I can use it. 
 
Thanks
Neel

micajblock

Replace is the easy way to go.

Neelz

Hi. I have not been able to find out which would be a correct syntax to use in the dataset. I am trying to replace the whole query instead of just the where clause as in my earlier example.Please assist.
 
Thanks
Neel

micajblock

Why would it be any different?
<pre class="_prettyXprint">
this.queryText.replace(this.queryText,"a string with your whole query");</pre>

Neelz

I am sorry if my question was not clear.
 
what I meant was what should be used inside the dataset? From your previous example I used '1=2' inside the where clause which was correct syntax. But I am now not sure what should i put inside the dataset window (where query is placed).
 
I am clear what should be used at the script window but just not what to be used in the below inside T1.
 
WITH T1 () 
AS 
( 
<SELECT columns FROM table1> - Need to replace this query 
), 
T2 AS 
( 
select * from Table 
where column1 = 'something' 
and ( coalesce(column2,'n') = 'n' 
or coalesce(column3,'n') = 'n' 
)
) 
select * from T2, T1 
where T1.COLUMN = T2.COLUMN

micajblock

Put anything that is valid query and returns the number of columns you need with the correct data types.