Re: ALLTRIM( ) in SQL statement

"David" <David.Aman@xxxxxxxxx> wrote:

Given the following table:

CREATE TABLE mytable (subject C(10), unit C(10))

INSERT INTO mytable (subject, unit) VALUES ('algebra1',' ')
INSERT INTO mytable (subject, unit) VALUES ('algebra1','1')
INSERT INTO mytable (subject, unit) VALUES ('algebra1','10')

I tried the following SQL statement:

SELECT subject, unit, COUNT(unit) as unitcount ;
FROM mytable ;
WHERE INLIST(unit,'10') ;
GROUP BY subject, unit

The results told me correctly that there was 1 value of '10' in the
table.
===================
Question: Why does the same SQL statement with an added ALLTRIM in
the
WHERE clause result in a response consisting of 3 rows?

SELECT subject, unit, COUNT(unit) as unitcount ;
FROM mytable ;
WHERE INLIST(ALLTRIM(unit),'10') ;
GROUP BY subject, unit

I think you need to look at set ansi.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
.

Loading