Re: ALLTRIM( ) in SQL statement

Alltrim " " equals the empty string which in the logic of SQL is equal to
any value.
-Anders

"David" <David.Aman@xxxxxxxxx> wrote in message
news:1173708844.491299.316220@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
    ... I think you need to look at set ansi. ... query result) when I run this SQL: ... SELECT subject, unit, COUNTas unitcount; ... But, when I simply add an ALLTRIM() ...
    (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)
  • Re: Linq question!
    ... IEnumerable<Stratas> str = from s in ic.Stratums ... join a in ic.AgeOrLengths on s.StratumId equals a.StratumId ... will normally prevent LINQ to SQL from ... the query cannot be converted to an SQL query. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Hide junction table fields in subform
    ... I don't know SQL but from the order of items around the equals sign it would seem I'm doing the data entry backwards. ... I should be keying in entries to the junction table fields, ...
    (microsoft.public.access.forms)
  • Re: Creating a query with Multi select list boxes
    ... Perhaps it is the way I am writing the "IN" part of the SQL ... The problem here is the equals sign. ... I've removed the parantheses that the query designer adds. ...
    (microsoft.public.access.modulesdaovba)