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; } } }

Dynamic SQL Where Clause

vibharavi2001

I'm trying to put some where clause in my report. Here is what I did

1) I've created a parameter Prm_StageID ( this is one of the columns in my Dataset)
2) In the data set's beforeOpen method I've a script

if(params["Prm_StageID"].value != null && params["Prm_StageID"].value != ""){
this.queryText = this.queryText +
" where Prm_StageID in (" +
reportContext.getParameterValue("Prm_StageID") + ")";
}

But the report is giving an Oracle Error:

SQL error #1: [ActuateD][Oracle JDBC Driver][Oracle]ORA-00933 :Sql command not properly ended

Could some one please help me in this?

Thanks in Advance,
Ravi

Find more posts tagged with

Comments

KavithaD

Hi Ravi,

Is Prm_StageID has String Data Type? If so reportContext.getParameterValue("Prm_StageID") should be there in side single quote.If it does not works Please post the Complete Exception.

Thank you,
Kavitha.

kdureidy

try to do it this way

var query = " select whatever from myTable ";

if(if(params["Prm_StageID"].value != null && params["Prm_StageID"].value != ""){
query = query + " where Prm_StageID in( params["Prm_StageID"].toString());
}

//params["Prm_StageID"] must be a list, and comma seperated.
//if this does not work, please send me the rptdesign by email, and i will fix it for you.
my email: ck.d.2000@gmail.com

Thanks,
Khalil.

vibharavi2001

Kavitha,

Thankyou for the reply, I made the changes you mentioned above, now it give the below error:

SQL error #1: [ActuateD][Oracle JDBC Driver][Oracle]ORA-00904 : "Reward": invalid Idendtifier
Reward is my default param value

Thanks,
Ravi

KavithaD

Could you replicate the same issue with a sample report connecting to classic model db.so that i can work on it to resolve the issue.

johnw

These articles will answer how to do what your trying to do in a much easier and much safer way than dynamically generating your where clause, which will leave you open to SQL Injection attacks.

http://birtworld.blogspot.com/2009/04/multi-select-parameters-part-2.html
http://birtworld.blogspot.com/2009/03/birt-multi-select-statements.html

John