case statement for teiid when objects to be compared are string

[ihrisi]

i have underlying data base as Oracle. Through the db i have configured the teiid data source. Now i want to execute a query like below

CASE WHEN column_vlaue = 'Flow Rate' THEN 'Product Flow Rate' ELSE column_value END

but its not able to compile the same providing the  exception:

Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException:

Teiid is converting the above query as

CASE WHEN g_0."column_vlaue" = 'Flow Rate' THEN 'Product Flow Rate' ELSE g_0."column_value" END.

Kindly suggest a way where i can implement the above for oracle with out having any error.

Thanks in advance

[shawkins]

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

[ihrisi]

Hi Steven,

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

SQLState:  50000

ErrorCode: 30504

[shawkins]

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?

[ihrisi]

Hi Steven,

below is the source code.

SELECT MP.R_MEASUREMENT_POINT_ID,

CASE WHEN MP.R_MEASUREMENT_POINT = 'Flow Rate' THEN 'Product Flow Rate' ELSE MP.R_MEASUREMENT_POINT END,

MP.QUANTITY_TYPE_ID, Q.QUANTITY_TYPE,

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.

[shawkins]

In the database what is the column type for R_MEASUREMENT_POINT?  varchar, nvarchar?

[ihrisi]

its of type nvarchar2

[shawkins]

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.

[ihrisi]

Hi ,

the work around for the issue is working fine. Thanks for that. Kindly suggest a permanent solution related to this.

Thanks in advance.

[shawkins]

A permanent solution will require a code change, can you log an issue: - JBoss Issue Tracker

[shawkins]

This was captured as [TEIID-5313] Oracle translator issue with mixing string types - JBoss Issue Tracker

[ihrisi]

Thanks for the assistance