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

passing parameter though query like using ?(optional parameter)

syama reddy

Hi,
 
 This is Syam .Am working on Birt tool,Here I got one problem i.e. am passing parameter through query using ? .if i give one input value that time am getting out put .when i am passing null value that time am getting error
 
 
This is my query

select if(? + INTERVAL a + b DAY in (select DATE_FORMAT(emp_attendance.date, '%Y-%m%-%d') from emp_attendance 
left join employee ON emp_attendance.employee_id = employee.id 
left join emp_type ON employee.emp_type_id = emp_type.id 
where month(emp_attendance.date)=? 
and year(emp_attendance.date)=? 
and employee.id=?),(select employee.id 
from emp_attendance 
left join employee ON emp_attendance.employee_id = employee.id 
left join emp_type ON employee.emp_type_id = emp_type.id 
where month(emp_attendance.date)=? 
and year(emp_attendance.date)=? and employee.id=? 
and ? + INTERVAL a + b DAY in (DATE_FORMAT(emp_attendance.date,'%Y-%m%-%d'))),?) as empid 
FROM 
(SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 
 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 
 UNION SELECT 8 UNION SELECT 9 ) d, 
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 
 UNION SELECT 30 UNION SELECT 40) m 
WHERE ? + INTERVAL a + b DAY <= ( SELECT (select date(last_day(? + INTERVAL a DAY )) FROM 
(SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 
 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 
 UNION SELECT 8 UNION SELECT 9 ) d 
WHERE last_day(? + INTERVAL a DAY ) 
group by last_day(? + INTERVAL a DAY ))) 
ORDER BY a + b

Find more posts tagged with

Comments

arya

Hi,
 This is arya, Am working on birt tool 2.5.2 .Here am getting one problem i.e. when i am passing one optional parameter through query using ? that time am getting error .if I pass one value am getting output but if I pass null value am getting error. below am sending my query.In main query am not using any tables
(Here am passing empid .if i pass one empid am getting that particular empid details but when i am passing parameter as null value am not getting all empid details)
 

select if(? + INTERVAL a + b DAY in (select DATE_FORMAT(emp_attendance.date, '%Y-%m%-%d') from emp_attendance 
left join employee ON emp_attendance.employee_id = employee.id 
left join emp_type ON employee.emp_type_id = emp_type.id 
where month(emp_attendance.date)=? 
and year(emp_attendance.date)=? and employee.id=?),(select employee.id 
from emp_attendance 
left join employee ON emp_attendance.employee_id = employee.id 
left join emp_type ON employee.emp_type_id = emp_type.id 
where month(emp_attendance.date)=? 
and year(emp_attendance.date)=? and employee.id=? 
and ? + INTERVAL a + b DAY in (DATE_FORMAT(emp_attendance.date,'%Y-%m%-%d'))),?) as empid 
FROM 
(SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 
 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 
 UNION SELECT 8 UNION SELECT 9 ) d, 
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 
 UNION SELECT 30 UNION SELECT 40) m 
WHERE ? + INTERVAL a + b DAY <= ( SELECT (select date(last_day(? + INTERVAL a DAY )) FROM 
(SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 
 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 
 UNION SELECT 8 UNION SELECT 9 ) d 
WHERE last_day(? + INTERVAL a DAY ) 
group by last_day(? + INTERVAL a DAY ))) 
ORDER BY a + b

kclark

If you're getting no results one way to solve this problem would be to check the value of your parameter in the beforeOpen of your data set. If the parameter is null then you can use
<pre class="_prettyXprint">
this.queryText = "...";</pre>
To reflect a query that doesn't use your parameter.
 
If you're just getting an error can you post the stack?