Thanks Charlie - seems you may have the answer I needed. Although - its actually the taxrate_el field that needs to contain NULL. Coi is Country of Issue - and is thus a Country code...
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Charlie Sent: Tuesday, June 14, 2016 10:48 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
On 6/14/2016 5:41 PM, Kurt Wendt wrote:
OK - part of the code in the system pulls data from MS SQL. So - I was trying to mod this code - to add NULL as an option into the Select. But, I suspect I'm not doing it correctly. Was
Look at the CAST function to allow and create nulls in a query result cursor.
SELECT d.secid ,d.sec_name AS sec_nam ,d.coi AS issue_cnty , t.taxrate_el NULL FROM [dbo].[Dividends] as d JOIN [dbo].[Trandata] as t ON t.dividend_id = d.dividend_id
So if coi is the field that is supposed to be null if it is zero, you would do something like this:
SELECT d.secid, d.sec_name as sec_name, CAST(IIF(d.coi=0, .null., d.coi) as N(12.2) NULL) as issue_cnty ... (and so on)
I think someone else mentioned it already but having a separate SELECT (into a temporary cursor) for the Excel export is ideal. Remember, this is VFP, you're not limited to SQL-only-kinds of data handling or other junk from other languages. Temporary cursors are extremely powerful, flexible, fast, and efficient. If you pull from MySQL into a table or cursor, you could immediately do another select from that table or cursor into your Excel exporting cursor. You could maybe do another query into MySQL, but if you've already pulled the data you need it'll be way faster to work with machine-local data.
-Charlie
[excessive quoting removed by server]