If you have a table with a varchar column containing different kinds of values (int, date, string, float) all converted to a string and you then want to join the int value to a different table. I'm getting errors like "Cannot convert "some date value" to a numeric".

Setup the tables:

CREATE TABLE "DBA"."TestData" (
    "FieldName" VARCHAR(10) NULL,
    "TestData" VARCHAR(10) NULL
) IN "system";

INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('Id','1');
INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('OtherId','1');
INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('Date','2015-01-01');
INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('Text','Bla');
INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('Float','1.15')

CREATE TABLE "DBA"."JoinId" (
    "Id" INTEGER NULL
) IN "system";

INSERT INTO "DBA"."JoinId" ("Id") VALUES(1);
INSERT INTO "DBA"."JoinId" ("Id") VALUES(2);

CREATE FUNCTION "DBA"."IsIntegerValue1"(string VarChar(10))
returns bit
begin
  declare isInt bit;

  if string = '1' then 
    set isInt = 1
  else
    set isInt = 0
  endif;

  return isInt;
end;

The result I am looking for:

FieldName | TestData   | Id
-----------------------------
Id        | 1          | 1
OtherId   | 1          | NULL
Date      | 2015-01-01 | NULL
Text      | Bla        | NULL
Float     | 1.15       | NULL

The queries:

SQL 1:

select *
  from TestData
       left outer join JoinId
                    on JoinId.Id = if TestData.Fieldname = 'Id' then TestData.TestData else null endif

SQL 2:

select *
  from TestData
       left outer join JoinId
                    on TestData.Fieldname = 'Id'
                   and JoinId.Id = if IsNumeric(TestData.TestData) = 1 then TestData.TestData else null endif

SQL 3:

select *
  from TestData
       left outer join JoinId
                    on TestData.Fieldname = 'Id'
                   and JoinId.Id = if IsNumeric(TestData.TestData) = 1 then TestData.TestData else null endif

SQL 4:

select *
  from TestData
       left outer join JoinId
                    on TestData.Fieldname = 'Id'
                   and Id = if IsIntegerValue1(TestData) = 1 then TestData else null endif

Result 1, 2, 3 and 4:

Cursor not in a valid state
SQLCODE=-853, ODBC 3 State="24000"

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

SQL 5:

select *
  from TestData
       left outer join JoinId
                    on TestData.Fieldname = 'Id'
                   and JoinId.Id = if IsNumeric(TestData.TestData) = 1 and IsDate(TestData.TestData) = 0 then TestData.TestData else null endif

Result 5: This doesn't crash but it doesn't give the result I am looking for (as expected because IsDate('1') returns true)

My questions:

• Why are SQL 1, 2, 3 and 4 crashing and not SQL 5?
• How can I get the desired result?

Extra note: In the actual case I'm using this for I could really use the available PK/FK indexes for performance reasons and I would rather not create additional indexes like "cast(JoinId.Id as varchar)" as suggested in Volkers answer.

Tested with ASA 16.0.0.2283