1

i would like a clear answer of why can't i process a query like this one with OleDb

"select top 0 * FROM [APPELLATION$] WHERE (((([libel_app] LIKE '%g') OR ([region] LIKE '%g')) OR ([couleur] LIKE '%g')))"

I can with SQL but when i try to fetch some data from an Excel file it throws an error that tells me that i use some restricted word or i have some symbols that are restricted. I read the list of restricted words and symbols and did not find any of them in my query. My program is giving me SQL request, i would not like to end up looking if the TOP N number is whether or not is zero, but if i have no solution...

Thanks in advance.

Oflocet
  • 566
  • 3
  • 9
  • 19

2 Answers2

2

Your collegues should remove hard-coded flavor-specific SQL from their code in the first place especially if it is to be used on different types of connections.

See the accepted answer on Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? for some of the flavors. Excel is not listed there, I bet it uses the same syntax as MsAccess but I'm not suprised it acts completely different upon such a weird query. As Paddy already mentioned that query does not make any sense, and should be avoided.

Fixing this by matching "SELECT TOP 0" is ugly and will make maintaining software pure hell (been there :( )

Community
  • 1
  • 1
C.Evenhuis
  • 25,996
  • 2
  • 58
  • 72
  • "I bet it uses the same syntax as MsAccess" -- Indeed it uses the Access database engine, hence the same syntax. – onedaywhen Mar 12 '12 at 11:00
0

The N in TOP N must be a numeric value of 1 (Banker's rounded) or greater.

e.g. 

SELECT TOP 0.5 * FROM MyTable;  -- rounds to zero = error

SELECT TOP 0.6 * FROM MyTable;  -- rounds to 1 = no problem
onedaywhen
  • 55,269
  • 12
  • 100
  • 138