SQL查询失败 [英] SQL query fails

问题描述

Hello Group


我有一个在Oracle和SQL Server中运行的查询，但在Microsoft中失败

Access。

查询是：


来自TABLE1的SELECT数据WHERE数据> =''A&''AND数据<''''''''


这是我的样本数据：


TABLE1.DATA

Row1 A& M商店

Row2 A& P Grocery

Row3 Assoc。食物


在Oracle和SQL Server下，返回的行是第1行和第2行。

在Access下没有返回行。


目标是编写一个可在所有3个平台上运行的SQL语句，而不是为每个平台创建一个自定义查询（或实际上只为

定制）访问）。请注意，我知道如何编写一个可以在

Access中使用的查询，但该查询将使用InStr函数，该函数不是普遍可用的。

可用。


我的理论是当Access（或Jet）在内部执行查询时

执行查询作为LIKE类型表达式，并看到有

文字字符串中的特殊字符导致查询返回没有

行。


谢谢！

Joe


PS：任何向我发送电子邮件回复的人都表示赞赏

解决方案

----- BEGIN PGP SIGNED消息-----

哈希：SHA1


你不能在所有数据库引擎中使用相同的外卡字符。 一个

或更多字符 MS SQL&中的外卡字符Oracle是

&符号（&）; Access（JET）使用星号（*）。 任何一个

字符 MS SQL&中的外卡Oracle是下划线（_）;访问

（JET）使用问号（？）。


-

MGFoster ::: mgf00<在> earthlink< decimal-point> net

奥克兰，加利福尼亚州（美国）


-----开始PGP SIGNATURE -----

版本： PGP个人隐私5.0

Charset：noconv

iQA / AwUBQQ6J1oechKqOuFEgEQJO + wCg0CS3xN5VK7CIchd1L0SIbM az3K0AoPm1

oi27wjYZCoy8XKU0 + AekmyO3

= fAcx

-----结束PGP SIGNATURE -----

Joe Stanton写道：

Hello Group

我有一个可以在Oracle和SQL Server中运行的查询，但在Microsoft
Access中失败。

查询是：

