Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Coalesce


This question is answered.


Permlink Replies: 6 - Last Post: Jan 5, 2018 2:36 AM Last Post By: Melissa Torn
Melissa Torn

Posts: 143
Registered: 4/30/09
Coalesce  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 30, 2017 2:53 AM
IB 2007
Database=Interbase Sample Database Employee

SELECT COALESCE (SUM(TOTAL_VALUE),0)
FROM SALES
WHERE CUST_NO=55555

It works but I get EMPTY result, i was expecting to get 0 (Zero)
What is the problem ?
Thank you
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Coalesce  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 30, 2017 10:45 AM   in response to: Melissa Torn in response to: Melissa Torn
On 12/30/2017 5:53 AM, Melissa Torn wrote:
SELECT COALESCE (SUM(TOTAL_VALUE),0)
FROM SALES
WHERE CUST_NO=55555

I don't have 2007 around to test right now, but it gives 0 like expected in XE3.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Melissa Torn

Posts: 143
Registered: 4/30/09
Re: Coalesce  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 3, 2018 3:19 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thank You Jeff,

Is there another way to get zero value instead of NULL ?

Thank You
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Coalesce  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 3, 2018 11:49 AM   in response to: Melissa Torn in response to: Melissa Torn
On 1/3/2018 6:19 AM, Melissa Torn wrote:
Thank You Jeff,

Is there another way to get zero value instead of NULL ?

Thank You

Try Wrapping your COALESCE with another COALESCE and see if that resolves it

SELECT COALESCE (COALESCE (SUM(TOTAL_VALUE),0), 0)
FROM SALES
WHERE CUST_NO=55555

Unfortunately I do not have 2007 installed anywhere to test any workarounds.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Melissa Torn

Posts: 143
Registered: 4/30/09
Re: Coalesce  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 3, 2018 8:34 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thank You Jeff,

I got below error msg.
Error at line 1
data type not supported for arithmetic
SQL - SELECT COALESCE (COALESCE (SUM(TOTAL_VALUE),0), 0)
FROM SALES
WHERE CUST_NO=55555

Try Wrapping your COALESCE with another COALESCE and see if that resolves it

SELECT COALESCE (COALESCE (SUM(TOTAL_VALUE),0), 0)
FROM SALES
WHERE CUST_NO=55555

Unfortunately I do not have 2007 installed anywhere to test any workarounds.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Coalesce
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 4, 2018 10:27 AM   in response to: Melissa Torn in response to: Melissa Torn
On 1/3/2018 11:34 PM, Melissa Torn wrote:
Thank You Jeff,

I got below error msg.
Error at line 1
data type not supported for arithmetic
SQL - SELECT COALESCE (COALESCE (SUM(TOTAL_VALUE),0), 0)
FROM SALES
WHERE CUST_NO=55555

Try Wrapping your COALESCE with another COALESCE and see if that resolves it

SELECT COALESCE (COALESCE (SUM(TOTAL_VALUE),0), 0)
FROM SALES
WHERE CUST_NO=55555

Unfortunately I do not have 2007 installed anywhere to test any workarounds.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Then try casting the inner coalesce like

SELECT COALESCE (cast(COALESCE (SUM(TOTAL_VALUE),0) as numeric(9,2)), 0)
FROM SALES
WHERE CUST_NO=55555

Although rather than fighting this bug in 2007, perhaps simpler to create a view
that already does the sum and coalesce against the view.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Melissa Torn

Posts: 143
Registered: 4/30/09
Re: Coalesce  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 5, 2018 2:36 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thank You so much Jeff,
I created a view as your advice.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02