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

parameter error

newbie1 · 2015-04-10T13:14:03+00:00

Hi everyone, I have a field name, which was created from: select fname || ' ' || mname || ' ' || lname as name from table Now, if I run this query as pl-sql editor: select * from table where name = 'fname mname lname' - it works, …

newbie1

Hi everyone,
 
I have a field name, which was created from:
 
select fname || ' ' || mname || ' ' || lname as name
from table
 
Now, if I run this query as pl-sql editor:
 
select * from table
where name = 'fname mname lname' - it works,
 
If I pass the NParameter in the Birt report design as:
 
select * from table where name = ? in the dataset, I got this error:
 
<div>Caused by: org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: DataSetName</div>
<div>Cannot set the string value () to parameter 7.</div>
<div> org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.</div>
<div>SQL error #1:Invalid column index</div>
<div> </div>
<div>This is how I set/define my parameter.</div>
<div>I got the same error whether I ran it in the Dataset Preview or in View Report. Can you please help me find where I went wrong ?</div>
<div>Thanks</div>
<div> </div>

Find more posts tagged with

Comments

micajblock

My guess is because it is an illegal SQL statement. it might work in SQL but not as a prepare statement. Can you provide more details on the use case? How are you planning on passing the name? Where is it coming from?
 
P.S. there is a BIRTStr.trim (so you do not need left and right trim)
P.P.S Your default value is wrong as it is evaluated before you enter a value.

newbie1

Hi Mica,
The report ask the user to enter the name in this format: "fName mName lName" . I can only think of passing that in as one Reportparameter. Unless, there is a way for me to cut the user's input into 3 fields and pass that into the query ?
 
For example: 
Define my Report Paramater as RP_Name.
In my DataSet parameter define it as DSP_FName; DSP_MName and DSP_LName and in the "Linked to Report Parameter" section, I can do BIRTstrtrim(RP_Name....) using the space between the value as a delimiter ? I'm sorry, I don't know java script so I don't even know where to start of doing this in java and where to put the java script in the BIRT report.
 
Any help is greatly appreciated.
Thanks.

micajblock

How do you verify that format of the name? Do all names exist in your database? If yes, I would typically suggest having a separate query like this:
<pre class="_prettyXprint">
select is, fname || ' ' || mname || ' ' || lname as name
from table</pre>
and the use a list box in the parameter using the ID as the value and the name as display. so your query for the report would look like this:
<pre class="_prettyXprint">
select fname || ' ' || mname || ' ' || lname as name
from table
where id=?</pre>

newbie1

Hi Mica,
thank you for the response. I do have a field set up as "fname || ' ' || mname || ' ' || lname as name" but when user does not know the ID, the user enter the name so I don't think the query above will help unless I'm not understanding your point ?
 
However, I found a solution for the name parameter above with this: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/files/file/1062-multiple-string-search-against-multiple-fields/'>http://developer.actuate.com/community/forum/index.php?/files/file/1062-multiple-string-search-against-multiple-fields/</a> but unfortunately this solution does not work if the report has another parameter. 
 
I have 2 parameters in my report. userid or Name (and the name format must be fname, mname, lname). I'm stuck at the name. Any input is greatly appreciate.

micajblock

What version are you using?

newbie1

<div>Version: Luna Service Release 1 (4.4.1)</div>
<div>Build id: 20140925-1800</div>

micajblock

the point I was trying to make is users should not enter the ID or the name as both are prone to error. Using a drop-down is much better. See attached example to what I mean. I am displaying the name in the drop-down but passing the ID to the query. I am using Classic Models so you should be able to run the report.

newbie1

Yes, that would be the best scenario....but that is not what the user want.

micajblock

OK, can you please provide as much detail as you can for the user requirement?