It is not clear what is wrong from what you have shown. Can you provide more of the details of the exception, or the full stacktrace - is there a message, is there an underlying oracle exception and vendor code, etc.?
i tried to run the CASE statement with a format like above. The main logic i want to implement is, when the column value is 'Flow rate ' then only i should replace the flow rate with 'Process flow rate' else i should have the value of the column as it is as default value.
But when m trying to run the case statement in above format it is giving me below error.
Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PPDMSrc: 12704 TEIID11008:TEIID11004 Error executing statement(s):
The error code looks like ORA-12704 character set mismatch
What is the source type for column_value and are you using this as a projected value in a union query? If so what is the source type of the projected value from the other union branches?
below is the source code.
CASE WHEN MP.R_MEASUREMENT_POINT = 'Flow Rate' THEN 'Product Flow Rate' ELSE MP.R_MEASUREMENT_POINT END,
MP.SHORT_NAME, MP.LONG_NAME, MP.REMARK, MP.ROW_CREATED_DATE, MP.ROW_CREATED_BY,
MP.ROW_CHANGED_DATE, MP.ROW_CHANGED_BY, MP.ACTIVE_IND, RS.SOURCE_NAME
FROM PPDMSrc.R_MEASUREMENT_POINT MP
INNER JOIN PPDMSrc.PPDM_QUANTITY Q ON MP.QUANTITY_TYPE_ID = Q.QUANTITY_TYPE_ID
LEFT OUTER JOIN PPDMSrc.R_SOURCE RS ON MP.SOURCE = RS.SOURCE_ID
WHERE MP.R_MEASUREMENT_POINT IN ('A Annulus Pressure', 'B Annulus Pressure',
'C Annulus Pressure', 'D Annulus Pressure', 'Wellhead Temperature', 'Tubing Pressure',
'Flow Rate') AND MP.ACTIVE_IND = 'Y';
where the PPDMSrc is the Oracle database.
In the database what is the column type for R_MEASUREMENT_POINT? varchar, nvarchar?
its of type nvarchar2
So that is the root issue. From that error code it implies that Oracle requires type to match - can you directly issue the source query against Oracle, but add the N literal prefix - ... MP.R_MEASUREMENT_POINT = 'Flow Rate' THEN N'Product Flow Rate' ...
A workaround for this particular case could be to use concat instead:
CASE WHEN MP.R_MEASUREMENT_POINT = 'Flow Rate' THEN 'Product ' || MP.R_MEASUREMENT_POINT ELSE MP.R_MEASUREMENT_POINT END
I'll double check this behavior locally. An issue is likely needed to address this generally.
the work around for the issue is working fine. Thanks for that. Kindly suggest a permanent solution related to this.
Thanks in advance.
Thanks for the assistance