cancel
Showing results for 
Search instead for 
Did you mean: 

Error code while trying to insert subreport via command

Former Member
0 Kudos

Hi everybody,

if I try to apply the following sql-statement in a subreport out of jasper (ireport):

SELECT

  jptsys_web_cust_prop.jptsys_web_cust_prop_id field_id,

  jptsys_web_cust_prop.jptsys_web_cust_prop_n field_n,

  jptsys_web_cust_prop.jptsys_web_cust_prop_sc field_sc,

  jptsys_web_cust_prop.property_type field_type,

  jptsys_web_cust_prop_cont_id cont_id,

COALESCE(

      TO_NCLOB(jptsys_web_cust_prop_cont.string_val),

      TO_NCLOB(jptsys_web_cust_prop_cont.string_value),

      TO_NCLOB(jptsys_web_cust_prop_cont.integer_val),

      TO_NCLOB(jptsys_web_cust_prop_cont.decimal_val),

      TO_NCLOB(TO_NCHAR(jptsys_web_cust_prop_cont.date_val, 'DD.MM.YYYY')),

      (SELECT

          TO_NCLOB(MAX(jptsys_web_lkup_data.jptsys_web_lkup_data_n))

      FROM

          jptsys_web_lkup_data

      WHERE

          jptsys_web_lkup_data.jptsys_web_lkup_data_id = jptsys_web_cust_prop_cont.single_sel_val_id

      )

  ) field_value,

jptsys_web_cust_prop_cont.descrip_grp_id field_descrip_id

FROM

  jptsys_web_cust_prop,

  jptsys_web_cust_prop_cont

WHERE

  jptsys_web_cust_prop_cont.jptsys_web_cust_prop_id = jptsys_web_cust_prop.jptsys_web_cust_prop_id AND

  jptsys_web_cust_prop_cont.entity_id = $P{EVENT_ID} AND

  jptsys_web_cust_prop.jptsys_web_cust_prop_id = '2316'

ORDER BY jptsys_web_cust_prop_cont.jptsys_web_cust_prop_cont_id

I get this error message:

Anybody who can help?

Rgds

Eberhard

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

I suspect that the issue is on this line:

jptsys_web_cust_prop_cont.entity_id = $P{EVENT_ID} AND


Are you trying to use EVENT_ID as a parameter to the query?  If so, you can't use the regular parameter syntax for the database.  Instead, you need to create a parameter in the Command Editor and Crystal will add the parameter value to the query.  So, it might look like this:


jptsys_web_cust_prop_cont.entity_id = {?EVENT_ID} AND


See my blog post for more information about working with parameters in queries: 


-Dell

Former Member
0 Kudos

Hi Dell,

if I change it the way you proposed

jptsys_web_cust_prop_cont.entity_id = {?EVENT_ID} AND


I get another error message:



Any other proposal?


Tks in advance.


Regards


Eberhard

abhilash_kumar
Active Contributor
0 Kudos

Hi Eberhard,

Make sure that the datatypes of the Event_ID parameter and the Entity_ID field match.

If it's a string, try surrounding the parameter within single quotes:

jptsys_web_cust_prop_cont.entity_id = '{?EVENT_ID}' AND


-Abhilash

Former Member
0 Kudos

I am one step further, because I created a parameter (EVENT_ID),

but

a) I am asked to insert a number (datatyp = NUM)

b) as far as I know the data for the EVENT_ID parameter are coming from the INCIDENT_ID of my main report and it´s a whole range then, not only one.

c) how do I connect main and sub report then, because as far as I see they do not have common table fields for a connection.

main report sql is:

SELECT

  incident.incident_id,

  incident.incident_ref,

  incident.date_logged,

  incident.short_desc,

  inc_data.event_type,

  inc_data.sub_event_type,

  inc_cat.inc_cat_sc,

  item.item_sc,

  usr.usr_sc,

  usr.usr_n,

  sectn_dept.sectn_dept_sc,

  division.division_sc,

  branch.branch_sc,

  sectn.sectn_sc,

  rproc_hdr.rproc_hdr_sc,

  jptsys_cust_ent.jptsys_cust_ent_id

from

  incident

JOIN inc_data ON incident.incident_id = inc_data.incident_id

JOIN item ON incident.item_id = item.item_id

JOIN inc_cat ON incident.inc_cat_id = inc_cat.inc_cat_id

join serv_dept ass_svd ON incident.ass_svd_id = ass_svd.serv_dept_id

JOIN usr ON incident.aff_usr_id = usr.usr_id

JOIN sectn_dept ON usr.sectn_dept_id = sectn_dept.sectn_dept_id

JOIN sectn ON sectn_dept.sectn_id = sectn.sectn_id

JOIN branch ON sectn.branch_id = branch.branch_id

JOIN division ON branch.division_id = division.division_id

JOIN rproc_hdr ON inc_data.u_num1 = rproc_hdr.rproc_hdr_id

INNER JOIN jptsys_cust_ent ON inc_data.ent_id = jptsys_cust_ent.jptsys_cust_ent_id

