My understanding is numeric fields can hold blank values. Isn't that what the BLANK FIELD command and ISBLANK () is all about ? -------- Original message --------From: Dave Thayer davethayer@gmail.com Date: 15/06/2016 00:49 (GMT+02:00) To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number? On Tue, Jun 14, 2016 at 3:41 PM, Kurt Wendt Kurt_Wendt@globetax.com 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 looking around in online help - but, could not quickly find the answer. So - I took a shot at trying to implement this. This is only Part of the line of code - but, you can see the attempt to define NULL: 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
Maybe something like ...CASE WHEN t.taxrate_el = 0 THEN NULL ELSE t.taxrate_el END AS taxrate_el FROM ....
Yes, but a BLANK in a numeric field evaluates to zero.
What you want is something like:
SELECT d.secid ,d.sec_name AS sec_nam ,d.coi AS issue_cnty , IIF(t.taxrate_el=0, NULL, t.taxratee_el) AS taxrate_el FROM [dbo].[Dividends] as d JOIN [dbo].[Trandata] as t ON t.dividend_id = d.dividend_id
Use the IIF to inject the NULL, and the AS clause to keep the result from being named exp_1 or something.
On Tue, Jun 14, 2016 at 6:05 PM, foxdev foxdev@ozemail.com.au wrote:
My understanding is numeric fields can hold blank values. Isn't that what the BLANK FIELD command and ISBLANK () is all about ? -------- Original message --------From: Dave Thayer davethayer@gmail.com Date: 15/06/2016 00:49 (GMT+02:00) To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number? On Tue, Jun 14, 2016 at 3:41 PM, Kurt Wendt Kurt_Wendt@globetax.com 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 looking around in online help - but, could not quickly find the answer. So - I took a shot at trying to implement this. This is only Part of the line of code - but, you can see the attempt to define NULL: 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
Maybe something like ...CASE WHEN t.taxrate_el = 0 THEN NULL ELSE t.taxrate_el END AS taxrate_el FROM ....
-- Dave Thayer Denver, CO
[excessive quoting removed by server]
Ted - thanks again for your feedback.
The deal - I can't just blatantly convert Zero values to NULL values. You see - for many instances - Zero for a tax rate is Legit.
So - before they do certain data processing - data is pulled in from MS SQL. And, that Select command I showed - which was only Part of the full Select command - that's where it initially pulls in the data. As such, at that point - I can NOT flip Zero values to NULL. However, in pulling in the data into the Cursor - I want to be able to ALLOW the taxratee_el field to be able to Store/hold a NULL value. Because, when they do certain processing - and based upon another field - that's when I need to force the taxratee_el to have a value of NULL.
Hope this makes more sense.
So - it seems I should do something like what Charlie stated - but, for this tax field. Maybe like this: SELECT d.secid ,d.sec_name AS sec_nam ,d.coi AS issue_cnty , CAST(t.taxrate_el AS N(12,2) NULL) AS taxrate_el FROM [dbo].[Dividends] as d JOIN [dbo].[Trandata] as t ON t.dividend_id = d.dividend_id
I suspect that will do the trick. Am going to try it now.
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 Ted Roche Sent: Tuesday, June 14, 2016 6:56 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
Yes, but a BLANK in a numeric field evaluates to zero.
What you want is something like:
SELECT d.secid ,d.sec_name AS sec_nam ,d.coi AS issue_cnty , IIF(t.taxrate_el=0, NULL, t.taxratee_el) AS taxrate_el FROM [dbo].[Dividends] as d JOIN [dbo].[Trandata] as t ON t.dividend_id = d.dividend_id
Use the IIF to inject the NULL, and the AS clause to keep the result from being named exp_1 or something.
On Tue, Jun 14, 2016 at 6:05 PM, foxdev foxdev@ozemail.com.au wrote:
My understanding is numeric fields can hold blank values. Isn't that what the BLANK FIELD command and ISBLANK () is all about ? -------- Original message --------From: Dave Thayer davethayer@gmail.com Date: 15/06/2016 00:49 (GMT+02:00) To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number? On Tue, Jun 14, 2016 at 3:41 PM, Kurt Wendt Kurt_Wendt@globetax.com 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 looking around in online help - but, could not quickly find the answer. So - I took a shot at trying to implement this. This is only Part of the line of code - but, you can see the attempt to define NULL: 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
Maybe something like ...CASE WHEN t.taxrate_el = 0 THEN NULL ELSE t.taxrate_el END AS taxrate_el FROM ....
-- Dave Thayer Denver, CO
[excessive quoting removed by server]
-
foxdev -
Kurt Wendt -
Ted Roche