Home » Archived » BIRT » Null parameter(How to pass null parameter)
| Null parameter [message #1062237] |
Thu, 06 June 2013 12:16  |
Eclipse User |
|
|
|
Hi Experts,
As per my requirement one parameter is optional, If there is no value in the parameter iam getting error. how to pass null parameter in report at run time.
Thanks
RED
|
|
| |
| Re: Null parameter [message #1063637 is a reply to message #1062555] |
Thu, 13 June 2013 10:54  |
| Eclipse User |
|
|
|
Yes uncheck the check box, but it is throughing the error. I have changed the scenario and written script on dataSet also getting error. Attached is the report and below is the error. Please suggest me.
Thanks,
The following items have errors:
Script on Dataset
Table (id = 6):
+ There are errors evaluating script "queryText = "Select employeenumber,";
queryText = queryText + "lastname,";
queryText = queryText + "firstname,";
queryText = queryText + "extension,";
queryText = queryText + "email,";
queryText = queryText + "officecode,";
queryText = queryText + "reportsto,";
queryText = queryText + "jobtitle";
queryText = queryText + " from employees";
if (params["P_employeenumber"] && params["P_employeenumber"].value.length > 0) {
queryText = queryText + "where employeenumber >= " + params["P_employeenumber"].value;
}
else {
queryText = "Select employeenumber,";
queryText = queryText + "lastname,";
queryText = queryText + "firstname,";
queryText = queryText + "extension,";
queryText = queryText + "email,";
queryText = queryText + "officecode,";
queryText = queryText + "reportsto,";
queryText = queryText + "jobtitle";
queryText = queryText + " from employees";
}".
|
|
| | |
| Re: Null parameter [message #1064480 is a reply to message #1064394] |
Wed, 19 June 2013 09:07 |
| Eclipse User |
|
|
|
Thanks Williams,
There is nothing in the + sign. below is the message after clicking on the +.
Table (id = 6):
- There are errors evaluating script "queryText = "Select employeenumber,";
queryText = queryText + "lastname,";
queryText = queryText + "firstname,";
queryText = queryText + "extension,";
queryText = queryText + "email,";
queryText = queryText + "officecode,";
queryText = queryText + "reportsto,";
queryText = queryText + "jobtitle";
queryText = queryText + " from employees";
if (params["P_employeenumber"] && params["P_employeenumber"].value.length > 0) {
queryText = queryText + "where employeenumber >= " + params["P_employeenumber"].value;
}
else {
queryText = "Select employeenumber,";
queryText = queryText + "lastname,";
queryText = queryText + "firstname,";
queryText = queryText + "extension,";
queryText = queryText + "email,";
queryText = queryText + "officecode,";
queryText = queryText + "reportsto,";
queryText = queryText + "jobtitle";
queryText = queryText + " from employees";
}".
|
|
| | | | |
| Re: Null parameter [message #1065831 is a reply to message #1062237] |
Thu, 27 June 2013 12:25 |
| Eclipse User |
|
|
|
My DataSet query as
SELECT DISTINCT b.segment1 Brand,b.segment2 Type,
( SUM (NVL(msi.list_price_per_unit,0))) cog,
( SUM (d.quantity_invoiced)) net_units,
( SUM (NVL(d.unit_selling_price,0))) tot_unit_price,
( SUM (NVL(c.exchange_rate,1))) totExc,
( SUM (d.quantity_invoiced)) tot_sold_qty,
( sum (nvl(d.extended_amount,0) * nvl(c.exchange_rate,1))) NetSales,
( SUM (DECODE(e.TYPE, 'CM', (nvl(d.extended_amount,0) * nvl(c.exchange_rate,1)), 0))) Returns,
( SUM (DECODE(e.TYPE, 'INV', (nvl(d.extended_amount,0) * nvl(c.exchange_rate,1)), 'DM', (nvl(d.extended_amount,0) * nvl(c.exchange_rate,1)),0))) gross_amount,
DECODE(b.segment1,
'M', DECODE(b.segment2, 'F', 'Modo Frames',
'C', 'Modo Clips',
'S', 'Modo Sun',
'H', 'Modo Cases',
'X', 'Modo Spareparts', 'Modo'),
'S', DECODE(b.segment2, 'F', 'Saki Frames',
'C', 'Saki Clips',
'S', 'Saki Sun',
'H', 'Saki Lens',
'X', 'Saki Others', 'Saki'),
'K', DECODE(b.segment2, 'F', 'Keo Frames',
'C', 'Keo Clips',
'S', 'Keo Sun',
'H', 'Keo Lens',
'X', 'Keo Others', 'Keo'),
'P', DECODE(b.segment2, 'F', 'M+ Frames',
'C', 'M+ Clips',
'K', 'M+ Frame & Clip',
'S', 'M+ Sun',
'H', 'M+ Lens',
'X', 'M+ Others', 'M+'),
'B', DECODE(b.segment2, 'F', 'Babyphat Frames',
'C', 'Babyphat Clips',
'S', 'Babyphat Sun', 'Babyphat'),
'H', DECODE(b.segment2, 'F', 'Phatfarm Frames',
'C', 'Phatfarm Clips',
'S', 'Phatfarm Sun', 'Modo'),
'D', DECODE(b.segment2, 'F', 'Dvf Frames',
'C', 'Dvf Clips',
'S', 'Dvf Sun', 'Dvf'),
'U', DECODE(b.segment2, 'F', 'Puma Frames',
'C', 'Puma Clips',
'S', 'Puma Sun', 'Puma'),
'TE', DECODE(b.segment2, 'F', 'Teko Frames',
'C', 'Teko Clips',
'S', 'Teko Sun', 'Teko'),
'C', DECODE(b.segment2, 'F', 'Kirkland Frames',
'S', 'Kirkland Sun', 'Kirkland'),
'E', DECODE(b.segment2, 'F', 'Etro Frames',
'S', 'Etro Sun', 'Etro'),
'F', DECODE(b.segment2, 'F', 'Fendi Frames',
'S', 'Fendi Sun', 'Fendi'),
'PL', DECODE(b.segment2, 'F', 'PHILLIP LIM FRAMES',
'S', 'PHILLIP LIM SUN', 'PHILLIP LIM'),
'ID', DECODE(b.segment2, 'F', 'ID Frames',
'S', 'ID Sun', 'ID'),
'SP', 'SPARES', 'Others') description
FROM mtl_item_categories a,
mtl_system_items_b msi,
mtl_categories b,
ra_customer_trx c,
ra_customers ar,
ra_customer_trx_lines d,
ra_cust_trx_types_all e,
ra_salesreps f
WHERE d.inventory_item_id = a.inventory_item_id(+)
AND msi.inventory_item_id = a.inventory_item_id
AND msi.organization_id = a.organization_id
AND a.category_id = b.category_id(+)
AND ar.customer_id = c.bill_to_customer_id
and c.customer_trx_id = d.customer_trx_id
AND msi.inventory_item_id = d.inventory_item_id
AND c.cust_trx_type_id = e.cust_trx_type_id
AND f.salesrep_id(+) = c.primary_salesrep_id
AND ar.primary_salesrep_id = c.primary_salesrep_id
AND d.line_type = 'LINE'
AND b.structure_id = 101
AND TRUNC(c.trx_date) >= ?
AND TRUNC(c.trx_date) <= ?
AND a.organization_id = ?
GROUP BY b.category_id, b.segment1, b.segment2
ORDER BY b.segment1
and i have written script on DataSet at beforeOpen
if (params["P_CUSTOMER_NUMBER"].value != null && params["P_CUSTOMER_NUMBER"].value != "")
{ this.queryText = this.queryText + " AND ar.customer_number = '" + params["P_CUSTOMER_NUMBER"].value + "'";}
It is failed...
Hence i have changed the script as
queryText = " SELECT DISTINCT b.segment1 Brand,b.segment2 Type,";
queryText = queryText + " ( SUM (NVL(msi.list_price_per_unit,0))) cog,";
queryText = queryText + " ( SUM (d.quantity_invoiced)) net_units,";
queryText = queryText + " ( SUM (NVL(d.unit_selling_price,0))) tot_unit_price,";
queryText = queryText + " ( SUM (NVL(c.exchange_rate,1))) totExc,";
queryText = queryText + " ( SUM (d.quantity_invoiced)) tot_sold_qty,";
queryText = queryText + " ( sum (nvl(d.extended_amount,0) * nvl(c.exchange_rate,1))) NetSales,";
queryText = queryText + " ( SUM (DECODE(e.TYPE, 'CM', (nvl(d.extended_amount,0) * nvl(c.exchange_rate,1)), 0))) Returns,";
queryText = queryText + " ( SUM (DECODE(e.TYPE, 'INV', (nvl(d.extended_amount,0) * nvl(c.exchange_rate,1)), 'DM', (nvl(d.extended_amount,0) * nvl(c.exchange_rate,1)),0))) gross_amount,";
queryText = queryText + " DECODE(b.segment1,";
queryText = queryText + " 'M', DECODE(b.segment2, 'F', 'Modo Frames',";
queryText = queryText + " 'C', 'Modo Clips',";
queryText = queryText + " 'S', 'Modo Sun',";
queryText = queryText + " 'H', 'Modo Cases',";
queryText = queryText + " 'X', 'Modo Spareparts', 'Modo'),";
queryText = queryText + " 'S', DECODE(b.segment2, 'F', 'Saki Frames',";
queryText = queryText + " 'C', 'Saki Clips',";
queryText = queryText + " 'S', 'Saki Sun',";
queryText = queryText + " 'H', 'Saki Lens',";
queryText = queryText + " 'X', 'Saki Others', 'Saki'),";
queryText = queryText + " 'K', DECODE(b.segment2, 'F', 'Keo Frames',";
queryText = queryText + " 'C', 'Keo Clips',";
queryText = queryText + " 'S', 'Keo Sun',";
queryText = queryText + " 'H', 'Keo Lens',";
queryText = queryText + " 'X', 'Keo Others', 'Keo'),";
queryText = queryText + " 'P', DECODE(b.segment2, 'F', 'M+ Frames',";
queryText = queryText + " 'C', 'M+ Clips',";
queryText = queryText + " 'K', 'M+ Frame & Clip',";
queryText = queryText + " 'S', 'M+ Sun',";
queryText = queryText + " 'H', 'M+ Lens',";
queryText = queryText + " 'X', 'M+ Others', 'M+'),";
queryText = queryText + " 'B', DECODE(b.segment2, 'F', 'Babyphat Frames',";
queryText = queryText + " 'C', 'Babyphat Clips',";
queryText = queryText + " 'S', 'Babyphat Sun', 'Babyphat'),";
queryText = queryText + " 'H', DECODE(b.segment2, 'F', 'Phatfarm Frames',";
queryText = queryText + " 'C', 'Phatfarm Clips',";
queryText = queryText + " 'S', 'Phatfarm Sun', 'Modo'),";
queryText = queryText + " 'D', DECODE(b.segment2, 'F', 'Dvf Frames',";
queryText = queryText + " 'C', 'Dvf Clips',";
queryText = queryText + " 'S', 'Dvf Sun', 'Dvf'),";
queryText = queryText + " 'U', DECODE(b.segment2, 'F', 'Puma Frames',";
queryText = queryText + " 'C', 'Puma Clips',";
queryText = queryText + " 'S', 'Puma Sun', 'Puma'),";
queryText = queryText + " 'TE', DECODE(b.segment2, 'F', 'Teko Frames',";
queryText = queryText + " 'C', 'Teko Clips',";
queryText = queryText + " 'S', 'Teko Sun', 'Teko'),";
queryText = queryText + " 'C', DECODE(b.segment2, 'F', 'Kirkland Frames',";
queryText = queryText + " 'S', 'Kirkland Sun', 'Kirkland'),";
queryText = queryText + " 'E', DECODE(b.segment2, 'F', 'Etro Frames',";
queryText = queryText + " 'S', 'Etro Sun', 'Etro'),";
queryText = queryText + " 'F', DECODE(b.segment2, 'F', 'Fendi Frames',";
queryText = queryText + " 'S', 'Fendi Sun', 'Fendi'),";
queryText = queryText + " 'PL', DECODE(b.segment2, 'F', 'PHILLIP LIM FRAMES',";
queryText = queryText + " 'S', 'PHILLIP LIM SUN', 'PHILLIP LIM'),";
queryText = queryText + " 'ID', DECODE(b.segment2, 'F', 'ID Frames',";
queryText = queryText + " 'S', 'ID Sun', 'ID'),";
queryText = queryText + " 'SP', 'SPARES', 'Others') description ";
queryText = queryText + " FROM mtl_item_categories a,";
queryText = queryText + " mtl_system_items_b msi,";
queryText = queryText + " mtl_categories b,";
queryText = queryText + " ra_customer_trx c,";
queryText = queryText + " ra_customers ar,";
queryText = queryText + " ra_customer_trx_lines d,";
queryText = queryText + " ra_cust_trx_types_all e,";
queryText = queryText + " ra_salesreps f";
queryText = queryText + " WHERE d.inventory_item_id = a.inventory_item_id(+)";
queryText = queryText + " AND msi.inventory_item_id = a.inventory_item_id";
queryText = queryText + " AND msi.organization_id = a.organization_id";
queryText = queryText + " AND a.category_id = b.category_id(+)";
queryText = queryText + " AND ar.customer_id = c.bill_to_customer_id";
queryText = queryText + " and c.customer_trx_id = d.customer_trx_id";
queryText = queryText + " AND msi.inventory_item_id = d.inventory_item_id";
queryText = queryText + " AND c.cust_trx_type_id = e.cust_trx_type_id";
queryText = queryText + " AND f.salesrep_id(+) = c.primary_salesrep_id";
queryText = queryText + " AND ar.primary_salesrep_id = c.primary_salesrep_id";
queryText = queryText + " AND d.line_type = 'LINE'";
queryText = queryText + " AND b.structure_id = 101";
queryText = queryText + " AND TRUNC(c.trx_date) >= ?";
queryText = queryText + " AND TRUNC(c.trx_date) <= ?";
queryText = queryText + " AND a.organization_id = ?";
if (params["P_CUSTOMER_NUMBER"].value != null && params["P_CUSTOMER_NUMBER"].value != "")
{ this.queryText = this.queryText + " AND ar.customer_number = '" + params["P_CUSTOMER_NUMBER"].value + "'";}
queryText = queryText + "GROUP BY b.category_id, b.segment1, b.segment2";
queryText = queryText + "ORDER BY b.segment1";
java.lang.System.out.println(queryText);
It is also failing and getting the message as
Table (id = 12):
- Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00933: SQL command not properly ended
odaconsumer.CannotGetResultSetMetaData ( 1 time(s) )
detail : org.eclipse.birt.data.engine.core.DataException: Cannot get the result set metadata.SQL statement does not return a ResultSet object.SQL error #1: ORA-00933: SQL command not properly ended at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:379) at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:339) at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.declareCustomColumn(PreparedStatement.java:1079) at org.eclipse.birt.data.engine.executor.DataSourceQuery.addCustomFields(DataSourceQuery.java:388)
|
|
| | | | | | |
Goto Forum:
Current Time: Mon Mar 17 21:58:26 EDT 2025
Powered by FUDForum. Page generated in 0.03424 seconds |
Home