SELECT SQL - WHERE clause

[AndrewMozley]

I do not use SELECT-SQL very much (perhaps I should use it more). But I would like to write a command which will detect in a single table where there are entries for a particular key do not add up to zero.

The table is XENTN. Three of the fields are tDate (D), Folio C(8) and tValue N(9,2). A transaction on a particular day is given an identifier (Folio) and may have several records in table XENTN.

The command :
SELECT SUM(tValue) AS TotValue , Folio FROM XENTN GROUP BY Folio
. . . produces a cursor with a single record for each transaction, and one can view the resultant cursor to see any offending transactions.

How can I modify the command to produce a cursor which only shows the offending transactions?
Thanks - Andrew

 

[Chris Miller]

You do that by a HAVING clause. HAVING SUM(tValue)<>0.00 wold work in your case, so as a whole query:

SELECT SUM(tValue) AS TotValue , Folio FROM XENTN GROUP BY Folio HAVING SUM(tValue)<>0.00

Chriss

 

[Chris Miller]

By the way, you could have helped yourself, as you know you can do SSQL on a cursor, so you could do something like that, too:

SELECT SUM(tValue) AS TotValue , Folio FROM XENTN GROUP BY Folio into Cursor tempTotals
Select * From tempTotals where TotValue<>0.00

Chriss

 

[AndrewMozley]

Thank you Chris. I now have have :

SELECT SUM(tValue) AS TotValue, Folio FROM XENTN GROUP BY Folio HAVING TotValue <> 0.00

and that works. Andrew

 

[TamarGranor]

Andrew - if you need help coming up to speed on VFP's SQL, you'll find a bunch of papers on the subject on my website at

http://tomorrowssolutionsllc.com/conferencepapers.php.

The most detailed is

http://tomorrowssolutionsllc.com/ConferenceSessions/Learn to use SQL.PDF,

but it also addresses SQL Server and PostgreSQL, so might be a little overwhelming.

I actually wrote an e-book on VFP's SQL a long time ago. It's still available from Hentzenwerke:

http://www.hentzenwerke.com/catalog/tamingvfpsql.htm

Tamar

 

[AndrewMozley]

Thanks, Tamar, for your reply.

You are quite right, I need to get up to speed with the use of SELECT-SQL in my VFP applications. I don’t actually use SELECT-SQL very much in my applications, which are often inherited from earlier platforms. So these applications tend to have a fair number of USE, SET RELATION TO, APPEND BLANK, REPLACE commands &c.

But the applications do include a menu selection to give the user (really the administrator or system developer) the facility to execute one or a sequence of VFP commands - mainly to interrogate the data when a fault has occurred. I validate the command by the use of COMPILE and CATCHing any errors, then put it into a Tempfile and execute it with a DO command. And when I use this facility I first take a full back-up of the user’s data files!

So this could be a place where much more extensive use could be made of SELECT-SQL.

I would like to suggest to the administrators (and to myself) that a range of options of the SELECT command could be useful - may prepare a one or two page guide to help.

The main commands that the user could find useful would be to look at records in a single table, possibly ordered by one of its fields (like a transaction date); then a step further, add a further detail - like the customer name from a related table.

I suspect that your book “Taming Visual FoxPro’s SQL” could be useful. Did try to pick up a second-hand copy, but I see that it is now only available as an e-Book (from Amazon ~!), so what with the $40 and the cost of printing I may hold off. But does anyone have a second hand copy for sale in the UK?

Thank you very much for your guidance over the years!

 

[TamarGranor]

That book was always only an e-book. So you're not going to find a paper copy. It's not as long as some Hentzenwerke books, only 165 pages.

Tamar

 

[Mike Lewis]

Andrew, I'm not familiar with the book that Tamar mentioned. But I can tell you that she has also published a lot of useful SQL information in magazine articles and conference papers - all of which you can read for free.

Follow the link in her above post.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads