Text Search in Oracle CLOB

[johnfrani]

I have a successful CR 8.5 report based on this query...

******************
SELECT
CSCODE."NATURECODE", CSCODE."DESCRIPTION",
CSMAIN."ACTDATE"
FROM
csmain, cscode
WHERE
CSCODE."NATURECODE" = CSMAIN."NATURECODE"
AND CSMAIN."ACTDATE" >= {ts '2007-02-22 00:00:00.00'} AND CSMAIN."ACTDATE" < {ts '2007-02-24 00:00:00.00'} and dbms_lob.instr(csmain.actnote, 'Field Event') > 0 and csmain.officerid > '0'
ORDER BY
CSCODE."NATURECODE" ASC
********************************
The WHERE clause:
Links the two tables
Allows the user to enter start and end dates (CR parameter felds)
Only selects records where the officerid value is greater than 0
Searches the csmain.actnote (CLOB field) for the text, "Field Event" and if this text appears in the actnote field, the record joins the returned record set

When I open this same report in CR11, the text search of the actnote field is not included in the query (as shown in Databse / Show SQL Query) resulting in an incorrect return.

How can I keep the same functionality (searching for text strings in an Oracle CLOB field) that I had in CR8.5 in the new CR11?

Thanks
John

 

[synapsevampire]

I'd guess that someone manually edited the SQL in the CR8.5 version.

To do the same in CR 9 and above, you use a Command Object, listed as Add Command under the data source and paste in the entire SQL as the data source.

Note that you also create parameters in that screen.

-k