I have had a look at the forum and have tried all the fixes for my this problem. However none of them have fixed it.
I have attached the failing EntityBroker SQL Query that is generated. And you will see that the CASE Stament C4 and Case Statement C7 all have TO_CHAR where it should be TO_NCHAR. When I change these and run this on the database the problem goes away.
Can you explain how it is tn translating these to CHAR when in the DB these are NVARCHAR2 columns?
SQL QUERY
SELECT CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN '1X'
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN '1X0X'
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN '1X1X'
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN '1X2X'
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN '1X3X'
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN '1X4X'
ELSE '1X5X'
END AS C1,
"Join1".ID AS ID,
"Join1".COLUMNNAME AS COLUMNNAME,
"Join1".DELETED AS DELETED,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_NUMBER(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN "Join1".C2
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_NUMBER(NULL)
END AS C2,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_BLOB(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN "Join1".C3
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_BLOB(NULL)
END AS C3,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_CHAR(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN "Join1".C4
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_CHAR(NULL)
END AS C4,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN "Join1".C5
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
END AS C5,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_NUMBER(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN "Join1".C6
END AS C6,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_CHAR(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_CHAR(NULL)
ELSE "Join1".C7
END AS C7
FROM CATALOG_ATTRIBUTES "Extent1"
INNER JOIN (SELECT "Extent2".ID AS ID,
"Extent2".DELETED AS DELETED,
"Extent2".COLUMNNAME AS COLUMNNAME,
"UnionAll5".C1 AS C1,
"UnionAll5".C2 AS C2,
"UnionAll5".C3 AS C3,
"UnionAll5".C4 AS C4,
"UnionAll5".C5 AS C5,
"UnionAll5".C6 AS C6,
"UnionAll5".C7 AS C7,
"UnionAll5".C8 AS C8,
"UnionAll5".C9 AS C9,
"UnionAll5".C10 AS C10,
"UnionAll5".C11 AS C11,
"UnionAll5".C12 AS C12,
"UnionAll5".C13 AS C13
FROM ATTRIBUTES "Extent2"
LEFT OUTER JOIN (SELECT "UnionAll4".C1 AS C1,
"UnionAll4".C2 AS C2,
"UnionAll4".C3 AS C3,
"UnionAll4".C4 AS C4,
"UnionAll4".C5 AS C5,
"UnionAll4".C6 AS C6,
"UnionAll4".C7 AS C7,
"UnionAll4".C8 AS C8,
"UnionAll4".C9 AS C9,
"UnionAll4".C10 AS C10,
"UnionAll4".C11 AS C11,
"UnionAll4".C12 AS C12,
"UnionAll4".C13 AS C13
FROM (SELECT "UnionAll3".C1 AS C1,
"UnionAll3".C2 AS C2,
"UnionAll3".C3 AS C3,
"UnionAll3".C4 AS C4,
"UnionAll3".C5 AS C5,
"UnionAll3".C6 AS C6,
"UnionAll3".C7 AS C7,
"UnionAll3".C8 AS C8,
"UnionAll3".C9 AS C9,
"UnionAll3".C10 AS C10,
"UnionAll3".C11 AS C11,
"UnionAll3".C12 AS C12,
"UnionAll3".C13 AS C13
FROM (SELECT "UnionAll2".C1 AS C1,
"UnionAll2".C2 AS C2,
"UnionAll2".C3 AS C3,
"UnionAll2".C4 AS C4,
"UnionAll2".C5 AS C5,
"UnionAll2".C6 AS C6,
"UnionAll2".C7 AS C7,
"UnionAll2".C8 AS C8,
"UnionAll2".C9 AS C9,
"UnionAll2".C10 AS C10,
"UnionAll2".C11 AS C11,
"UnionAll2".C12 AS C12,
"UnionAll2".C13 AS C13
FROM (SELECT "UnionAll1".ATTRIBUTEID AS C1,
"UnionAll1".C1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".C3 AS C4,
"UnionAll1".C4 AS C5,
"UnionAll1".C5 AS C6,
"UnionAll1".VALUE AS C7,
"UnionAll1".C6 AS C8,
"UnionAll1".C7 AS C9,
"UnionAll1".C8 AS C10,
"UnionAll1".C9 AS C11,
"UnionAll1".C10 AS C12,
"UnionAll1".C11 AS C13
FROM (SELECT "Extent3".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_NCHAR(NULL) AS C3,
CAST(NULL AS TIMESTAMP(9)) AS C4,
TO_NUMBER(NULL) AS C5,
"Extent3".VALUE AS VALUE,
1 AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM STRING_ATTRIBUTE "Extent3"
UNION ALL
SELECT "Extent4".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_NCHAR(NULL) AS C3,
CAST(NULL AS TIMESTAMP(9)) AS C4,
TO_NUMBER("Extent4".VALUE) AS C5,
TO_NCHAR(NULL) AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11,
1 AS C12
FROM INTEGER_ATTRIBUTE "Extent4") "UnionAll1"
UNION ALL
SELECT "Extent5".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_NCHAR(NULL) AS C3,
"Extent5".VALUE AS VALUE,
TO_NUMBER(NULL) AS C4,
TO_NCHAR(NULL) AS C5,
0 AS C6,
1 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM DATETIME_ATTRIBUTE "Extent5") "UnionAll2"
UNION ALL
SELECT "Extent6".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
"Extent6".VALUE AS VALUE,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NCHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
1 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM CLOB_ATTRIBUTE "Extent6") "UnionAll3"
UNION ALL
SELECT "Extent7".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
"Extent7".VALUE AS VALUE,
TO_NCHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NCHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
0 AS C8,
1 AS C9,
0 AS C10,
0 AS C11
FROM BLOB_ATTRIBUTE "Extent7") "UnionAll4"
UNION ALL
SELECT "Extent8".ATTRIBUTEID AS ATTRIBUTEID,
"Extent8".VALUE AS VALUE,
TO_BLOB(NULL) AS C1,
TO_NCHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NCHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
1 AS C10,
0 AS C11
FROM FLOAT_ATTRIBUTE "Extent8") "UnionAll5"
ON "Extent2".ID = "UnionAll5".C1) "Join1"
ON "Extent1".ATTRIBUTEID = "Join1".ID
WHERE "Extent1".CATALOGID = '23efd9a6-6728-fb94-e040-0c0a650f142e' /* @EntityKeyValue1 */
I have checked all the fields in the entity model and they are all set to false for Unicode.
Any help you can offer is very much appreciated.
Oracle 12704 Type Mismatch
-
- Posts: 3
- Joined: Wed 29 Jun 2011 17:26
OK I have done some more work on our data model and it does seem that there is a bug somewhere in the way that it is translated.
I have copied another select statement that generates this error. And you will see that the CASE STATEMENT C4 has _TO_CHAR. When the conversion of the SELECT UNION Section has conversions TO_NCHAR.
When we change the Entity Model and Database back to VARCHAR2 the problem goes away.
Can you advise as we can't have VARCHAR2 as the data type as this will be used in an international product.
SELECT STATEMENT
SELECT CASE
WHEN "UnionAll3".C6 = 1 THEN '0X0X'
WHEN "UnionAll3".C7 = 1 THEN '0X1X'
WHEN "UnionAll3".C8 = 1 THEN '0X2X'
ELSE '0X3X'
END AS C1,
"UnionAll3".C1 AS C2,
"Extent5".NAME AS NAME,
"Extent5".CONTAINER_ID AS CONTAINER_ID,
"Extent5".TYPE_ID AS TYPE_ID,
"Extent5".INVENTORY_ID AS INVENTORY_ID,
"Extent5".TEMPLATE_ID AS TEMPLATE_ID,
CASE
WHEN "UnionAll3".C6 = 1 THEN "UnionAll3".C2
WHEN "UnionAll3".C7 = 1 THEN TO_NUMBER(NULL)
WHEN "UnionAll3".C8 = 1 THEN TO_NUMBER(NULL)
END AS C3,
CASE
WHEN "UnionAll3".C6 = 1 THEN TO_CHAR(NULL)
WHEN "UnionAll3".C7 = 1 THEN "UnionAll3".C3
WHEN "UnionAll3".C8 = 1 THEN TO_CHAR(NULL)
END AS C4,
CASE
WHEN "UnionAll3".C6 = 1 THEN CAST(NULL AS TIMESTAMP(9))
WHEN "UnionAll3".C7 = 1 THEN CAST(NULL AS TIMESTAMP(9))
WHEN "UnionAll3".C8 = 1 THEN "UnionAll3".C4
END AS C5,
CASE
WHEN "UnionAll3".C6 = 1 THEN TO_NUMBER(NULL)
WHEN "UnionAll3".C7 = 1 THEN TO_NUMBER(NULL)
WHEN "UnionAll3".C8 = 1 THEN TO_NUMBER(NULL)
ELSE "UnionAll3".C5
END AS C6
FROM (SELECT "UnionAll2".C1 AS C1,
"UnionAll2".C2 AS C2,
"UnionAll2".C3 AS C3,
"UnionAll2".C4 AS C4,
"UnionAll2".C5 AS C5,
"UnionAll2".C6 AS C6,
"UnionAll2".C7 AS C7,
"UnionAll2".C8 AS C8
FROM (SELECT "UnionAll1".ID AS C1,
"UnionAll1".C1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".C3 AS C4,
"UnionAll1".VALUE AS C5,
"UnionAll1".C4 AS C6,
"UnionAll1".C5 AS C7,
"UnionAll1".C6 AS C8
FROM (SELECT "Extent1".ID AS ID,
TO_NUMBER(NULL) AS C1,
TO_NCHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
"Extent1".VALUE AS VALUE,
0 AS C4,
0 AS C5,
0 AS C6
FROM CIA_FLOAT_VALUE "Extent1"
UNION ALL
SELECT "Extent2".ID AS ID,
TO_NUMBER(NULL) AS C1,
TO_NCHAR(NULL) AS C2,
"Extent2".VALUE AS VALUE,
TO_NUMBER(NULL) AS C3,
0 AS C4,
0 AS C5,
1 AS C6
FROM CIA_DATETIME_VALUE "Extent2") "UnionAll1"
UNION ALL
SELECT "Extent3".ID AS ID,
TO_NUMBER("Extent3".VALUE) AS C1,
TO_NCHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
1 AS C5,
0 AS C6,
0 AS C7
FROM CIA_INTEGER_VALUE "Extent3") "UnionAll2"
UNION ALL
SELECT "Extent4".ID AS ID,
TO_NUMBER(NULL) AS C1,
"Extent4".VALUE AS VALUE,
CAST(NULL AS TIMESTAMP(9)) AS C2,
TO_NUMBER(NULL) AS C3,
0 AS C4,
1 AS C5,
0 AS C6
FROM CIA_STRING_VALUE "Extent4") "UnionAll3"
INNER JOIN CONTAINER_INVENTORY_ATTRIBUTE "Extent5"
ON "UnionAll3".C1 = "Extent5".ID WHERE "Extent5".TYPE_ID = '4a5ee9a6-a482-941c-e040-0c0a650f1f75' /* @EntityKeyValue1 */
Regards
Alixx
I have copied another select statement that generates this error. And you will see that the CASE STATEMENT C4 has _TO_CHAR. When the conversion of the SELECT UNION Section has conversions TO_NCHAR.
When we change the Entity Model and Database back to VARCHAR2 the problem goes away.
Can you advise as we can't have VARCHAR2 as the data type as this will be used in an international product.
SELECT STATEMENT
SELECT CASE
WHEN "UnionAll3".C6 = 1 THEN '0X0X'
WHEN "UnionAll3".C7 = 1 THEN '0X1X'
WHEN "UnionAll3".C8 = 1 THEN '0X2X'
ELSE '0X3X'
END AS C1,
"UnionAll3".C1 AS C2,
"Extent5".NAME AS NAME,
"Extent5".CONTAINER_ID AS CONTAINER_ID,
"Extent5".TYPE_ID AS TYPE_ID,
"Extent5".INVENTORY_ID AS INVENTORY_ID,
"Extent5".TEMPLATE_ID AS TEMPLATE_ID,
CASE
WHEN "UnionAll3".C6 = 1 THEN "UnionAll3".C2
WHEN "UnionAll3".C7 = 1 THEN TO_NUMBER(NULL)
WHEN "UnionAll3".C8 = 1 THEN TO_NUMBER(NULL)
END AS C3,
CASE
WHEN "UnionAll3".C6 = 1 THEN TO_CHAR(NULL)
WHEN "UnionAll3".C7 = 1 THEN "UnionAll3".C3
WHEN "UnionAll3".C8 = 1 THEN TO_CHAR(NULL)
END AS C4,
CASE
WHEN "UnionAll3".C6 = 1 THEN CAST(NULL AS TIMESTAMP(9))
WHEN "UnionAll3".C7 = 1 THEN CAST(NULL AS TIMESTAMP(9))
WHEN "UnionAll3".C8 = 1 THEN "UnionAll3".C4
END AS C5,
CASE
WHEN "UnionAll3".C6 = 1 THEN TO_NUMBER(NULL)
WHEN "UnionAll3".C7 = 1 THEN TO_NUMBER(NULL)
WHEN "UnionAll3".C8 = 1 THEN TO_NUMBER(NULL)
ELSE "UnionAll3".C5
END AS C6
FROM (SELECT "UnionAll2".C1 AS C1,
"UnionAll2".C2 AS C2,
"UnionAll2".C3 AS C3,
"UnionAll2".C4 AS C4,
"UnionAll2".C5 AS C5,
"UnionAll2".C6 AS C6,
"UnionAll2".C7 AS C7,
"UnionAll2".C8 AS C8
FROM (SELECT "UnionAll1".ID AS C1,
"UnionAll1".C1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".C3 AS C4,
"UnionAll1".VALUE AS C5,
"UnionAll1".C4 AS C6,
"UnionAll1".C5 AS C7,
"UnionAll1".C6 AS C8
FROM (SELECT "Extent1".ID AS ID,
TO_NUMBER(NULL) AS C1,
TO_NCHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
"Extent1".VALUE AS VALUE,
0 AS C4,
0 AS C5,
0 AS C6
FROM CIA_FLOAT_VALUE "Extent1"
UNION ALL
SELECT "Extent2".ID AS ID,
TO_NUMBER(NULL) AS C1,
TO_NCHAR(NULL) AS C2,
"Extent2".VALUE AS VALUE,
TO_NUMBER(NULL) AS C3,
0 AS C4,
0 AS C5,
1 AS C6
FROM CIA_DATETIME_VALUE "Extent2") "UnionAll1"
UNION ALL
SELECT "Extent3".ID AS ID,
TO_NUMBER("Extent3".VALUE) AS C1,
TO_NCHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
1 AS C5,
0 AS C6,
0 AS C7
FROM CIA_INTEGER_VALUE "Extent3") "UnionAll2"
UNION ALL
SELECT "Extent4".ID AS ID,
TO_NUMBER(NULL) AS C1,
"Extent4".VALUE AS VALUE,
CAST(NULL AS TIMESTAMP(9)) AS C2,
TO_NUMBER(NULL) AS C3,
0 AS C4,
1 AS C5,
0 AS C6
FROM CIA_STRING_VALUE "Extent4") "UnionAll3"
INNER JOIN CONTAINER_INVENTORY_ATTRIBUTE "Extent5"
ON "UnionAll3".C1 = "Extent5".ID WHERE "Extent5".TYPE_ID = '4a5ee9a6-a482-941c-e040-0c0a650f1f75' /* @EntityKeyValue1 */
Regards
Alixx
-
- Posts: 3
- Joined: Wed 29 Jun 2011 17:26
And now after further playing around this is the original query with the EDMX haveing been changed to use VARCHAR2 rather than NVARCHAR2
You will notice that the SELECT UNION section TO_CHAR RATHER than TO_NCHAR so the top CASE sections match the SELECT UNION
SQL STATEMENT
SELECT CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN '1X'
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN '1X0X'
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN '1X1X'
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN '1X2X'
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN '1X3X'
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN '1X4X'
ELSE '1X5X'
END AS C1,
"Join1".ID AS ID,
"Join1".COLUMNNAME AS COLUMNNAME,
"Join1".DELETED AS DELETED,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_NUMBER(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN "Join1".C2
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_NUMBER(NULL)
END AS C2,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_BLOB(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN "Join1".C3
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_BLOB(NULL)
END AS C3,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_CHAR(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN "Join1".C4
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_CHAR(NULL)
END AS C4,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN "Join1".C5
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
END AS C5,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_NUMBER(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN "Join1".C6
END AS C6,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_CHAR(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_CHAR(NULL)
ELSE "Join1".C7
END AS C7
FROM CATALOG_ATTRIBUTES "Extent1"
INNER JOIN (SELECT "Extent2".ID AS ID,
"Extent2".DELETED AS DELETED,
"Extent2".COLUMNNAME AS COLUMNNAME,
"UnionAll5".C1 AS C1,
"UnionAll5".C2 AS C2,
"UnionAll5".C3 AS C3,
"UnionAll5".C4 AS C4,
"UnionAll5".C5 AS C5,
"UnionAll5".C6 AS C6,
"UnionAll5".C7 AS C7,
"UnionAll5".C8 AS C8,
"UnionAll5".C9 AS C9,
"UnionAll5".C10 AS C10,
"UnionAll5".C11 AS C11,
"UnionAll5".C12 AS C12,
"UnionAll5".C13 AS C13
FROM ATTRIBUTES "Extent2"
LEFT OUTER JOIN (SELECT "UnionAll4".C1 AS C1,
"UnionAll4".C2 AS C2,
"UnionAll4".C3 AS C3,
"UnionAll4".C4 AS C4,
"UnionAll4".C5 AS C5,
"UnionAll4".C6 AS C6,
"UnionAll4".C7 AS C7,
"UnionAll4".C8 AS C8,
"UnionAll4".C9 AS C9,
"UnionAll4".C10 AS C10,
"UnionAll4".C11 AS C11,
"UnionAll4".C12 AS C12,
"UnionAll4".C13 AS C13
FROM (SELECT "UnionAll3".C1 AS C1,
"UnionAll3".C2 AS C2,
"UnionAll3".C3 AS C3,
"UnionAll3".C4 AS C4,
"UnionAll3".C5 AS C5,
"UnionAll3".C6 AS C6,
"UnionAll3".C7 AS C7,
"UnionAll3".C8 AS C8,
"UnionAll3".C9 AS C9,
"UnionAll3".C10 AS C10,
"UnionAll3".C11 AS C11,
"UnionAll3".C12 AS C12,
"UnionAll3".C13 AS C13
FROM (SELECT "UnionAll2".C1 AS C1,
"UnionAll2".C2 AS C2,
"UnionAll2".C3 AS C3,
"UnionAll2".C4 AS C4,
"UnionAll2".C5 AS C5,
"UnionAll2".C6 AS C6,
"UnionAll2".C7 AS C7,
"UnionAll2".C8 AS C8,
"UnionAll2".C9 AS C9,
"UnionAll2".C10 AS C10,
"UnionAll2".C11 AS C11,
"UnionAll2".C12 AS C12,
"UnionAll2".C13 AS C13
FROM (SELECT "UnionAll1".ATTRIBUTEID AS C1,
"UnionAll1".C1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".C3 AS C4,
"UnionAll1".C4 AS C5,
"UnionAll1".C5 AS C6,
"UnionAll1".VALUE AS C7,
"UnionAll1".C6 AS C8,
"UnionAll1".C7 AS C9,
"UnionAll1".C8 AS C10,
"UnionAll1".C9 AS C11,
"UnionAll1".C10 AS C12,
"UnionAll1".C11 AS C13
FROM (SELECT "Extent3".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_CHAR(NULL) AS C3,
CAST(NULL AS TIMESTAMP(9)) AS C4,
TO_NUMBER(NULL) AS C5,
"Extent3".VALUE AS VALUE,
1 AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM STRING_ATTRIBUTE "Extent3"
UNION ALL
SELECT "Extent4".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_CHAR(NULL) AS C3,
CAST(NULL AS TIMESTAMP(9)) AS C4,
TO_NUMBER("Extent4".VALUE) AS C5,
TO_CHAR(NULL) AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11,
1 AS C12
FROM INTEGER_ATTRIBUTE "Extent4") "UnionAll1"
UNION ALL
SELECT "Extent5".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_CHAR(NULL) AS C3,
"Extent5".VALUE AS VALUE,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
0 AS C6,
1 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM DATETIME_ATTRIBUTE "Extent5") "UnionAll2"
UNION ALL
SELECT "Extent6".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
"Extent6".VALUE AS VALUE,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
1 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM CLOB_ATTRIBUTE "Extent6") "UnionAll3"
UNION ALL
SELECT "Extent7".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
"Extent7".VALUE AS VALUE,
TO_CHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
0 AS C8,
1 AS C9,
0 AS C10,
0 AS C11
FROM BLOB_ATTRIBUTE "Extent7") "UnionAll4"
UNION ALL
SELECT "Extent8".ATTRIBUTEID AS ATTRIBUTEID,
"Extent8".VALUE AS VALUE,
TO_BLOB(NULL) AS C1,
TO_CHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
1 AS C10,
0 AS C11
FROM FLOAT_ATTRIBUTE "Extent8") "UnionAll5"
ON "Extent2".ID = "UnionAll5".C1) "Join1"
ON "Extent1".ATTRIBUTEID = "Join1".ID
WHERE "Extent1".CATALOGID = '23efd9a6-6728-fb94-e040-0c0a650f142e' /* @EntityKeyValue1 */
You will notice that the SELECT UNION section TO_CHAR RATHER than TO_NCHAR so the top CASE sections match the SELECT UNION
SQL STATEMENT
SELECT CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN '1X'
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN '1X0X'
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN '1X1X'
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN '1X2X'
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN '1X3X'
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN '1X4X'
ELSE '1X5X'
END AS C1,
"Join1".ID AS ID,
"Join1".COLUMNNAME AS COLUMNNAME,
"Join1".DELETED AS DELETED,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_NUMBER(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN "Join1".C2
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_NUMBER(NULL)
END AS C2,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_BLOB(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN "Join1".C3
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_BLOB(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_BLOB(NULL)
END AS C3,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_CHAR(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN "Join1".C4
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_CHAR(NULL)
END AS C4,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN "Join1".C5
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN CAST(NULL AS TIMESTAMP(9))
END AS C5,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_NUMBER(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_NUMBER(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN "Join1".C6
END AS C6,
CASE
WHEN (((((NOT (("Join1".C8 = 1)
AND ("Join1".C8 IS NOT NULL)))
AND (NOT (("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL))))
AND (NOT (("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL))))
AND (NOT (("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL))))
AND (NOT (("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL))))
AND (NOT (("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL))) THEN TO_CHAR(NULL)
WHEN ("Join1".C12 = 1)
AND ("Join1".C12 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C11 = 1)
AND ("Join1".C11 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C10 = 1)
AND ("Join1".C10 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C9 = 1)
AND ("Join1".C9 IS NOT NULL) THEN TO_CHAR(NULL)
WHEN ("Join1".C13 = 1)
AND ("Join1".C13 IS NOT NULL) THEN TO_CHAR(NULL)
ELSE "Join1".C7
END AS C7
FROM CATALOG_ATTRIBUTES "Extent1"
INNER JOIN (SELECT "Extent2".ID AS ID,
"Extent2".DELETED AS DELETED,
"Extent2".COLUMNNAME AS COLUMNNAME,
"UnionAll5".C1 AS C1,
"UnionAll5".C2 AS C2,
"UnionAll5".C3 AS C3,
"UnionAll5".C4 AS C4,
"UnionAll5".C5 AS C5,
"UnionAll5".C6 AS C6,
"UnionAll5".C7 AS C7,
"UnionAll5".C8 AS C8,
"UnionAll5".C9 AS C9,
"UnionAll5".C10 AS C10,
"UnionAll5".C11 AS C11,
"UnionAll5".C12 AS C12,
"UnionAll5".C13 AS C13
FROM ATTRIBUTES "Extent2"
LEFT OUTER JOIN (SELECT "UnionAll4".C1 AS C1,
"UnionAll4".C2 AS C2,
"UnionAll4".C3 AS C3,
"UnionAll4".C4 AS C4,
"UnionAll4".C5 AS C5,
"UnionAll4".C6 AS C6,
"UnionAll4".C7 AS C7,
"UnionAll4".C8 AS C8,
"UnionAll4".C9 AS C9,
"UnionAll4".C10 AS C10,
"UnionAll4".C11 AS C11,
"UnionAll4".C12 AS C12,
"UnionAll4".C13 AS C13
FROM (SELECT "UnionAll3".C1 AS C1,
"UnionAll3".C2 AS C2,
"UnionAll3".C3 AS C3,
"UnionAll3".C4 AS C4,
"UnionAll3".C5 AS C5,
"UnionAll3".C6 AS C6,
"UnionAll3".C7 AS C7,
"UnionAll3".C8 AS C8,
"UnionAll3".C9 AS C9,
"UnionAll3".C10 AS C10,
"UnionAll3".C11 AS C11,
"UnionAll3".C12 AS C12,
"UnionAll3".C13 AS C13
FROM (SELECT "UnionAll2".C1 AS C1,
"UnionAll2".C2 AS C2,
"UnionAll2".C3 AS C3,
"UnionAll2".C4 AS C4,
"UnionAll2".C5 AS C5,
"UnionAll2".C6 AS C6,
"UnionAll2".C7 AS C7,
"UnionAll2".C8 AS C8,
"UnionAll2".C9 AS C9,
"UnionAll2".C10 AS C10,
"UnionAll2".C11 AS C11,
"UnionAll2".C12 AS C12,
"UnionAll2".C13 AS C13
FROM (SELECT "UnionAll1".ATTRIBUTEID AS C1,
"UnionAll1".C1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".C3 AS C4,
"UnionAll1".C4 AS C5,
"UnionAll1".C5 AS C6,
"UnionAll1".VALUE AS C7,
"UnionAll1".C6 AS C8,
"UnionAll1".C7 AS C9,
"UnionAll1".C8 AS C10,
"UnionAll1".C9 AS C11,
"UnionAll1".C10 AS C12,
"UnionAll1".C11 AS C13
FROM (SELECT "Extent3".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_CHAR(NULL) AS C3,
CAST(NULL AS TIMESTAMP(9)) AS C4,
TO_NUMBER(NULL) AS C5,
"Extent3".VALUE AS VALUE,
1 AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM STRING_ATTRIBUTE "Extent3"
UNION ALL
SELECT "Extent4".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_CHAR(NULL) AS C3,
CAST(NULL AS TIMESTAMP(9)) AS C4,
TO_NUMBER("Extent4".VALUE) AS C5,
TO_CHAR(NULL) AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11,
1 AS C12
FROM INTEGER_ATTRIBUTE "Extent4") "UnionAll1"
UNION ALL
SELECT "Extent5".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
TO_CHAR(NULL) AS C3,
"Extent5".VALUE AS VALUE,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
0 AS C6,
1 AS C7,
0 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM DATETIME_ATTRIBUTE "Extent5") "UnionAll2"
UNION ALL
SELECT "Extent6".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
TO_BLOB(NULL) AS C2,
"Extent6".VALUE AS VALUE,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
1 AS C8,
0 AS C9,
0 AS C10,
0 AS C11
FROM CLOB_ATTRIBUTE "Extent6") "UnionAll3"
UNION ALL
SELECT "Extent7".ATTRIBUTEID AS ATTRIBUTEID,
TO_NUMBER(NULL) AS C1,
"Extent7".VALUE AS VALUE,
TO_CHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
0 AS C8,
1 AS C9,
0 AS C10,
0 AS C11
FROM BLOB_ATTRIBUTE "Extent7") "UnionAll4"
UNION ALL
SELECT "Extent8".ATTRIBUTEID AS ATTRIBUTEID,
"Extent8".VALUE AS VALUE,
TO_BLOB(NULL) AS C1,
TO_CHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
0 AS C6,
0 AS C7,
0 AS C8,
0 AS C9,
1 AS C10,
0 AS C11
FROM FLOAT_ATTRIBUTE "Extent8") "UnionAll5"
ON "Extent2".ID = "UnionAll5".C1) "Join1"
ON "Extent1".ATTRIBUTEID = "Join1".ID
WHERE "Extent1".CATALOGID = '23efd9a6-6728-fb94-e040-0c0a650f142e' /* @EntityKeyValue1 */