IBM Power Ideas Portal
Hide about this portal


This portal is to open public enhancement requests against IBM Power Systems products, including IBM i. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas

  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Add a new idea Subscribe
Status Not under consideration
Workspace IBM i
Categories Db2 for i
Created by Guest
Created on Jul 26, 2019

RELATED IDEAS

Return Type for DB2 Coalesce Function Should Be "CHAR" NOT "DECFLOAT(34)" when params are (INT,CHAR) OR (CHAR,INT)

See this idea on ideas.ibm.com

Apparently by design when the DB2 coalesce function evalute parameters consisting of mixed data-types, particularly CHAR(S) and INTEGERS it chooses a return data-type of DECFLOAT(34). When only these data-types are involved it should choose a variable character based datatype because all INTEGERS can be represented as CHARACTERS.


Use Case:

This is problematic when a wrapper function like IFNULL evaluates one of the following senarios:
Senario #1
PARAM1 (data-type = VARCHAR value = 'val')
PARAM2 (data-type = INTEGER value = 0)
RETURN-TYPE (data-type = DECFLOAT(34) value = +++)

This obsecures valid data because of the promotion of the return type to data-type DECFLOAT(34)

OR

Senario #2
PARAM1 (data-type = INTEGER value = NULL)
PARAM2 (data-type = CHAR value = ' ')
RETURN (data-type = DECFLOAT(34) value = +++)

The common sense return should be data-type CHARS because INTEGERS can be represented as CHARS and the SPACE would be returned instead of the pluses (presumably LOW/HIGH Values?)


Idea priority High
  • Guest
    Reply
    |     Mar 16, 2020

    Hi,
    We're declining this RFE because this is working as intended across the Db2 Family.
    Also, there is an approach that can be used, which is described as follows:

    The rules for the result data type of COALESCE for the Db2 family of products were discussed and decided in 2002. Changing the result data type now would break many applications that have been coded to expect the current result.

    When using ACS to run the following statement, a warning is returned to indicate the cast did not work. When using STRSQL, there is a data mapping error shown on the message line and the ++++ result indicates a problem value.

    For example: values coalesce ('val', 0 );

    To change the result of the COALESCE function to be character data, you must cast your numeric values to a character data type. Then the result will be character data.

    For example: values coalesce ('val', cast(0 as varchar(3)));

  • Guest
    Reply
    |     Oct 25, 2019

    Due to processing by IBM, this request was reassigned to have the following updated attributes:
    Brand - Servers and Systems Software
    Product family - Power Systems
    Product - IBM i
    Component - Db2 for i
    Operating system - IBM i
    Source - None

    For recording keeping, the previous attributes were:
    Brand - Servers and Systems Software
    Product family - Power Systems
    Product - IBM i
    Component - Core OS
    Operating system - IBM i
    Source - None

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.