--- --- --- --- ---
SELECT "dbTmp5".COLUMN_DDL
FROM (
SELECT "dbTmp1".*
FROM (
SELECT
"COLNO",
(CASE WHEN ("TYPENAME"='VARCHAR' OR "TYPENAME"='CHARACTER' OR "TYPENAME"='DECIMAL') THEN
(CASE WHEN "TYPENAME"='VARCHAR' THEN (
' ' || "COLNAME" || ' ' || "TYPENAME" || '(' || "LENGTH" || ')' || ' ' ||
(CASE WHEN "NULLS" = 'N' THEN 'NOT NULL' ELSE 'NULL' END) ||
(CASE WHEN "DEFAULT" IS NOT NULL AND "DEFAULT" != 'NULL' THEN ' ' ELSE '' END) ||
(CASE WHEN "DEFAULT" IS NOT NULL AND "DEFAULT" != 'NULL' THEN 'DEFAULT ' || "DEFAULT" ELSE '' END)
|| ','
)
WHEN "TYPENAME"='CHARACTER' THEN (
' ' || "COLNAME" || ' ' || "TYPENAME" || '(' || "LENGTH" || ')' || ' ' ||
(CASE WHEN "NULLS" = 'N' THEN 'NOT NULL' ELSE 'NULL' END) ||
(CASE WHEN "DEFAULT" IS NOT NULL AND "DEFAULT" != 'NULL' THEN ' ' ELSE '' END) ||
(CASE WHEN "DEFAULT" IS NOT NULL AND "DEFAULT" != 'NULL' THEN 'DEFAULT ' || "DEFAULT" ELSE '' END)
|| ','
)
ELSE (
' ' || REPLACE("COLNAME", ' ', '') || ' ' || "TYPENAME" || '(' || "LENGTH" || ', ' || "SCALE" || ')' || ' ' ||
(CASE WHEN "NULLS" = 'N' THEN 'NOT NULL' ELSE 'NULL' END) ||
(CASE WHEN "DEFAULT" IS NOT NULL AND "DEFAULT" != 'NULL' THEN ' ' ELSE '' END) ||
(CASE WHEN "DEFAULT" IS NOT NULL AND "DEFAULT" != 'NULL' THEN 'DEFAULT ' || "DEFAULT" ELSE '' END)
|| ','
)
END)
ELSE
(' ' || "COLNAME" || ' ' || "TYPENAME" || ' ' ||
(CASE WHEN "NULLS" = 'N' THEN 'NOT NULL' ELSE 'NULL' END) ||
(CASE WHEN "DEFAULT" IS NOT NULL AND "DEFAULT" != 'NULL' THEN ' ' ELSE '' END) ||
(CASE WHEN "DEFAULT" IS NOT NULL AND "DEFAULT" != 'NULL' THEN 'DEFAULT ' || "DEFAULT" ELSE '' END)
|| ','
)
END
) AS "COLUMN_DDL",
(CASE WHEN "KEYSEQ" = 0 THEN NULL ELSE "KEYSEQ" END) AS "KEYSEQ", "IDENTITY", 0 AS "IS_PRIMARYKEY"
FROM "SYSCAT"."COLUMNS"
WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}'
ORDER BY "COLNO" ASC
) AS "dbTmp1"
UNION ALL
SELECT "dbTmp2".*
FROM (
SELECT
(SELECT (COUNT(*) + 1) AS COUNT_ITEMS
FROM "SYSCAT"."COLUMNS"
WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}'
) AS "COLNO",
(CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '1') IS NULL = TRUE
THEN ''
ELSE (
' ' || 'PRIMARY KEY (' || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '1') IS NULL = FALSE
THEN '' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '1') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '2') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '2') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '3') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '3') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '4') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '4') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '5') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '5') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '6') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '6') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '7') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '7') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '8') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '8') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '9') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '9') || (
CASE WHEN (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '10') IS NULL = FALSE
THEN ', ' || (SELECT "COLNAME" FROM "SYSCAT"."COLUMNS" WHERE "TABSCHEMA" = 'APPCORE' AND "TABNAME" = '${TABLE_NAME}' AND "KEYSEQ" = '10')
ELSE ''
END
)
ELSE ''
END
)
ELSE ''
END
)
ELSE ''
END
)
ELSE ''
END
)
ELSE ''
END
)
ELSE ''
END
)
ELSE ''
END
)
ELSE ''
END
)
ELSE ''
END
) || ')'
)
END
) AS "COLUMN_DDL",
NULL AS "KEYSEQ", 'N' AS "IDENTITY", 1 AS "IS_PRIMARYKEY"
FROM SYSIBM.SYSDUMMY1
) AS "dbTmp2"
UNION ALL
SELECT "dbTmp3".*
FROM (
SELECT NULL AS "COLNO", 'CREATE TABLE ${TABLE_NAME} (' AS "COLUMN_DDL", NULL AS "KEYSEQ", 'N' AS "IDENTITY", -1 AS "IS_PRIMARYKEY"
FROM SYSIBM.SYSDUMMY1
) AS "dbTmp3"
UNION ALL
SELECT "dbTmp4".*
FROM (
SELECT NULL AS "COLNO", ');' AS "COLUMN_DDL", NULL AS "KEYSEQ", 'N' AS "IDENTITY", 2 AS "IS_PRIMARYKEY"
FROM SYSIBM.SYSDUMMY1
) AS "dbTmp4"
) AS "dbTmp5"
WHERE "dbTmp5".COLUMN_DDL != ''
ORDER BY "dbTmp5".IS_PRIMARYKEY ASC, "dbTmp5".COLNO ASC;
--- --- --- --- ---
Comments
Post a Comment