CODEWITHSUNDEEP
sqlinformix
Newbee
Newbee

Reputation: 817

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?.

Upvotes: 1

Views: 93

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

you can simply select null

 .....

 UNION 

SELECT 
    'TOTAL'
    , NULL::INTEGER
    ,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')

because null is not a type you could try add above the first query 

DEFINE test INT;
LET test = NULL;


......

SELECT 
    'TOTAL'
    , test
    ,COUNT(distinct t.trans_id) 
    .....

Or like suggusted by @Jonathan Leffler use NULL::INTEGER or CAST(NULL AS INTEGER)

Upvotes: 3

Lu&#237;s Marques
Lu&#237;s Marques

Reputation: 1451

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).

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One way is to convert to NULL:

    (case when cc.chain_id <> 0 then cc.chain_id end) as chain_id

Another is to convert everything to a string:

    (case when cc.chain_id <> 0 then cast(cc.chain_id as varchar(255)) else '' end) as chain_id

Upvotes: 2

Related Questions