WHERE

  (incident.csg_id = '1' or incident.csg_id = '7') AND

  ass_svd.serv_dept_id <> '99' AND

  item.item_sc = 'S-BESCHAFFUNG' AND

  inc_cat.inc_cat_sc IN ('SR WINDOWS')

Eberhard

former_member205840
Active Contributor
0 Kudos

Hi Eberhard,

Where you are trying to place your sub report ? that matters.  If you place your sub report in detail section, then even your main reports INCIDENT_ID is a range parameter no problem because in detail section each record will get processed (i.e. each time Incident ID will pass one ID to EVEN ID in sub report and gets the data.

If you place your sub report on report header it will pass only first INCIDENT ID, in report footer last INCIDENT ID.

Thanks,

Sastry

Former Member
0 Kudos

only the last two columns are 2 sub-reports.

So it seems they go to the detail section.

am I right?

former_member205840
Active Contributor
0 Kudos

Hi Eberthard,

Yes, now you can link your sub report parameter with main report INCIDENT_ID field.  This will pass one INCIDENT ID to your sub reports and gets related data in sub report.

Thanks,

Sastry

Former Member
0 Kudos

yes, but I still have this input problem while creating the parameter. How can it be set, so the user will  not be asked for input, but input is coming from incident_id?

abhilash_kumar
Active Contributor
0 Kudos

Right-click the subreport > Change Subreport Links > Move the Incident ID field to the 'Fields to link to' pane.

From the drop-down at the bottom left, you should see this option : 'Subreport parameter field to use'.

Choose the Event ID prompt from the drop-down.

This passes the Incident ID field on every row of the Main Report to the Subreport's SQL's where clause.

-Abhilash

former_member205840
Active Contributor
0 Kudos

Hi Eberthard,

If you link Main report field to sub report field then it will as for user input for both INCIDENT ID , EVENT ID parameters.

Link main report INCIDENT ID to sub report EVENT ID Parameter, this will only ask user to input INCIDENT ID.

Thanks,

Sastry

DellSC
Active Contributor
0 Kudos

This is true unless you're using a Command in the subreport.  In this case you create a parameter in the command and then link from the main report to the parameter in the command and un-check "Select data in subreport based on field:".

If you link directly to the field, Crystal will pull all of the data from the Command into memory and do the filtering there.  If, instead, you add a parameter to the Command and link to the parameter, the filter is passed to the database where it can be more efficiently processed.

See here for more info on using commands:  .

-Dell

Former Member
0 Kudos

Hi everybody,

I finally got it, but I still have two issues on this report.

1) A layout-problem: the supposed to be is like this:

Mine looks like this:

How do I get rid of the box around my subreport? For the line pitch I only have to suppress all the sections apart from details, I guess

2) A syntax-issue: the field in the subreport contents a condition:

( $F{FIELD_VALUE} == null ? "nicht angegeben" : $F{FIELD_VALUE} )

I created a formula:

if {sub_win_anz.FIELD_TYPE} = NULL then "nicht angegeben"

else if {sub_win_anz.FIELD_VALUE}

but something is wrong with the NULL

regards

Eberhard

abhilash_kumar
Active Contributor
0 Kudos

Hi Eberhard,

Issue 1:

Right click the subreport > Format > border tab > turn off the right, left, top and bottom borders

Also make sure every section inside the subreport, except the details section, is suppressed.

Issue 2:

The syntax should be:

(If isNull({database field}) or {database field} = '') then

'XXX'

Else

'YYY'

-Abhilash

Former Member
0 Kudos

Hi Abhilash.

(If isNull({sub_win_anz.FIELD_VALUE) or {sub_win_anz.FIELD_VALUE} = '') then

'nicht angegeben'

Else

{sub_win_anz.FIELD_VALUE}

Means: if there is no value in the field, take the string 'nicht angegeben', if there is, take the value of the field

but I get an error: unknown fieldname

Rgds

Eberhard

abhilash_kumar
Active Contributor
0 Kudos

You're missing the curly brace in the isnull. Try:

(If isNull({sub_win_anz.FIELD_VALUE}) or {sub_win_anz.FIELD_VALUE} = '') then

'nicht angegeben'

Else

{sub_win_anz.FIELD_VALUE}

-Abhilash

Former Member
0 Kudos

Sorry, I just copied and pasted, but now I get the error:

keyword then is missing

Rgds

Eberhard

abhilash_kumar
Active Contributor
0 Kudos

This should work:

If (isNull({sub_win_anz.FIELD_VALUE}) or {sub_win_anz.FIELD_VALUE} = '') then

'nicht angegeben'

Else

{sub_win_anz.FIELD_VALUE}

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

the formula is without an error now, thanks.

But I don´t get the string in the preview!

abhilash_kumar
Active Contributor
0 Kudos

Open the formula and set the drop-down on the top to 'Default Values for Nulls'.

I hope this works,

-Abhilash

Former Member
0 Kudos

stupid me, I did not apply the formula into the details. with myself.

tks so much. I hope you are not upset.

Brgds

Eberhard

Answers (0)