| DML | //
// Demonstration of how to use the various MaxDB functions
//
// -> server supplied functions
//
// Precedence Rules
// Expressions within parentheses are done first, then prefix operators (e.g. -1),
// then multiplication and division, then addition and subtraction.
// When more operations of equal precedence are
// together (e.g. 1 * 5 / 4) they are done from left to right.
//
// ,555 + -22 / (12 - 3) * 66 AS dec4
// ^ ^ ^ ^ ^
// 5th 2nd 3rd 1st 4th
//
SELECT (12 - 3) AS int1
,-22 / (12 - 3) AS dec2
,-22 / (12 - 3) * 66 AS dec3
,555 + -22 / (12 - 3) * 66 AS dec4
FROM dual
//
// Equivalent DB2 command:
// SELECT (12 - 3) AS int1
// ,-22 / (12 - 3) AS int2
// ,-22 / (12 - 3) * 66 AS int3
// ,555 + -22 / (12 - 3) * 66 AS int4
// FROM sysibm.sysdummy1;
//
// Result from db2
//
// INT1 INT2 INT3 INT4
// ----------- ----------- ----------- -----------
// 9 -2 -132 423
//
// Equivalent MaxDB to DB2 command:
SELECT (12 - 3) AS int1
,FIXED(-22 / (12 - 3)) AS int2
,FIXED(-22 / (12 - 3) * 66) AS int3
,555 + FIXED(-22 / (12 - 3)) * 66 AS int4
FROM dual
//
// TRUNC versus ROUND
SELECT (12 - 3) AS int1
,-22 / (12 - 3) AS dec2a
,FLOOR(-22 / (12 - 3)) AS dec2b
,FIXED(-22 / (12 - 3) * 66,5,2) AS dec3
,TRUNC(555 + -22 / (12 - 3) * 66,2) AS dec4
,ROUND(555 + -22 / (12 - 3) * 66,2) AS dec5
FROM dual
//
// See result column dec1 in comparison to int2
// 3 DIV 0,5 leads to an error message
SELECT 3 / 1 AS dec1
,TRUNC(3/1,2) AS dec2
,TRUNC(3/1,0) AS int1
,3 DIV 1 AS int2
,3 MOD 2 AS int3
,3 MOD 3 AS int4
FROM dual
//
|
| Result | > SELECT (12 - 3) AS int1 ,-22 / (12 - 3) AS dec2 ,-22 / (12 - 3) * 66 AS dec3 ,555 + -22 / (12 - 3) * 66 AS dec4 FROM dual
> | INT1 | DEC2 | DEC3 | DEC4 |
> | -------- | ------------------------------------------------------- | ---------------------------------------------- | ---------------------------------------------- |
> | 9 | -2.444444444444444444444444444444444444 | -161.33333333333333333333333333333333333 | 393.66666666666666666666666666666666667 |
> SELECT (12 - 3) AS int1 ,FIXED(-22 / (12 - 3)) AS int2 ,FIXED(-22 / (12 - 3) * 66) AS int3 ,555 + FIXED(-22 / (12 - 3)) * 66 AS int4 FROM dual
> | INT1 | INT2 | INT3 | INT4 |
> | -------- | ------------------------------------------------------ | ------------------------------------------------------ | ---------------------------------------------- |
> | 9 | -2 | -161 | 423 |
> SELECT (12 - 3) AS int1 ,-22 / (12 - 3) AS dec2a ,FLOOR(-22 / (12 - 3)) AS dec2b ,FIXED(-22 / (12 - 3) * 66,5,2) AS dec3 ,TRUNC(555 + -22 / (12 - 3) * 66,2) AS dec4 ,ROUND(555 + -22 / (12 - 3) * 66,2) AS dec5 FROM dual
> | INT1 | DEC2A | DEC2B | DEC3 | DEC4 | DEC5 |
> | -------- | ------------------------------------------------------- | ------------------------------------------------------ | ----------- | ---------------------------------------------- | ---------------------------------------------- |
> | 9 | -2.444444444444444444444444444444444444 | -3 | -161.33 | 393.66 | 393.67 |
> SELECT 3 / 1 AS dec1 ,TRUNC(3/1,2) AS dec2 ,TRUNC(3/1,0) AS int1 ,3 DIV 1 AS int2 ,3 MOD 2 AS int3 ,3 MOD 3 AS int4 FROM dual
> | DEC1 | DEC2 | INT1 | INT2 | INT3 | INT4 |
> | ------------------------------------------------------- | --------- | ------------------------------------------------------ | -------- | -------- | -------- |
> | 3.0000000000000000000000000000000000000 | 3.00 | 3 | 3 | 1 | 0 |
|