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

Question qith multiple parameters in an SQL Query

sjpondiong

Okay, I have several questions with regards to setting multiple parameters in an SQL Query for report design file. 
 
Lets say I have a query with this type of format: 
 
SELECT firstname, lastname, employeeNum, position, dateHired, dateLeft 
FROM Employees 
WHERE position = *a1a AND dateHired BETWEEN TIMESTAMP(*b2b) AND TIMESTAMP(*c2c); 
 
legend: a1a = String parameter (Alphanumeric) 
b2b = DateTime parameter 
c2c = DateTime parameter 
 
Here's my question: 
 
1.) If I place it like this, "WHERE position = ? AND dateHired BETWEEN TIMESTAMP(?) AND TIMESTAMP(?)", will the Designer itself map out the parameters to the statement like place the string parameter and the date time parameter to their proper places, or should I create a variable to make sure that the parameters will be passed properly? 
 
2.) How can I best implement that, If in case I need to create a script or variable to map out the parameters correctly in the query? 
 
3.) any suggestions on how I can make this more simplier? 
 
Any help is appreciated... thanks

Find more posts tagged with

Comments

mwilliams

Hi sjpondiong,

If you use the ?'s, BIRT creates a dataSet paramter for each ? used in your query. They are in order of appearance in the query, so you'll just have to attach them to report parameters or the appropriate values with this in mind.

sjpondiong

Okay. Just to be clear, if the parameters in the query should be date, I should also placed them as Date type in the report design right?

thanks

sjpondiong

Ok, I did this one and I got this error... please refer to the attached document. 
 
My SQL Query looks like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT
EMPLOYEE ID,
FIRSTNAME,
LASTNAME,
JOB_POSITION,
DATE_HIRED,
DATE_LEFT,
STATUS
FROM
EMPLOYEE
WHERE
DATE_HIRED >= DATE(?)
AND DATE_HIRED < DATE(?)
ORDER BY
EMPLOYEE ID DESC</pre>
 
 
I have two parameters, startDate and endDate, both identified as type Date. 
 
Any thoughts?

sjpondiong

<blockquote class='ipsBlockquote' data-author="'sjpondiong'" data-cid="70235" data-time="1289500550" data-date="11 November 2010 - 11:35 AM">
Ok, I did this one and I got this error... please refer to the attached document. 
 
My SQL Query looks like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT
EMPLOYEE ID,
FIRSTNAME,
LASTNAME,
JOB_POSITION,
DATE_HIRED,
DATE_LEFT,
STATUS
FROM
EMPLOYEE
WHERE
DATE_HIRED >= DATE(?)
AND DATE_HIRED < DATE(?)
ORDER BY
EMPLOYEE ID DESC</pre>
 
 
I have two parameters, startDate and endDate, both identified as type Date. 
 
Any thoughts? </blockquote>
 
Nevermind with this one... I found my mistake... I removed the DATE(?) in the parameters and replaced them with just ? and they worked.

mwilliams

Great! Let us know whenever you have questions!