0

The result of the following query irritates me:

select
  char(coalesce(1, cast(5 as decimal(2,0)))) as a,
  char(1 * cast(5 as decimal(2, 0))) as b
from sysibm.sysdummy1;

a = ' 00000000001.'
b = ' 0000005.'

The returned value of the first selection is clearly of type decimal(11,0). The type unification of the coalesce function is documented here (see numeric operands). Following these instructions and resolving the decimal(11,0) backwards means that the literal 1 is interpreted as a large integer.

The return type of the second selection is a decimal(7,0). How DB2 behaves on multiplication can be read here (the precision of the result is the sum of the precision of both operands). In this case, this means that the literal 1 is interpreted as a small integer.

Note that the char(..) function has only been applied to show the precision and scale of the returned value.

When having an integer literal (like 1 in the example above), how do I know how DB2 interprets it (smallint, largeint, bigint)?

I'm using DB2 for z/OS v11.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
nico.ruti
  • 605
  • 6
  • 17
  • By "terminal" I suspect you mean "literal". As the same manual you quoted [indicates](https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_implicitcaststr2num.html), character types are implicitly cast to `DECFLOAT(34)` when necessary. – mustaccio Jun 27 '16 at 14:02
  • @mustaccio Yes, sorry for the bad wording, I meant 'literal'. I believe the issue has nothing to do with character casting; the selections are only wrapped in a char()-Function to show the precision / scale of the result. – nico.ruti Jun 27 '16 at 14:15
  • "Integer" literal `'1'` is not an integer, it's a character literal. May be you could rephrase the question because right now it's not very clear what your problem is. – mustaccio Jun 27 '16 at 14:37
  • Is this what you're looking for: https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_integerconstants.html ? – mustaccio Jun 27 '16 at 16:08
  • Unfortunately, this short article does not answer my question. I'm rather looking for an explanation for the example above: Why is DB2 interpreting one literal as a largeint and the other one as a smallint? I know it's a question of detail which the normal user might not care about as long as it works. – nico.ruti Jun 28 '16 at 05:59

1 Answers1

2

All numeric constants that fit into a large integer are interpreted as such - except when they are not, thank you IBM ;).

In your second case you have to see the context in which the literal appears, that is within an arithmetic expression. The behaviour is explicitly described in the section "Arithmetic with an integer and a decimal operand" of the SQLReference:

The temporary copy of the integer that has been converted to a decimal number has a precision p and scale 0. p is 19 for a big integer, 11 for a large integer, and 5 for a small integer. In the case of an integer constant, p depends on the number of digits in the integer constant. p is 5 for an integer constant consisting of 5 digits or fewer. Otherwise, p is the same as the number of digits in the integer constant.

So the handling for constants differs from all sorts of "builtin" binary datatypes. The constant value (although binary by definition) is treated more like a decimal constant.

piet.t
  • 11,718
  • 21
  • 43
  • 52