Question Re: cannot convert to a numeric in Technology Q&A

cannot convert to a numeric

Former Member — Mon, 13 Feb 2012 04:00:34 GMT

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.

Re: cannot convert to a numeric

MCMartin — Mon, 13 Feb 2012 05:23:49 GMT

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)

Re: cannot convert to a numeric

Former Member — Mon, 13 Feb 2012 11:41:07 GMT

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.

Re: cannot convert to a numeric

VolkerBarth — Mon, 13 Feb 2012 12:02:24 GMT

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

Re: cannot convert to a numeric

Former Member — Mon, 13 Feb 2012 13:45:52 GMT

What you are trying to do is skirt one of the fundamental principles of the relational data model, namely that of DOMAINs. SQL Anywhere is (very) forgiving about on-the-fly dynamic type conversion, much more so than other systems, but doing what you are trying to do is still going to cause considerable confusion, grief, and frustration because SQL is not a programming language - it is a query language based on 1st order predicate logic, and the order of operations to compute a query result is not guaranteed. As a user, you wouldn't want it any other way - because reordering the computation to do it efficiently is precisely what a query optimizer is for.

I have answered similar questions in the past. Look here for one example.

Re: cannot convert to a numeric

Former Member — Mon, 13 Feb 2012 14:20:49 GMT

I appreciate the answer. As I have thought thru this process and looked at related issues, it is becoming clearer to me. I think my stubbornness got in the way of clear thinking. We are going to use a different route to achieve the end result. Thank everybody for their responses.