inconsistent datatype after sps version 70 upgrade

[Former Member]

We performed the upgrade from spa version 69 to spa version 70 and afterwards we shave some SQL statements failing with the following error;

Could not execute 'Select distinct ITEMNAME, ITEMURLDISPLAY AS ITEMURL, DATE, SIZE, TYPE, FILEID, FOLDERID from ...' in 137 ms 258 µs .

SAP DBTech JDBC: [266] (at 497): inconsistent datatype: the expression has TEXT type which is not allowed: DATA: line 4 col 38 (at pos 497)

Here is a bit of Sql that hasn't changed:

Select distinct ITEMNAME, ITEMURLDISPLAY AS ITEMURL, DATE, SIZE, TYPE, FILEID, FOLDERID from "DB_1". "TBL_1" INNER JOIN "DB_1"."TBL_2" ON "DB_1"."ITBL_1"."SYCID" = "DB_1"."TBL_2"."SYCID" WHERE (ITEMURL LIKE '/sometest/%') AND

( (ITEMNAMELOWER LIKE '%search%' ) OR (DATA LIKE 'search') )

Has anyone else experienced issues with LIKe in SPS version 70? If so (or not so) is there something we missed in the upgrade process that might cause this type of behavior?

Thank you in advance for ANY insight

[Former Member]

Looks like modifying the carom LIKE to  CONTAINS

CONTAINS(DATA,'%search%')

will allow a similar behavior to LIKE

Select distinct ITEMNAME, ITEMURLDISPLAY AS ITEMURL, DATE, SIZE, TYPE, FILEID, FOLDERID from "DB_1"."TBL_1"

INNER JOIN

"DB_!"."TBL_2"

ON

"DB_1"."TBL_1"."SYCID" = "DB_1"."TBL_2"."SYCID"

WHERE

(ITEMURL LIKE '/searchyourcloud/%') AND

(

(ITEMNAMELOWER LIKE '%search%' ) OR ( CONTAINS(DATA,'%search%') )

)

which could be our workaround.

[former_member182302]

Yes simon, i understand that we can use CONTAINS as workaround. And may be use "match" if we are using in the models.

What i was trying to understand here is, is there any change on the LIKE predicate, as mentioned by you it is working on "TEXT" datatypes and now it is not working on them anymore.

That would be a considerable change, with significant effects on the systems that went go-live already.

May be anybody from development team can help to answer your question on the changes done on Version 70.

Regards,

Krishna Tangudu

[Former Member]

There is an issue with that concerning data loss as the DATA is a Blob in essence and we'll need that type in production.

In the spirit of attempts I tried to alter and received a:

feature not supported: ALTER TABLE ALTER not allowed to change datatype or length of TEXT/SHORTTEXT

However, I did create a test table with the DATA defined as a varchar and the SQL will execute returning information. Looks tied to potential changes in the execution of LIKE with regards to large (blob, etc.) data types.

[former_member182302]

Hi,

If the "DATA" type is changed to VARCHAR is it working?

Regards,

Krishna Tangudu