Function Call in SQL-Statement

[Former Member]

Hi everybody,

todays heavy issue:

in my jasper sql, there is a function call about a description (could be a very long text over more than 4 database fields) in the database. The sql is like this:

select

    incident.incident_id,

    incident.incident_ref,

    inc_data.event_type,

    inc_data.sub_event_type,

    incident.date_logged,

    inc_cat.inc_cat_sc,

    incident.inc_resolve_act,

    DESCRIPTION(incident.incident_id) RFC_DESC,

    item.item_sc,

    incident.inc_chk_ref,

    serv_dept.serv_dept_sc,

    sectn_dept.sectn_dept_sc

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.cause_id = inc_cat.inc_cat_id

JOIN assyst_usr ON incident.assyst_usr_id = assyst_usr.assyst_usr_id

JOIN serv_dept ON incident.ass_svd_id = serv_dept.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

where

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

  (inc_data.event_type = 'i' OR inc_data.event_type = 'c' OR inc_data.event_type = 'p') AND

  item.item_sc = 'S-AUSLAENDERWESEN' AND

  incident.inc_status = 'o' AND

  sectn_dept.sectn_dept_sc LIKE 'KVR%'

If I try to apply, I get the following error message:

How can I handle something like this?

Thanks in advance for your support.

Rgds

Eberhard

[abhilash_kumar · Accepted solution]

Hi Eberhard,

Does this SQL run without errors when run in an Oracle Client like Toad/SQL Developer?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

yes, with sql developer it runs.

I just left the DESCRIPTION out now, so CR creats a field (RFC_DESC), but I don´t think this solves my problem already, because how should CR know to look for more fields to combine them, if necessary?

rgds

Eberhard

[abhilash_kumar · Accepted solution]

1) Does the DESCRIPTION() function exist on the same database that you're connected to?

2) Does the user that you're logged in as, have access to the schema this function resides in?

3) Have you tried adding schema name before the function name, like so:

schema_name.Description(Incident.incident_id)

If this doesn't work, you should consider creating a View or even a Stored Proc based on this SQL Query.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

1) yes

2) no, they had to grant acces for me first

3) I used schema name and now I could use the whole statement and create the sql-command in CR

so far so good. thanks. I don´t think this was the end of the issue for this report.

rgds

Eberhard

[abhilash_kumar · Accepted solution]

Glad it works!

-Abhilash

[former_member205840]

Hi Eberhard,

I see that you are using a ORACLE driver, which is unable to translate DESCRIPTION().  Can you remove this function and use field directly ?

Crystal can display/ hold 64kb of text, for each record, this field contains more than that,  then we may need to twerk this using left, right, mid, instr and len functions and display it.

Thanks,

Sastry

[Former Member]

Hi Sastry,

I removed the whole line so far, so I could use the sql and the subreport with parameter again by the way, but how to proceed to apply the function and get it work?

I have no idea.

Sorry for all my questions, but I only started a few weeks ago with CR.

rgds

Eberhard

[former_member205840]

Hi Eberhard,

What is this DESCRIPTION() function does ?  I googled it and could not find any useful information.

Also I see that incident.incident_id is used in Description() function. 

Thanks,

Sastry

[Former Member]

Hi Sastry,

I am clear for the moment, thank you.

rgds

Eberhard