• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1368
  • Last Modified:

Error in Select Clause

Hi,

  I'm trying to set a request in Sql 2000 server but i always have an error...

i try this:

SELECT inmaitre.[code-pro], inmaitre.[desc-fr], inmaitre.[desc-an], inmaitre.[prix-cout], inmaitre.[unit-stoc]
FROM inmaitre
WHERE (((inmaitre.[acti-inac])="A") AND ((inmaitre.[code-cie])="HFL"));

And the i have this error:

Error in SELECT clause: expression near '['. Missing FROM clause. Unable to parse query text.

and after that i "try" to fix my code by doing this:

SELECT     inmaitre. [ code - pro ], inmaitre. [ DESC - fr ], inmaitre. [ DESC - an ], inmaitre. [ prix - cout ], inmaitre. [ unit - stoc ]
FROM         inmaitre
WHERE     (((inmaitre. [ acti - inac ]) = " A ") AND ((inmaitre. [ code - cie ]) = " HFL "));

and it doesn't work coz the name of the field are not correct...

Someone can help me ???

thx Romston :)
0
romston69
Asked:
romston69
  • 8
  • 7
  • 3
  • +2
1 Solution
 
rafranciscoCommented:
Try changing the double-quotes to single-quotes:

SELECT inmaitre.[code-pro], inmaitre.[desc-fr], inmaitre.[desc-an], inmaitre.[prix-cout], inmaitre.[unit-stoc]
FROM inmaitre
WHERE (((inmaitre.[acti-inac])='A') AND ((inmaitre.[code-cie])='HFL'));
0
 
Kevin3NFCommented:
looks ok to me....can you comment out the WHERE clause though and see if it will parse?  Or, try with just one field, or even no brackets at all
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, if your query is only one table, you don't have to use it's name when referring to the fields

SELECT [code-pro], [desc-fr], [desc-an], [prix-cout], [unit-stoc]
FROM inmaitre
WHERE [acti-inac]='A' AND [code-cie]='HFL';

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
romston69Author Commented:
Ok,
i tried this:

SELECT [code-pro], [desc-fr], [desc-an], [prix-cout], [unit-stoc]
FROM inmaitre
WHERE [acti-inac]='A' AND [code-cie]='HFL';

and i have againt the: "Error in SELECT clause: expression near '['. Missing FROM clause. Unable to parse query text." error msg.

and my code is modified like this:

SELECT     [ code - pro ], [ DESC - fr ], [ DESC - an ], [ prix - cout ], [ unit - stoc ]
FROM         inmaitre
WHERE     [ acti - inac ] = 'A' AND [ code - cie ] = 'HFL';



0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Wild guess)  Are all of your fields spelled correctly?  
Also, if you can get rid of the dashes in your field names, then you won't need the square brackets.
Also, if this is not Access then get rid of the semi-colon ( ; ) at the end.

Hope this helps.
-Jim
0
 
Kevin3NFCommented:
Strange....this worked in my test:

SELECT [field-one], [field-two]
FROM mytable1
WHERE [field-one]='A' AND [field-two]='HFL';

Try letting Query Analyser and the object build your select statement for you, with no WHERE clause....
0
 
rafranciscoCommented:
Wild guess also, try using double-quotes instead of square-brackets:

SELECT "code-pro", "desc-fr", "desc-an", "prix-cout", "unit-stoc"
FROM inmaitre
WHERE "acti-inac"='A' AND "code-cie"='HFL';
0
 
romston69Author Commented:
Ok,
    I can't change the field names coz they are field that i read from a progress database :S

I try to delete the ; at the and and i have the same error :s
I try to replace the square-brackets with double-quotes and it return 0 rows but i don't have a message error :S

0
 
rafranciscoCommented:
>> I try to replace the square-brackets with double-quotes and it return 0 rows but i don't have a message error <<

And you are sure that there should be rows returned right?  Also, the tables are in SQL Server right and not in a Progress database?  Can you provide some sample data.
0
 
Kevin3NFCommented:
Is this a linked server to a progress datbase?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>progress database
What's a 'Progress database'?  If it's a specific database tool, then there's likely something going on where they have some 'special' SQL formatting that you're missing...
0
 
romston69Author Commented:
Umm when i create a table in Sql server with the same name, i can make a select statement like the one i said before.

But when this select is from a table that is linked from a one in progress it does'nt work...

kinda weird...

ps: i have 65 550 rows in my inmaitre table and the select i made have normaly a lots of return rows.
0
 
Kevin3NFCommented:
I think the issue is likely related to how you are calling the linked server...

Try Select T.[field-one]
from [LinkedServername].[Database].[owner].[table] as T
0
 
PePiCommented:
why not create a temp table and bulk insert the data from progress? I don't know how this will affect the performance though.
0
 
romston69Author Commented:
I simplify my request by :

SELECT T.[code-pro]
FROM powerGUI.inmaitre as T

and i have this message error:

ADO error: [MERANT][ODBC PROGRESS driver][PROGRESS]** Unable to understand after -- "DECLARE". (247)
[MERANT][ODBC PROGRESS driver][PROGRESS]** Invalid statement (254)

0
 
romston69Author Commented:
The ideal is that i want to import to sql serv  a filtered version of the data in progress and not import all the data to sql serv

>Comment from PePi
>Date: 05/11/2005 01:53PM EDT
> Comment  

>why not create a temp table and bulk insert the data from progress? I don't know how this will affect the performance >though.

0
 
Kevin3NFCommented:
If this is a one time thing, can you just export from progress and DTS into SQL?

Otherwise, you are going to have to find the correct SELECT syntax to reference a Linked Server to a progress db through a Merant driver....
0
 
romston69Author Commented:
Umm the thing is that i want to use DTS to make this importation on my server once a day :S

I'm making this request via DTS of sql serv.2000

0
 
Kevin3NFCommented:
crud.  Not sure if I will be able to help much more.....others?
0
 
romston69Author Commented:
AAAA !!
I think i found something:

Select statements generated in Microsoft Access can only be used in Access when going to the Progress database.
With the Progress database, using brackets in your select statement will produce the error "Unable to understand after -- "DECLARE". (247)" unless you are using Microsoft Access when connecting to the Progress database.

For example: Select [emp].[employee] from [emp] where emp is the table name and employee is the column name, Progress will return the error- Unable to understand after -- "DECLARE". (247)" unless you are using Microsoft Access to connect to Progress.

When select statements are executed in Microsoft Access the Microsoft Jet engine puts brackets around the column names and the table names when you view the SQL. This works fine going from Microsoft Access to the Progress database. If you copy the SQL from Access to another application you will receive the above error, however.

When executing a select statement do not put brackets around the column names or table names.


----------------
Ok what i did is i wrote this request:

SELECT code-pro, desc-fr, desc-an, prix-cout, unit-stoc
FROM inmaitre
WHERE ((acti-inac like "A") AND (code-cie like "HFL"))

And it works, but if sql serv try to arrange the code he'll separate the field name. So i just put the code i did'nt use the query analyser.

Thx All :) How can i give my 50 pts ? :S
0
 
Kevin3NFCommented:
Any way you like, or post a question in Community SUpport asking for a refund since you resolved it yourself:
http://www.experts-exchange.com/Community_Support/
0
 
romston69Author Commented:
if i was rich in pts i'll give you all 50 :P

anyway thx all !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 8
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now