Re: SQL query fails
From: Joe Stanton (joe_at_bravenewsoftware.com)
Date: 08/03/04
- Next message: Michel Walsh: "Re: Finding the end of a recordset"
- Previous message: Lauri: "Re: Date query"
- In reply to: Bob Quintal: "Re: SQL query fails"
- Next in thread: John Spencer (MVP): "Re: SQL query fails"
- Reply: John Spencer (MVP): "Re: SQL query fails"
- Reply: Bob Quintal: "Re: SQL query fails"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 03 Aug 2004 16:44:25 GMT
It may have escaped notice, but the second operator in the string has 3
trailing single quotes, meaning that the data in the second operator is
actually (put on one line below for clarity):
A'
and so the properly constructed literal string in SQL is
'A'''
This SQL is constructed in this way due to the need to create a statement
that is acceptable (without tweaking) to Oracle, SQL Server, and Access. So
this requires using the single quote as the literal string delimiter as well
as not relying on LIKE (which has different wildcard characters for Access
and Oracle/SQL) or perhaps Instr, etc.
The user inputs the data A&. The program determines the next ASCII
character in sequence after the last character in the input (the &), and in
thise case determines it to be the single quote. And so the purpose is to
find all strings that start with A&.
"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message
news:Xns953A6EA825617BQuintal@66.150.105.49...
> "Joe Stanton" <joe@bravenewsoftware.com> wrote in
> news:ZSuPc.102929$QI2.43690@newssvr29.news.prodigy.com:
>
> > Hello Group
> >
> > I have a query that works in Oracle and SQL Server, but fails
> > in Microsoft Access.
> >
> > The query is:
> >
> > SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
> >
> > Here is my sample data:
> >
> > TABLE1.DATA
> > Row1 A&M Stores
> > Row2 A&P Grocery
> > Row3 Assoc. Foods
> >
> > Under Oracle and SQL Server the rows that are returned are
> > Rows 1 and 2. Under Access no rows are returned.
> >
> > The goal is to write a SQL statement that works on all 3
> > platforms without creating a customized query for each
> > platform (or actually custom just for Access). Please note
> > that I know how to write a query that would work in Access,
> > but that query would use the InStr function which is not
> > universally available.
> >
> > It is my theory that when Access (or Jet) executes the query
> > it internally executes the query as a LIKE type expression,
> > and seeing that there are special characters in the literal
> > string this causes the query to return no rows.
> >
> > Thank you!
> > Joe
> >
> > PS: Anyone emailing me responses are appreciated
> >
> >
>
> I touched up your query to explain what I think is going on:,
> SELECT data from TABLE1 WHERE data>=65+38 AND data<65
>
> Seems to me that nothing should get returned in any version.
> However, the <65 (the ascii value for A) may automatically be
> doing a left() in SQL server and Oracle.
>
> Using WHERE data LIKE "A&*" will work.
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
- Next message: Michel Walsh: "Re: Finding the end of a recordset"
- Previous message: Lauri: "Re: Date query"
- In reply to: Bob Quintal: "Re: SQL query fails"
- Next in thread: John Spencer (MVP): "Re: SQL query fails"
- Reply: John Spencer (MVP): "Re: SQL query fails"
- Reply: Bob Quintal: "Re: SQL query fails"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
