SQL : Searching for records with an exact field value 1

[AndrewMozley]

I have a table XSORD which has a field ‘Account’ - the customer account number, and have executed the instruction :

SET EXACT ON

In order to retrieve records for a particular customer, say ‘S100’, I can execute a command :

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 :

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

 

[Tore Bleken]

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

For SQL you must use Set Ansi On.

Unlike SET ANSI, SET EXACT does not apply to Visual FoxPro SQL commands. For more information, see SET ANSI Command.

 

[AndrewMozley]

Thank you Tore. I shall bear that in mind. - Andrew M.

 

[Chris Miller]

A solution without setting ANSI ON also is to look for

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:

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

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

 

[Mike Lewis]

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

 

[Chris Miller]

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

 

[Mike Lewis]

You're right, Chris. Thanks for reminding me. But my general point about doing things as locally as possible remains valid.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

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