选择数据fromTABLE1 WHERE数据> =''A&''AND数据<''A'''''

这是我的样本数据：

TABLE1 .DATA
Row1 A& M商店
Row2 A& P Grocery
Row3 Assoc。食品

在Oracle和SQL Server下，返回的行是第1行和第2行。
在Access下没有返回行。

目标是写一个SQL语句适用于所有3个平台，无需为每个平台创建自定义查询（或实际上仅为访问自定义）。请注意，我知道如何编写一个可以在
Access中运行的查询，但该查询将使用不普遍可用的InStr函数。

这是我的理论当Access（或Jet）在内部执行查询时，
将查询作为LIKE类型表达式执行，并且看到文字字符串中有特殊字符，这会导致查询返回
行。

感谢您的回复，但这里的问题不是使用通配符，而是

查找数据。

" MGFoster" < [e-mail]>在留言中写道

news：zR ***************** @ newsread1.news.pas.earthl ink.net ...

-----开始PGP签名消息-----
哈希：SHA1

你不能在所有数据库引擎中使用相同的外卡字符。 一个或多个字符 MS SQL&中的外卡字符Oracle是&符号&（&）; Access（JET）使用星号（*）。 任何一个
字符 MS SQL&中的外卡Oracle是下划线（_）;访问
（JET）使用问号（？）。

-
MGFoster ::: mgf00< at> earthlink< decimal-point> net
奥克兰，加利福尼亚州（美国）

-----开始PGP签名-----
版本：PGP个人隐私5.0
Charset：noconv

iQA / AwUBQQ6J1oechKqOuFEgEQJO + wCg0CS3xN5VK7CIchd1L0SIbM az3K0AoPm1
oi27wjYZCoy8XKU0 + AekmyO3
= fAcx
----- END PGP SIGNATURE -----

Joe Stanton写道：

Hello Group

我有一个在Oracle和SQL Server中运行的查询，但在
Microsoft Access中失败。

查询是：

从数据中选择数据WHERE数据> =''A&''和数据<''''''''
<这是我的样本数据：

TABLE1.DATA
Row1 A& M商店
Row2 A& P Grocery
Row3 Assoc。食品

在Oracle和SQL Server下，返回的行是第1行和第2行。
在Access下没有返回行。

目标是写一个SQL语句适用于所有3个平台
而无需为每个平台创建自定义查询（或实际自定义为
for Access）。请注意，我知道如何编写一个在Access中工作
的查询，但该查询将使用普遍可用的不是
的InStr函数。

这是我的理论当Access（或Jet）执行查询时，
在内部执行查询作为LIKE类型表达式，并且看到文字字符串中有
特殊字符，这会导致查询返回
没有行。

在我看来，A是A。 < " A&安培;" < A& M ...... < Assoc ...，（在大多数

字母排序方法中，至少在JET字母顺序中）。


所以没有你的记录符合标准


（数据> =''A&''）AND（数据<''A''）


（圆形大于关系是不可能的。）


因此没有选择。


-

HTH

Van T. Dinh

MVP（访问）


" Joe Stanton" < jo*@bravenewsoftware.com>在消息中写道

新闻：ZS ******************** @ newssvr29.news.prodigy .com ...

Hello Group

我有一个在Oracle和SQL Server中运行的查询，但在Microsoft
Access中失败。

查询是：

从数据中选择数据WHERE数据> =''A&''和数据<''''''

这是我的样本数据：

TABLE1.DATA
Row1 A& M商店
Row2 A& P Grocery
Row3 Assoc。食品

在Oracle和SQL Server下，返回的行是第1行和第2行。
在Access下没有返回行。

目标是写一个SQL语句适用于所有3个平台，无需为每个平台创建自定义查询（或实际上仅为访问自定义）。请注意，我知道如何编写一个可以在
Access中运行的查询，但该查询将使用普遍可用的不是
的InStr函数。

这是我的理论当Access（或Jet）在内部执行查询时
将查询作为LIKE类型表达式执行，并且看到文字字符串中有
特殊字符，这会导致查询返回
没有行。

谢谢！
Joe

PS：任何通过电子邮件发送给我的回复都表示赞赏

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

解决方案

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can''t use the same wild card character in all db engines. The "one
or more characters" wild card character in MS SQL & Oracle is the
ampersand (&); Access (JET) uses the asterisk (*). The "any one
character" wild card in MS SQL & Oracle is the underline (_); Access
(JET) uses the question-mark (?).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ6J1oechKqOuFEgEQJO+wCg0CS3xN5VK7CIchd1L0SIbM az3K0AoPm1
oi27wjYZCoy8XKU0+AekmyO3
=fAcx
-----END PGP SIGNATURE-----
Joe Stanton wrote:

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 for the response, but the issue here is not using wildcards but
just finding data.
"MGFoster" <[e-mail]> wrote in message
news:zR*****************@newsread1.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can''t use the same wild card character in all db engines. The "one
or more characters" wild card character in MS SQL & Oracle is the
ampersand (&); Access (JET) uses the asterisk (*). The "any one
character" wild card in MS SQL & Oracle is the underline (_); Access
(JET) uses the question-mark (?).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ6J1oechKqOuFEgEQJO+wCg0CS3xN5VK7CIchd1L0SIbM az3K0AoPm1
oi27wjYZCoy8XKU0+AekmyO3
=fAcx
-----END PGP SIGNATURE-----
Joe Stanton wrote:

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.

It looks to me that "A" < "A&" < "A&M ..." < "Assoc...", (in most of
alphabetical ordering methods and at least in JET alphabetical ordering).

so none of your Records meet the criteria

(Data >= ''A&'') AND (Data < ''A'')

(a "circular" greater than relationship which is impossible.)

and therefore none is selected.

--
HTH
Van T. Dinh
MVP (Access)


"Joe Stanton" <jo*@bravenewsoftware.com> wrote in message
news:ZS********************@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

这篇关于SQL查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！