Concat Function in report query...

[Kumar_rahul]

Hi All,

 

I have a report query which contains a column which is obtained by applying CONCAT function of Oracle. The query is given below:

 

select

a11.TBP_ID TBP_ID,

a11.LCBO_LC_NO LCBO_LC_NO,

CONCAT(a11.LC_BOL_NAME, a11.LC_AWB_NAME) CustCol_10

from

REP_LCPRO_BO a11

 

I need to put a condition on CustCol_10 depending on a multi-select input control.

 

I have been trying to use the IN clause function ($X{ IN, COLUMN, PARAMETER NAME}) to implement the above scenario.

 

However it does not accept:

a) CustCol_10 as the Column Name

i.e. $X{ IN, a11. CustCol_10, PARAMETER_NAME}

b) neither does it accept CONCAT(a11.LC_BOL_NAME, a11.LC_AWB_NAME)

i.e. $X{ IN, CONCAT(a11.LC_BOL_NAME, a11.LC_AWB_NAME), PARAMETER_NAME}

 

How can I implement the above scenario?

 

Any help/ suggestion is welcome.

 

Regards,

Rahul.

[lucianc]

Using $X{IN} with column expressions that contain commas is not currently support by JasperReports. Post a bug for this issue here and we'll investigate what solution could be provided for it.

 

In the meantime, you could use one of the following workaround:

• Use a subquery to allow the alias to be used in WHERE expressions:

  Code:

  select * from (select
  a11.TBP_ID TBP_ID,
  a11.LCBO_LC_NO LCBO_LC_NO,
  CONCAT(a11.LC_BOL_NAME, a11.LC_AWB_NAME) CustCol_10
  from
  REP_LCPRO_BO a11)
  where $X{IN, CustCol_10, PARAMETER_NAME}

• Do not use $X{..}, but the (now deprecated) "where column in ($P!{..}}" solution. You can find some pointers on this solution on [url=http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=&func=view&catid=8&id=23204#23204]this thread.[/ul]

 

Regards,

Lucian

[Kumar_rahul]

Thanks Lucian for your prompt response.

 

I have implemented the scenario using the first workaround, you suggested i.e. Use a subquery to allow the alias to be used in WHERE expressions.

 

It works fine for me in most of the cases. However when we select a string in the Prompt which begins with " or ' the report crashes and we get an error stating "Multi Parameters cannot contain null values".

 

This, I guess, has nothing to do with the work-around. It should be a general issue where in we select a string to be passed as parameter from a multi-select prompt which begins with " or '.

 

Regards,

Rahul.

 

Post edited by: Kumar_rahul, at: 2008/01/17 12:04

Post edited by: Kumar_rahul, at: 2008/01/17 12:06

[lucianc]

Kumar_rahul wrote:

It works fine for me in most of the cases. However when we select a string in the Prompt which begins with " or ' the report crashes and we get an error stating "Multi Parameters cannot contain null values".

 

That's yet another bug. Please log it here.

 

Regards,

Lucian