cancel
Showing results for 
Search instead for 
Did you mean: 

DB2 SQL - not like and <> get different result?

martin_mikala
Participant
0 Kudos

Hi,

Do you know why DB2 10.5.7 (I didn't test other) get different result for selects with "NOT LIKE" and "<>", when I set exact string for both? Seems ,that this difference is not in everytime. Me do it for OWNER column in syscat.tables. Bellow is example.

> db2 "select SUBSTR(TABSCHEMA,1,10) AS TABSCHEMA,SUBSTR(TABNAME,1,20) AS TABNAME,SUBSTR(OWNER,1,10) AS OWNER, TYPE, STATUS, CREATE_TIME, ALTER_TIME from syscat.tables where TABNAME = 'Z70_RN_VEN_TABL' AND OWNER not like 'SAPEST'"

TABSCHEMA  TABNAME              OWNER      TYPE STATUS CREATE_TIME                ALTER_TIME

---------- -------------------- ---------- ---- ------ -------------------------- --------------------------

SAPSR3     Z70_RN_VEN_TABL      SAPEST     T    N      2013-05-11-15.17.26.378695 2013-05-11-15.17.26.658445

  1 record(s) selected.

> db2 "select SUBSTR(TABSCHEMA,1,10) AS TABSCHEMA,SUBSTR(TABNAME,1,20) AS TABNAME,SUBSTR(OWNER,1,10) AS OWNER, TYPE, STATUS, CREATE_TIME, ALTER_TIME from syscat.tables where TABNAME like 'Z70_RN_VEN_TABL' AND OWNER <> 'SAPEST'"

TABSCHEMA  TABNAME              OWNER      TYPE STATUS CREATE_TIME                ALTER_TIME

---------- -------------------- ---------- ---- ------ -------------------------- --------------------------

  0 record(s) selected.

B.R.

Martin

Accepted Solutions (1)

Accepted Solutions (1)

Frank-Martin
Advisor
Advisor
0 Kudos

Hi Martin,

interesting question. Looks like DB2 adds some trailing blanks to column OWNER in sysact.tables. I do not know why this is the case.

select length(OWNER), hex(owner)  from syscat.tables where owner = 'SAPN75' and tabname = 'SVERS'     

1               2

---------------- -------------------------------------                                                                     

8               5341504E37352020                                                                                                                                                                                                                                                 

1 record(s) selected.

Operators <> and NOT LIKE behave differently regarding trailing blanks. Trailing blanks in VARCHAR columns are significant for LIKE operators but not for = or <> comparisons.


select tabname , tabschema, owner from syscat.tables where tabname = 'SVERS' and owner <> 'SAPN75'


0 record(s) selected.


select tabname , tabschema, owner from syscat.tables where tabname = 'SVERS' and owner NOT LIKE 'SAPN75'


1 record(s) selected.


To circumvent the difference you may have to use two NOT LIKE operators.


select tabname , tabschema, owner from syscat.tables where tabname = 'SVERS' and owner NOT LIKE 'SAPN75' and owner NOT LIKE 'SAPN75 %'


1 record(s) selected.


For this reason you sometimes find SQL statements with double LIKE generated by the ABAP stack.

Regards

               Frank

Answers (0)