The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.

asked 13 Feb '12, 04:00

cpv's gravatar image

cpv
915511
accept rate: 0%

edited 13 Feb '12, 06:01

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264


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)

permanent link

answered 13 Feb '12, 05:23

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

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.

(13 Feb '12, 11:41) cpv
Replies hidden
1

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

(13 Feb '12, 12:02) Volker Barth

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.

permanent link

answered 13 Feb '12, 13:45

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

1

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.

(13 Feb '12, 14:20) cpv
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 13 Feb '12, 04:00

question was seen: 2,727 times

last updated: 13 Feb '12, 14:20