#1  
Old 2nd January 2019, 04:12 AM
Andreas Schachtner Andreas Schachtner is offline
Member
 
Join Date: Jul 2009
Posts: 36
Default Change NAN values in Single Field

I have a Table with a single field and in some rows we have NAN values.

How can i set these rows with SQL to another value?
  #2  
Old 4th January 2019, 03:42 AM
Andreas Schachtner Andreas Schachtner is offline
Member
 
Join Date: Jul 2009
Posts: 36
Default

This SQL brings an exeption in EM with the Table in the Attachement.

Code:
Select Sum(Coalesce(Dauer,0)),Mitarbeiter from MitarbeiterPflegeNachweis
where Dauer>0
group by Mitarbeiter
Is there a SQL function for testing NAN as Coalesce test for NULL?
Is NAN a valid value in a Nexus Table?
Attached Files
File Type: zip Table.zip (292.1 KB, 1 views)
  #3  
Old 4th January 2019, 09:56 AM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: Change NAN values in Single Field

> This SQL brings an exeption in EM with the Table in the Attachement.
> Select Sum(Coalesce(Dauer,0)),Mitarbeiter from
> MitarbeiterPflegeNachweis where Dauer>0
> group by Mitarbeiter


The attached table doesn't throw any exception for me. What exception
are you getting?

> Is there a SQL function for testing NAN as Coalesce test for NULL?


We don't have any SQL function for testing for NAN at the moment,
please enter an issue in our online tracker.

> Is NAN a valid value in a Nexus Table?


Yes.


--
Eivind Bakkestuen [NDD]
  #4  
Old 4th January 2019, 04:57 PM
Andreas Schachtner Andreas Schachtner is offline
Member
 
Join Date: Jul 2009
Posts: 36
Default

Hello Eivind, the SQL Error is

NexusDB: Query218422687: Query execution failed:
Unexpected exception object raised: [EInvalidOp] Invalid floating point operation

[$3CA1/15521]

....wich is fine, but how do you guy's would handle such a table with NAN values in it. Every calculation end's in NAN, wich is mathematical correct, but?

The perfect case would be a function that handles NULL and NAN in addition to the normal COALESCE and a new possibly Function for NAN. And a "is NAN" would also nice.
  #5  
Old 4th January 2019, 05:21 PM
Andreas Schachtner Andreas Schachtner is offline
Member
 
Join Date: Jul 2009
Posts: 36
Default

new featurerequest logged

#2233
  #6  
Old 4th January 2019, 06:35 PM
Michaƫl HUNAULT
 
Posts: n/a
Default Re: Change NAN values in Single Field

Le 01/01/2019 Ć* 19:12, Andreas Schachtner a Ć©critĀ*:
> I have a Table with a single field and in some rows we have NAN values.
>
>
> How can i set these rows with SQL to another value?
>
>

Hi Andreas,

convert the value to a varchar.

UPDATE YourTable
SET YourField = <value you want> WHERE CAST(YourField AS VARCHAR(60)) =
'NAN';

--
Michaƫl

  #7  
Old 4th January 2019, 08:14 PM
Andreas Schachtner Andreas Schachtner is offline
Member
 
Join Date: Jul 2009
Posts: 36
Default

Hi Michaƫl,

thx for this! You made my day! Perfect!


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update with different values in each row of a single column? John Treder nexusdb.public.support.sql 6 8th April 2018 04:30 AM
OT: Conditionally updating a single field John Turner nexusdb.public.support.sql 3 7th March 2009 02:18 AM
Change String Field Field length Brian nexusdb.public.support 1 6th March 2008 07:43 PM
Can't retrieve field values out of my field named "USER". Luke Turner nexusdb.public.support.sql 4 31st October 2006 11:10 AM
Field type = single (strange value inserted) Sophie Rattier nexusdb.public.support 4 8th February 2004 02:34 AM


All times are GMT +11. The time now is 07:18 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.