I am trying to create a new column which can show the “Date Created” from the history table left joined with current table from the date column “TIMESTAMP” I use Oracle Interactive Reporting (IR) SQL querying tool to pull the data from the database DB2 .History table column is DB2.TABLEH.TIMESTAMP B and the Current table is DB2.TIMESTAMP A
I created a simple coalesce query like below which runs fine
COALESCE(Min ( B.TIMESTAMP), A.TIMESTAMP)
But I would like to have the result to say from which argument was fulfilled by having a parentheses if the second argument returned the result. I tried with concatenate function but getting the error as below. Here is the full SQL
SELECT A.ID,COALESCE(Min ( B.TIMESTAMP), '('CONCAT CAST(A.TIMESTAMP AS VARCHAR(10) ) CONCAT')') AS DATE_CREATED
FROM DB2.TABLE A
LEFT OUTER JOIN DB2.TABLEH B ON A.ID=B.ID AND A.COUNTRY= B.COUNTRY
WHERE A.COUNTRY='XXXXXXX'
GROUP BY A.ID, A.TIMESTAMP
Please note I cannot have this in another column with a CASE WHEN statement but need to show the results with parentheses ONLY if the 2nd argument is fulfilled within the same result.
Below is the error I get when I run above SQL
“BIC00004. DAL010059. An error occurred while accessing the database:
THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE. SQLCODE=-401, SQLSTATE=42818, DRIVER=3.72.44
Extended error description is:
The data types of the operands for the operation “{0}” are not compatible.”
I would like the result to show like this. If the result is from 2nd argument from the current table.it should be in a parenthesis as shown below.
| Id | Date Created |
|---|---|
| 001 | 08-01-2004 |
| 002 | (05-02-2000) |
Output Result
1
The types of the arguments of CONCAT need to be the same (or at least similar so a cast can be implied). If you want to put it in parens, you have to use a string type for both arguments. E.g. also CAST the first argument to VARCHAR.
COALESCE(CAST(Min ( B.TIMESTAMP) AS VARCHAR(10)), '('CONCAT CAST(A.TIMESTAMP AS VARCHAR(10) ) CONCAT')')
2
I was able to resolve this and also change the date format the same as DB2 with a small tweak in the SQL.
The full SQL code is:
SELECT A.id,
Coalesce(To_char(Min(B.TIMESTAMP), 'mm/dd/yyyy'),
'('CONCAT To_char(A.TIMESTAMP,
'mm/dd/yyyy') CONCAT')') AS DATE_CREATED
FROM db2.table A
LEFT OUTER JOIN db2.tableh B
ON A.id = B.id
AND A.country = B.country
WHERE A.country = 'XXXXXXX'
GROUP BY A.id,
A.TIMESTAMP