Due to the fact that a SQL query performs differently on DB2/400 compared with Progress (or Oracle) RDBMS I am looking for the SQL-92 guidelines on the web. Can anyone please help out?
Q: Why doesn't the LIKE clause used in 'select tabschema from syscat.columns where tabschema like 'DEV32'' work if it doesn't have a '%'?
A: The tabschema is a fixed length string (CHAR) column and therefore is stored in the catalog as DEV32 followed by 3 blank characters. DB2's implementation is consistent with SQL92 subclause 8.5 <like predicate>, general rule 5. Without the '%' character included or 3 blanks, the result of the predicate is false. In addition, if the '=' is used instead of 'like', the result of the predicate will be true also. The rules for the LIKE predicate are different than the equal predicate. SQL92 subclause 8.2 <comparison predicate>, general rule 3, states that if two character strings have different lengths, the shorter string is effectively replaced by a string of the same length by concatentating pad characters (blanks usually). Hence "DEV32 '=' DEV32" is true.
So on DB2/400 the query 'select acc_nr, descr from ledger where ledger.adm_nr = 621 and ledger.acc_nr like '2000' ' returns no result where at Progress it does.
Q: Why doesn't the LIKE clause used in 'select tabschema from syscat.columns where tabschema like 'DEV32'' work if it doesn't have a '%'?
A: The tabschema is a fixed length string (CHAR) column and therefore is stored in the catalog as DEV32 followed by 3 blank characters. DB2's implementation is consistent with SQL92 subclause 8.5 <like predicate>, general rule 5. Without the '%' character included or 3 blanks, the result of the predicate is false. In addition, if the '=' is used instead of 'like', the result of the predicate will be true also. The rules for the LIKE predicate are different than the equal predicate. SQL92 subclause 8.2 <comparison predicate>, general rule 3, states that if two character strings have different lengths, the shorter string is effectively replaced by a string of the same length by concatentating pad characters (blanks usually). Hence "DEV32 '=' DEV32" is true.
So on DB2/400 the query 'select acc_nr, descr from ledger where ledger.adm_nr = 621 and ledger.acc_nr like '2000' ' returns no result where at Progress it does.