[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error in Select Clause

Posted on 2005-05-11
22
Medium Priority
?
1,186 Views
Last Modified: 2011-10-03
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
Comment
Question by:romston69
  • 8
  • 7
  • 3
  • +2
22 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13978976
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13978992
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13978996
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:romston69
ID: 13979033
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13979112
(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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13979116
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13979137
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
 

Author Comment

by:romston69
ID: 13979249
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13979297
>> 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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13979331
Is this a linked server to a progress datbase?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13979347
>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
 

Author Comment

by:romston69
ID: 13980042
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13980251
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
 
LVL 6

Expert Comment

by:PePi
ID: 13980280
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
 

Author Comment

by:romston69
ID: 13980313
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
 

Author Comment

by:romston69
ID: 13980333
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13980355
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
 

Author Comment

by:romston69
ID: 13980409
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13980568
crud.  Not sure if I will be able to help much more.....others?
0
 

Author Comment

by:romston69
ID: 13980593
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
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 200 total points
ID: 13980661
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
 

Author Comment

by:romston69
ID: 13980692
if i was rich in pts i'll give you all 50 :P

anyway thx all !
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question