cannot convert to a numeric

[Former Member]

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.

[MCMartin]

I think you mean, that your select shall return numbers and varchars mixed for column xtest. A list like:

1
2
Christine
3

But how shall SQLA decide which data type to use for xtest? In my opinion you should try to represent all data as strings, like cast(nn.cn as varchar)

[Former Member]

Currently that is what we have, however we have to make it easier for users. e.g. they have to convert the string to number to use it say in an Excel pivot table, etc. Here is the kicker: if the user - in the original application - changes the format mask from say text to number, the original application doesn't remove the text based entries (because it is stored as text underneath the hood). So, we have to do that.

Another way to put this: I know that I can't format text to a number if isnumeric = 0. So I was attempting to create a temporary "container" in which I would change the offending text value to 0. And then I was attempting to say - outside of the container/subselect - to give me THAT container if we want it formatted as number, or to choose another container / leave it alone if we want it formatted as text.

[VolkerBarth]

I do not really understand your requirements - nevertheless the following FAQ may help how to return "potential numbers" without getting conversion errors.