on 06-09-2016 10:11 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.