sqlcli and NULL cells on MaxDB

[micha_holzmann]

We have a Z-table name zabrufmat. We are using sqlcli on a Linux host.

select a.MATNR,
a.mo AS Bedarf,
SUM(b.VERME) AS HRL_Bestand,
(SUM(b.VERME) - a.mo AS Delta
FROM ZABRUFMAT a
LEFT OUTER JOIN LQUA b ON (a.MATNR = b.MATNR AND b.LGTYP = 'HRL')
GROUP BY a.MATNR,a.mo,lgtyp

If for a given material from table zabrufmat is nothing in stock, the result is NULL (nothing). From other RDBMS i know the SQL function coalesce(). Is there anything similar available for sqlcli/MaxDB?

[holger_becker]

For historical reasons, MaxDB supports different SQL modes. In our internal SQL mode, the counterpart to the COALESCE() function is called VALUE(). COALESCE() works directly in Oracle or SAPR3 SQL mode:

sqlcli -d sdb -u dba,dba select value(3, null, 2, null,1) from dual
| EXPRESSION1 |
| -------------------- |
| 3                       |

1 row selected (453 usec)

sqlcli -d sdb -u dba,dba -S oracle select coalesce(3, null, 2, null,1) from dual
| EXPRESSION1 |
| -------------------- |
| 3                       |

1 row selected (481 usec)