cancel
Showing results for 
Search instead for 
Did you mean: 

SQLJ upper

Former Member
0 Kudos

Hi

I need to have a select statement like:

<b><i>SELECT C1,C2 from T1 where upper(C1) like upper(:val_c1)</i></b>

But SQLJ translator complains about upper.

Any suggestion?

TIA

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

SQLJ works on the Open SQL level, which means that basically entry level SQL-92 is supported (and few intermediate SQL features as well, such as joins), while support for functions such as LOWER and UPPER is part of the full SQL-92. For more information about the Open SQL grammar, please have a look at the documentation at http://help.sap.com/saphelp_nw04/helpdata/en/9b/f46cabaa874bc9a82234e8cf1d0696/frameset.htm.

Zornitsa

Former Member
0 Kudos

Thanks Zornitsa

So, in this case, I would need to mix jdbc with SQLJ. Is that correct?

Regards.

Former Member
0 Kudos

Yes, I think you should use both SQLJ and JDBC with native SQL access to execute the database-specific statements. You can have a look at the documentation about combining SQLJ and JDBC (http://help.sap.com/saphelp_nw04/helpdata/en/b5/f5bb805b2e9243ac9a1eafc4ecbd5f/frameset.htm) and about native SQL access (http://help.sap.com/saphelp_nw04/helpdata/en/51/ef9f94e4ba894d8e93b2eed7c9f82d/frameset.htm).

Regards,

Zornitsa

Answers (1)

Answers (1)

adrian_goerler
Active Participant
0 Kudos

Hi,

the Open SQL grammar does not provide the upper function because the semantics of UPPER are database dependent. (It works fine for a...Z, of course, but for slightly more exotic characters, the behavior differs from database to database). So please be aware that you might run into porting issues, if you use UPPER on Native SQL.

Also, please note that the database can't use an index, if you the UPPER function.

A more general solution for you issue would be to store your data in a normalized way. E.g. in upper case, trimmed from leading and trailing spaces, normalized according to phonetic similarity, ... whatsoever.

If you keep your data normalized, you can easily retrieve the data again and avoid duplicates.

Adrian

Former Member
0 Kudos

Hi Adrian

Good advice. I will normalize data.

Thanks.