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))
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)
answered 13 Feb '12, 05:23
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.
answered 13 Feb '12, 13:45