Jump to content

Completely lost with ExecuteSQL


This topic is 2679 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Just trying to learn this executeSQL and can not seem to get sensible results.

I have a table which represents any cost prices changes that occur with a product.

If I use a simple statement eg ExecuteSQL ( 
"SELECT ProductDescription 
FROM  CostPriceChanges"
;"";",")

i get a nice list of products show up eg Sweet Potatoes, Limes, Limes, Lettuce, etc etc

However when i add where criteria I get the dreaded ?

ExecuteSQL ( 
"SELECT ProductDescription 
FROM  CostPriceChanges
WHERE Cost Price >= .01"
;"";",")

I am thinking, it is something in the syntax of the where that i am not getting into the grey matter but that is where i am having trouble.

Any Help would be appreciated.

Link to comment
Share on other sites

Thanks beverly, that works only new to this SQL thingy. my next problem which i hope is as simple is, when I select

SELECT ProductDescription,\"Cost Price\",\"RRP Price\",Date
FROM CostPriceChanges

I get an error. So in other words using your above solutions it works for both Cost price and rrp price but as soon as i throw in the Date field it breaks.

Also I still get no joy whatsoever when using the WHERE clause if for eg I say WHERE ProductDescription = "Limes" it fails or Just Limes without quotes also fails. Is their a strict format that i am missing?  I thought that part was pretty straight forward but sadly no.

Link to comment
Share on other sites

16 minutes ago, Peter Barfield said:

 as soon as i throw in the Date field it breaks.

"Date" is a reserved word in SQL. Just get into the habit of quoting your field names.

 

20 minutes ago, Peter Barfield said:

when using the WHERE clause if for eg I say WHERE ProductDescription = "Limes" it fails or Just Limes without quotes also fails.

Use single quotes for text constants.

Link to comment
Share on other sites

thanks comment, I have just finished playing around and did in fact just created another field for the purpose of testing called it datework and yes that worked. going to try and get the other where part working now. Quick question though is there any rules to using global fields in the where criteria. just purely for eg where productdescription = _global_productdescription field.?

Link to comment
Share on other sites

While it will work the way you wrote it, I would suggest you use the arguments parameter of the ExecuteSQL() function for this, i.e. write:

WHERE productdescription = ? 

and add YourTable::_global_productdescription as a parameter (see the help for an example). Among other things, this has the advantage of not having to  worry about the field name meeting SQL standards.

Note also that a field name inserted as text into a calculation formula will not be automatically updated if your rename the field. There are various techniques to get around the problem by using the GetFieldName() function.

 

Link to comment
Share on other sites

This topic is 2679 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.