Re: ALLTRIM( ) in SQL statement

On Mar 11, 11:46 pm, Gene Wirchenko <g...@xxxxxxxx> wrote:
"David" <David.A...@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.- Hide quoted text -

- Show quoted text -

Hi, Gene, thanks for your reply...

Here is what I don't think I'm understanding. With SET ANSI OFF, I
get the expected response of 1 value of "10" (i.e., one row in the
query result) when I run this SQL:

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

But (with SET ANSI OFF, still), when I simply add an ALLTRIM( )
function,
I get three row in the results consisting of:

1 instance of (blank)
1 instance of "1"
1 instance of "10"

Here's the SQL:

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

I'm a little confused, but I guess my question is, why does ALLTRIM( )
have this effect?

Thanks,
David

.

Relevant Pages

  • Re: ALLTRIM( ) in SQL statement
    ... Alltrim " " equals the empty string which in the logic of SQL is equal to ... I think you need to look at set ansi. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Exact match using INLIST()
    ... In addition to Rush's advice re Set Ansi, ... concerning string comparisons. ... To avoid this confusion (and to make your SQL ... For example, I asked for 'African American', but I received 'African ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Remove duplicate records
    ... Emails UNIQUE ... I agree with you on the problem with the ALLTRIM() when it comes to the SQL ... The above index expression does not exeed the 240 characaters limit. ...
    (microsoft.public.fox.programmer.exchange)
  • SET ANSI ON causes Rushmore to fail
    ... SET ANSI ON causes an SQL Select to run very very slowly. ... Rushmore obviously fails and downloads the whole table. ... will be running via ADO where the SET ANSI is ON by default. ...
    (microsoft.public.fox.programmer.exchange)
Loading