Netezza concat function in calculated field

Issue:

[toc on_off::hide=1]

I have a question regarding configuring JasperReports Server so we can create some Calculated Fields within a Domain.

How does the "applicationContext-semanticLayer.xml" file know which Database server it is based on? Where is the setting that we can switch to modify what kind of Server it thinks we're using? I have a suspicion that it's defaulting to the "default" server setting, which is incompatable with the syntax for our Netezza DB Server. For example, I'm trying to use the CONCAT function in the calculated field like:

CONCAT([field1]," ",[field2])

But it is returning an error when trying to query this field. And, unfortunately, when I try to input the actual SQL to perform the concatination:

[field1] || " " || [field2]

the field turns red and it wont let me save my field.

I need to know how to check/configure our JasperReports Server to verify what kind of DB server it THINKS it's using. If there is no Netezza-specific configuration setting within JasperReports Server

I'm told Netezza follows most of the PostgreSQL syntax.

Resolution:

Netezza uses "||" to concatenate, so the following lines needs to be added to applicationContext-symantecLayer.xml:

These lines need to be added inside the property name="serverTypes":

<bean class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfig">
    <property name="productName" value="netezza"/>
    <property name="sqlGenerator" ref="netezzaSQLGenerator"/>
    ...
</bean>

Then an SQL generator for netezza is added, with a concat function defined to join the arguments with "||"

<bean id="netezzaSQLGenerator" parent="defaultSQLGenerator" scope="prototype">
    <property name="functionTemplates">
        <map merge="true">
            <entry key="concat">
                <value>
                    sqlArgs.join(" || ")
                </value>
            </entry>
        </map>
    </property>
</bean>

Ref. Case #00025889 -- 21:48, 14 May 2012 (UTC)