error while calling pl/sql function from query

[chaktifarid]

Hello, I'm new to Jasper.

I am trying to call a pl/sql function from the query but i am getting the following error: missing 'FROM' at '(' .

My query is something like:

select a, my_function(p1,p2), b , DECODE(C, '1', 'M.', '2', 'Mme') from tab1,tab2 where tab1.id = tab2.id 

Any idea?

[Tom C]

This is a SQL query issue that has nothing to do with our product. You need to use joins to lift data from multiple tables, something like

select t1.a, my_function(t1.p1,t2.p2), t2.b, DECODE(t1.C, '1', 'M.', '2', 'Mme')from tab1 t1inner join tab2 t2 on t1.id = t2.id[/code]

Please take some SQL training classes (and Java classes) to get yourself familiar with the technology to use our product effectively. You may find this YouTube video useful and helpful:

https://www.youtube.com/watch?v=rocjHUQf5n0

Good luck and happy coding. :)

 

[jgust]

I suspect your issue is that p1 and p2 are parameters and do not come from tab1 or tab2. 

If that is the case then you just need to correct the SQL so that Jasper understands. 
Updated SQL below makes the assumption of where the data is coming from.  You should always use aliases for better readability.

select t1.a                                as Awesome     , my_function($P{p1},$P{p2})          as Super_Function     , t2.b                                as Bodacious     , DECODE(t1.C, '1', 'M.', '2', 'Mme') as Case_is_BetterFROM tab1 t1INNER JOIN tab2 t2 ON t1.id = t2.id[/code]

[chaktifarid]

Thank you for your answer. Basically i am new to jasper not to coding :) 

The query is working normally on Oracle reports.

what i am doing here is trying to create the report by using jasper.

[chaktifarid]

Thank you jgust, no it is not the case, p1 and p2 are coming from my query. 

 

[jgust]

Thank you for the clarification.

I was able to recreate the issue by setting the language to SQL and using a custom function in the query.

 

The solution was to change the language to PLSQL.  Once I did that I was able to read the fields.

 

[chaktifarid]

It's working.

Thank you so much for your time,your help and your kindness.

[MariaF]

@tchen @jgust Hi, I am trying to do something similar as above, and I have been reading all the posts related to this topic. I am trying to call a plsql with multiple parameters from Jasper. When I use hard coded parameters, the dummy select with the function works fine. But as soon as I try to change the hard coded strings for parameters values, I get sql error of column or field not found. The parameters have been defined in the Parameters section as Strings.

Code not working from Jasper with plsql setting:

select mysch.CF_CostPerTreatmentFormula ( $P{p_asset_id}, 'CORAL ARDISIA' , 'FOLIAR HAND GUN' ,'GUN', '01-jan-2023', '31-jan-2023') from dual

select mysch.CF_CostPerTreatmentFormula ( $P!{p_asset_id}, 'CORAL ARDISIA' , 'FOLIAR HAND GUN' ,'GUN', '01-jan-2023', '31-jan-2023') from dual

This query works fine from Jasper with Hardcoded values from Jasper and plsql settting:

select mysch.CF_CostPerTreatmentFormula ( 'CC-100', 'CORAL ARDISIA' , 'FOLIAR HAND GUN' ,'GUN', '01-jan-2023', '31-jan-2023') from dual

I need to pass the six values as parameters. Any help is greatly appreciated!

[shreekrishna.shankhwar]

@MariaF Can you please share your jrxml for to understand or screenshot of the error when you try to read the field.

[MariaF]

@shreekrishna.shankhwar  Hi, I have tried passing the parameter as $P{p_asset_id} and $P!{p_asset_id}

[MariaF]

When I try to drag a paramater from the list below into the query, it doesn't let me. Also, when I type $P{p_asset_id} it turns red in the query. But when I type it with the "!" it stays blue. The Read Fields button does not report an error either way. But when I compile the report I get the error above. Disclaimer: This is for a pesticide treatment application 🙂 

[MariaF]

Solution:

1) Each time we change the parameters inside the query, we have to refresh manually the Return $F value of the Expression that calls the plsql function in the property panel.

2) Make sure the parameters are in the same order than in the calling plsql function. 

3) Here is the query syntax that returned the correct value:

select mysch.CF_CostPerTreatmentFormula (

$P{p_asset_id},

$P{p_plant_treated},

$P{p_treatment},

$P{p_treatment_method_abrev},

$P{p_date_from}, $P{p_date_to}

)

from dual