Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL : Searching for records with an exact field value 1

Status
Not open for further replies.

AndrewMozley

Programmer
Oct 15, 2005
621
GB
I have a table XSORD which has a field ‘Account’ - the customer account number, and have executed the instruction :
Code:
SET EXACT ON

In order to retrieve records for a particular customer, say ‘S100’, I can execute a command :
Code:
SELECT * FROM XSORD WHERE Account = ‘S100’

This however returns a cursor which certainly includes records for customer S100, but also records for customers ‘S1004’ and ‘S1009'.

I believe that I can correct this by changing the SQL statement to :
Code:
SELECT * FROM XSORD WHERE Account == ‘S100’

It seems that in this session, SQL is not respecting the SET(“EXACT”) setting. Is that always the case?

Thanks - Andrew
 
Set Exact doesn't apply to SQL syntax. Never has???

For SQL you must use Set Ansi On.

Help: said:
Unlike SET ANSI, SET EXACT does not apply to Visual FoxPro SQL commands. For more information, see SET ANSI Command.
 
A solution without setting ANSI ON also is to look for

Code:
SELECT * FROM XSORD WHERE Account = 'S100 '
That doesn't work if account number is the full field length, but in such a case there also would be no other account number with additional digits possible, anymore.

I remember we had a discussion about this also in a quite recent thread184-1822574
Specifically, look at the screenshot of a browse window of a result I posted short after the query:
Code:
Select itemcode, sum(quantity) as total From sales group by itemcode where itemcode="3"
With or without ANSI this group by will separate the itemcodes 3 from 32, so that's a way to see that the ansi mode influces whats true by where clause, but still is separated into differing groups with group by.

In your case a query like
Code:
SELECT Account FROM XSORD group by account WHERE Account = 'S100'
Would show you either a list of accounts S100, S1004, S1009 or just S100, depending on whether SET('ANSI') is ON or OFF.

But even in case ANSI is OFF, the GROUP BY still separates the accounts and would build up aggregates (like a SUM(), COUNT(), etc.) for each account number separately.

Chriss
 
I usually do this by using the "double-equals" syntax - on the basis that it makes the command is more self-contained. If you were to rely on a particular setting for EXACT or ANSI, then you really need to store the existing setting, make the new setting, execute the command, and then restore the original setting.

It's a good general rule to always keep things as local as possible.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
I thought AndrewMozley was doing exactly that (no pun intended) and failed. Therefore deleted my previous post, that was just wrong.

You're right, Mike, using == you can both avoid SET EXACT ON and also SET ANSI ON. I think SET ANSI ON also has some other benefits worth considering as a default, but I also stand with EXACT and ANSI OFF most of the times.

Chriss
 
That's true. ou should rather not decide for SET ANSI ON jst so this query works, because the side effect can be all previously working queries have to be tested to still behave as they should.

Chriss
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top