Newbee Newbee - 1 month ago 9
SQL Question

How to make a cell blank in a row from a query result?

I have a query as below:

SELECT
cc.chain_desc as chain_desc
,cc.chain_id as chain_id
,COUNT(distinct t.trans_id) as TranCount
FROM TRANSACTION AS t
LEFT OUTER JOIN location AS l
ON t.location_id = l.location_id
LEFT OUTER JOIN trans_line AS tl
ON t.trans_id = tl.trans_id
LEFT OUTER JOIN contract as c
ON t.contract_id = c.contract_id
LEFT OUTER JOIN chain_desc as cc
ON l.chain_id = cc.chain_id
WHERE
t.loc_country = 'U'
AND c.issuer_id IN (156966,166203)
AND t.trans_date >= '2016-10-01 00:00'
and t.trans_date < '2016-10-31 00:00'
AND tl.cat NOT IN ('DEF','DEFD','DEFC')
GROUP BY cc.chain_desc, cc.chain_id


UNION

SELECT
'TOTAL'
,0
,COUNT(distinct t.trans_id)
FROM TRANSACTION AS t
LEFT OUTER JOIN location AS l
ON t.location_id = l.location_id
LEFT OUTER JOIN trans_line AS tl
ON t.trans_id = tl.trans_id
LEFT OUTER JOIN contract as c
ON t.contract_id = c.contract_id
LEFT OUTER JOIN chain_desc as cc
ON l.chain_id = cc.chain_id
WHERE
t.loc_country = 'U'
AND c.issuer_id IN (156966,166203)
AND t.trans_date >= '2016-10-01 00:00'
and t.trans_date < '2016-10-31 00:00'
AND tl.cat NOT IN ('DEF','DEFD','DEFC')


The above query when executed reurns the below result:

enter image description here

I need the result to be displayed as below:

enter image description here

The column "Chain_Id" is of "integer" type, how can I make that blank?.

Answer

In Informix you can use NULL in the projection list, but the column must have a type. Since in Informix NULL does not have a type, you need to use a CAST.

SELECT NULL::INTEGER AS id FROM systables WHERE tabid = 1;

SELECT CAST(NULL AS INTEGER) AS id FROM systables WHERE tabid = 1;

You can check the answers to this other question (Informix: Select null problem).

You can check the IBM Knowledge Center (NULL Keyword).

Comments