I am trying to find a way to conditionally format datatype a field in a materialized view. Yes it is odd, but trust the fact that we are extracting from other applications, and in one case, there are a series of custom fields that are all string, but can be formatted by the user in the original application for data input. Now our users are expecting to see the same format that they choose in their original application. (there are other technical reasons why we don't format this at the time of import into SQLAnywhere).

I have tried case statements, and even putting one field into two separate subselects, and then "pre"converting non-numeric field data into zeroes. Nothing has worked. I have tried everything I can to work around this, and it is either impossible, or there is a bug. I am only using this on a simple table with 3 records.

The one field has two nulls and one string value. I can convert the string value to a 0 in the subselects, but as soon as a I make a conditional statement, e.g. if the column should be string then pull subselect column a, if the column should be numeric, then pull subselect column b I get:

Cannot convert 'Christine' to a numeric SQLCODE=-157, ODBC 3 State="07006"

Here is a simple example of what I tried to do:

select 
 if (select DataTypeSQL from "41646d696e"."md_Custom_Field_Definition" where FieldName = 'CustomFieldEmployee2') = 'NUMERIC_2' then f."Employee Custom Field 02" else "Employee Custom Field 02b" endif as "xtest"
from
(select
      case when matt.C2 = 'NUMERIC_2' then if isnumeric(nn.cn) = 0 then 0 else nn.cn endif end as "Employee Custom Field 02",
      if matt.C2 = 'TEXT()' then nn.ct endif as "Employee Custom Field 02b",
            from(("41646d696e".qr_mployeeQuery as n
            left outer join(
select v.QQubeCompanyID, v.ListID,
v.CustomFieldEmployee2 as cn,
v.CustomFieldEmployee2 as ct
from "41646d696e".qr_EmployeeQuery as v) as nn
on (n.QQubeCompanyID = nn.QQubeCompanyID)
and (n.ListID = nn.ListID))

thank you.