SQL Dialect problem...

------=_NextPart_000_0051_01C52237.79ED2EE0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Good Morning.

I'm trying to pull a query prepared in Access into MySQL and I seem to be
hitting an SQL dialect wall. I expect that the problem is occurring at the
Results."AsbestosResult1%" as well as the following line. These fields in
quotes are calculated fields from Access. Is the problem in how I delineate
them from the rest of the text (i.e., use of double quotes as compared to
single quotes, etc.)?

Thanks,
Peter




SELECT
`Bulk General Table`.SampleDate,
`Bulk General Table`.VendorSampleID,
`Ref Material Description`.MatDescription,
Results."AsbestosResult1%",
Results."AsbestosResult2%",
"AsbestosResult1%" + "AsbestosResult2%" AS Exp
FROM `Bulk General Table`,
`Ref Material Description`,
Results
WHERE `Bulk General Table`.MatDescCode = `Ref Material
Description`.MatDescCode
AND (`Bulk General Table`.VendorSampleID = Results.VendorSampleID)
AND (((("AsbestosResult1%" + "AsbestosResult2%") >= 1)))
;

Peter Crosby CIH, CSP, CHMM
Exposure Assessment Strategies, Inc.
www.eastrat.com
973-655-0145

"Protecting Your Most Important Assets..."



------=_NextPart_000_0051_01C52237.79ED2EE0--

Re: SQL Dialect problem...

Peter,

Please provide the schemas for the tables involved, too.

Thanks,

- Jan

On Sun, 6 Mar 2005 10:30:11 -0500, "Peter Crosby"
said:
> Good Morning.
>
> I'm trying to pull a query prepared in Access into MySQL and I seem to be
> hitting an SQL dialect wall. I expect that the problem is occurring at
> the
> Results."AsbestosResult1%" as well as the following line. These fields
> in
> quotes are calculated fields from Access. Is the problem in how I
> delineate
> them from the rest of the text (i.e., use of double quotes as compared to
> single quotes, etc.)?
>
> Thanks,
> Peter
>
>
>
>
> SELECT
> `Bulk General Table`.SampleDate,
> `Bulk General Table`.VendorSampleID,
> `Ref Material Description`.MatDescription,
> Results."AsbestosResult1%",
> Results."AsbestosResult2%",
> "AsbestosResult1%" + "AsbestosResult2%" AS Exp
> FROM `Bulk General Table`,
> `Ref Material Description`,
> Results
> WHERE `Bulk General Table`.MatDescCode = `Ref Material
> Description`.MatDescCode
> AND (`Bulk General Table`.VendorSampleID = Results.VendorSampleID)
> AND (((("AsbestosResult1%" + "AsbestosResult2%") >= 1)))
> ;


[snip]


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: SQL Dialect problem...

Also, Peter things to check:

(1) Must be running MySQL 3.23.6 or later.
(2) Server must have been started with "--ansi" or "--sql-
mode=3DANSI_QUOTES" in order to use double quotes.

While liberties can be taken with backticks, double quotes, and things,
as in most other software you're safest if you stick to the SQL92
standard when moving tables and queries. I got the following to work:

SELECT
`Bulk General Table`.SampleDate,
`Bulk General Table`.VendorSampleID,
`Ref Material Description`.MatDescription,
Results.`AsbestosResult1%`,
Results.`AsbestosResult2%`,
`AsbestosResult1%` + `AsbestosResult2%` AS Exp
FROM `Bulk General Table`,
`Ref Material Description`,
Results
WHERE `Bulk General Table`.MatDescCode =3D `Ref Material
Description`.MatDescCode
AND (`Bulk General Table`.VendorSampleID =3D
Results.VendorSampleID)
AND ((((`AsbestosResult1%` + `AsbestosResult2%`) >=3D 1)))
;

Note no double quotes. I'm running MySQL 4.1.7-nt.

- Jan


On Sun, 06 Mar 2005 12:13:55 -0500, "Jan Theodore Galkowski"
said:
> Peter,
>
> Please provide the schemas for the tables involved, too.
>
> Thanks,
>
> - Jan
>
> On Sun, 6 Mar 2005 10:30:11 -0500, "Peter Crosby"
> said:
> > Good Morning.
> >
> > I'm trying to pull a query prepared in Access into MySQL and I seem
> > to be hitting an SQL dialect wall. I expect that the problem is
> > occurring at the Results."AsbestosResult1%" as well as the
> > following line. These fields in quotes are calculated fields from
> > Access. Is the problem in how I delineate them from the rest of the
> > text (i.e., use of double quotes as compared to single quotes,
> > etc.)?
> >
> > Thanks, Peter
> >
> >
> >
> >
> > SELECT `Bulk General Table`.SampleDate,
> > `Bulk General Table`.VendorSampleID, `Ref Material
> > Description`.MatDescription, Results."AsbestosResult1%",
> > Results."AsbestosResult2%", "AsbestosResult1%" +
> > "AsbestosResult2%" AS Exp FROM `Bulk General Table`,
> > `Ref Material Description`, Results WHERE `Bulk General
> > Table`.MatDescCode =3D `Ref Material Description`.MatDescCode AND
> > (`Bulk General Table`.VendorSampleID =3D Results.VendorSampleID) AND
> > (((("AsbestosResult1%" + "AsbestosResult2%") >=3D 1))) ;

[snip]
--=20

Jan Theodore Galkowski (o°) =

jtgalkowski@alum.mit.edu
http://www.smalltalkidiom.net

The Smalltalk Idiom
laboratory workflow software
PHP,ANSI SQL,relational designs
Internet engineering



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: SQL Dialect problem...

I'm surprised Access even likes that syntax. I would certainly replace
the double quotes with backticks and try again. Having '%' in a field
name is a bit unusual too.

John Bonnett

-----Original Message-----
From: Peter Crosby [mailto:staternj@comcast.net]=20
Sent: Monday, 7 March 2005 2:00 AM
To: win32@lists.mysql.com
Subject: SQL Dialect problem...

Good Morning.

I'm trying to pull a query prepared in Access into MySQL and I seem to
be
hitting an SQL dialect wall. I expect that the problem is occurring at
the
Results."AsbestosResult1%" as well as the following line. These fields
in
quotes are calculated fields from Access. Is the problem in how I
delineate
them from the rest of the text (i.e., use of double quotes as compared
to
single quotes, etc.)?

Thanks,
Peter=20




SELECT
`Bulk General Table`.SampleDate,
`Bulk General Table`.VendorSampleID,
`Ref Material Description`.MatDescription,
Results."AsbestosResult1%",
Results."AsbestosResult2%",
"AsbestosResult1%" + "AsbestosResult2%" AS Exp
FROM `Bulk General Table`,
`Ref Material Description`,
Results=20
WHERE `Bulk General Table`.MatDescCode =3D `Ref Material
Description`.MatDescCode
AND (`Bulk General Table`.VendorSampleID =3D Results.VendorSampleID)
AND (((("AsbestosResult1%" + "AsbestosResult2%") >=3D 1)))
;

Peter Crosby CIH, CSP, CHMM
Exposure Assessment Strategies, Inc.
www.eastrat.com
973-655-0145

"Protecting Your Most Important Assets..."



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org