How can I replacing empty ldtext column from longdescription table in maximo using sql

Question

Symptom,Cause,Resolution are not filled up, so it empty, I want to replace the fetched ldtext columns from long description table with some text. I want show null columns with some text in a report.

Thank you

To replace null values in column use COALESCE() function . For DB2 specific solution use IFNULL() function.

SELECT COALESCE(columnName,'Some Text')

OR

SELECT IFNULL(columnName,'Some Text'

SELECT b.ticketid,(SELECT COALESCE(CAST(SYMPTOM.LDTEXT AS VARCHAR(1000),'text') 
                   FROM MAXIMO.LONGDESCRIPTION SYMPTOM  
                   WHERE SYMPTOM.LDOWNERCOL = 'PROBLEMCODE' 
                         AND SYMPTOM.LDOWNERTABLE = 'TICKET' 
                         AND SYMPTOM.LDKEY = B.TICKETUID) AS "symptom" 
FROM incident b

from the above query it doesn't work

SELECT b.ticketid, COALESCE(SELECT CAST(SYMPTOM.LDTEXT AS VARCHAR(1000)) 
                            FROM MAXIMO.LONGDESCRIPTION SYMPTOM  
                            WHERE SYMPTOM.LDOWNERCOL = 'PROBLEMCODE' 
                                  AND SYMPTOM.LDOWNERTABLE = 'TICKET' 
                                  AND SYMPTOM.LDKEY = B.TICKETUID), 'some text') AS "symptom"
FROM incident b

It work as above

"Didn't work" isn't a valid complaint; be specific, _what_ didn't work. What were you expecting. In the case of the first query, you're still anticipating rows in `MAXIMO.LONGDESCRIPTION`, just not necessarily values in column `LDTEXT`. In the second, the query works in the absence of rows. It looks like you want a `LEFT JOIN`, which will probably perform better. Oh, avoid quotations on column names (and other identifiers) - it makes them behave strangely. — Clockwork-Muse, Feb 12 '14 at 09:54

score 0 · Answer 1 · answered Feb 12 '14 at 08:14

0

To replace null values in column use COALESCE() function . For DB2 specific solution use IFNULL() function.

SELECT COALESCE(columnName,'Some Text')

OR

SELECT IFNULL(columnName,'Some Text')

answered Feb 12 '14 at 08:14

Mudassir Hasan

28,083
20
99
133

IFNULL only works on db2 for z/OS. Does not work on LUW – AngocA Feb 12 '14 at 08:37
`COALESE(..)` is the SQL Standard function anyways, can you give a reason why you're advocating a non-standard function? – Clockwork-Muse Feb 12 '14 at 09:46
Thanks Muse for your comments, I need to clear confusion here, need to show the symptom,cause and resolution in the report from incident management, where ever these are null columns or without data i need to place some text the above three columns replacing blank. – Roshan Jameer Feb 12 '14 at 10:06

How can I replacing empty ldtext column from longdescription table in maximo using sql

1 Answers1