[PERF] JSQLParser Version : parse timeout when calling many nested functions

[young0098]

Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram

Failing SQL Feature:

• parse timeout，just a common sql , haved set timeout argument to 3 minutes.
• Example: WITH ROLLUP can't be parsed

SQL Example:

• Simplified Query Example, focusing on the failing feature

INSERT INTO
C01_INDIV_TELBK_CUST_INFO_H_T2 (PARTY_ID, PARTY_SIGN_STAT_CD, SIGN_TM, CLOSE_TM)
SELECT
A1.PARTY_ID,
A1.PARTY_SIGN_STAT_CD,
CAST(
(
CASE
WHEN A1.SIGN_TM IS NULL
OR A1.SIGN_TM = '' THEN CAST(
CAST(
CAST('ATkkIVQJZm' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
WHEN CHARACTERS (TRIM(A1.SIGN_TM)) <> 19
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) > '1'
OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) > '3'
OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 4) = '0000'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '00'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '00'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) = '0' THEN CAST(
CAST(
CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
ELSE (
CASE
WHEN (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 29
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '02'
)
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 31
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <> '02'
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <= 12
)
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) = 31
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')
) THEN CAST(A1.SIGN_TM AS TIMESTAMP)
WHEN SUBSTR (TRIM(A1.SIGN_TM), 6, 2) || SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '0229'
AND (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 400 = 0
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 100 <> 0
)
) THEN CAST(A1.SIGN_TM AS TIMESTAMP)
ELSE CAST(
CAST(
CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
END
)
END
) AS DATE FORMAT 'YYYYMMDD'
),
CAST(
(
CASE
WHEN A1.CLOSE_TM IS NULL
OR A1.CLOSE_TM = '' THEN CAST(
CAST(
CAST('ATkkIVQJZm' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
WHEN CHARACTERS (TRIM(A1.CLOSE_TM)) <> 19
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) > '1'
OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) > '3'
OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) = '0000'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '00'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '00'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) = '0' THEN CAST(
CAST(
CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
ELSE (
CASE
WHEN (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 29
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '02'
)
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 31
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <> '02'
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <= 12
)
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) = 31
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')
) THEN CAST(A1.CLOSE_TM AS TIMESTAMP)
WHEN SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) || SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '0229'
AND (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 400 = 0
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 100 <> 0
)
) THEN CAST(A1.CLOSE_TM AS TIMESTAMP)
ELSE CAST(
CAST(
CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE
) || ' 00:00:00' AS TIMESTAMP
)
END
)
END
) AS DATE FORMAT 'YYYYMMDD'
)
FROM
T01_PTY_SIGN_H_T1 A1
WHERE
A1.PARTY_SIGN_TYPE_CD = 'CD_021'
AND A1.ST_DT <= CAST('LDBCGtCIyo' AS DATE FORMAT 'YYYYMMDD')
AND A1.END_DT > CAST('LDBCGtCIyo' AS DATE FORMAT 'YYYYMMDD')
GROUP BY
1,
2,
3,
4
```

Software Information:

• JSqlParser 4.8
• Database Teradata

Tips:

Please write in English and avoid Screenshots (as we can't copy and paste content from it).
Try your example online with the latest JSQLParser and share the link in the error report.
Do provide Links or References to the specific Grammar and Syntax you are trying to use.

[manticore-projects]

The query fails after 52 seconds:

CAST( expression AS DATE FORMAT char_literal) is not supported.

[manticore-projects]

void testIssue1983() throws JSQLParserException {
        String sqlStr = "INSERT INTO\n" +
                "C01_INDIV_TELBK_CUST_INFO_H_T2 (PARTY_ID, PARTY_SIGN_STAT_CD, SIGN_TM, CLOSE_TM)\n" +
                "SELECT\n" +
                "A1.PARTY_ID,\n" +
                "A1.PARTY_SIGN_STAT_CD,\n" +
                "CAST(\n" +
                "(\n" +
                "CASE\n" +
                "WHEN A1.SIGN_TM IS NULL\n" +
                "OR A1.SIGN_TM = '' THEN CAST(\n" +
                "CAST(\n" +
                "CAST('ATkkIVQJZm' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "WHEN CHARACTERS (TRIM(A1.SIGN_TM)) <> 19\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) > '1'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) > '3'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 1, 4) = '0000'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '00'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '00'\n" +
                "OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) = '0' THEN CAST(\n" +
                "CAST(\n" +
                "CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "ELSE (\n" +
                "CASE\n" +
                "WHEN (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 29\n" +
                "AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '02'\n" +
                ")\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 31\n" +
                "AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <> '02'\n" +
                "AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <= 12\n" +
                ")\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) = 31\n" +
                "AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')\n" +
                ") THEN CAST(A1.SIGN_TM AS TIMESTAMP)\n" +
                "WHEN SUBSTR (TRIM(A1.SIGN_TM), 6, 2) || SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '0229'\n" +
                "AND (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 400 = 0\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 4 = 0\n" +
                "AND CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 100 <> 0\n" +
                ")\n" +
                ") THEN CAST(A1.SIGN_TM AS TIMESTAMP)\n" +
                "ELSE CAST(\n" +
                "CAST(\n" +
                "CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "END\n" +
                ")\n" +
                "END\n" +
                ") AS DATE FORMAT 'YYYYMMDD'\n" +
                "),\n" +
                "CAST(\n" +
                "(\n" +
                "CASE\n" +
                "WHEN A1.CLOSE_TM IS NULL\n" +
                "OR A1.CLOSE_TM = '' THEN CAST(\n" +
                "CAST(\n" +
                "CAST('ATkkIVQJZm' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "WHEN CHARACTERS (TRIM(A1.CLOSE_TM)) <> 19\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) > '1'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) > '3'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) < '0'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) > '9'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) = '0000'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '00'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '00'\n" +
                "OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) = '0' THEN CAST(\n" +
                "CAST(\n" +
                "CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "ELSE (\n" +
                "CASE\n" +
                "WHEN (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 29\n" +
                "AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '02'\n" +
                ")\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 31\n" +
                "AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <> '02'\n" +
                "AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <= 12\n" +
                ")\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) = 31\n" +
                "AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')\n" +
                ") THEN CAST(A1.CLOSE_TM AS TIMESTAMP)\n" +
                "WHEN SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) || SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '0229'\n" +
                "AND (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 400 = 0\n" +
                "OR (\n" +
                "CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 4 = 0\n" +
                "AND CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 100 <> 0\n" +
                ")\n" +
                ") THEN CAST(A1.CLOSE_TM AS TIMESTAMP)\n" +
                "ELSE CAST(\n" +
                "CAST(\n" +
                "CAST('cDXtwdFyky' AS DATE FORMAT 'YYYYMMDD') AS DATE\n" +
                ") || ' 00:00:00' AS TIMESTAMP\n" +
                ")\n" +
                "END\n" +
                ")\n" +
                "END\n" +
                ") AS DATE FORMAT 'YYYYMMDD'\n" +
                ")\n" +
                "FROM\n" +
                "T01_PTY_SIGN_H_T1 A1\n" +
                "WHERE\n" +
                "A1.PARTY_SIGN_TYPE_CD = 'CD_021'\n" +
                "AND A1.ST_DT <= CAST('LDBCGtCIyo' AS DATE FORMAT 'YYYYMMDD')\n" +
                "AND A1.END_DT > CAST('LDBCGtCIyo' AS DATE FORMAT 'YYYYMMDD')\n" +
                "GROUP BY\n" +
                "1,\n" +
                "2,\n" +
                "3,\n" +
                "4";
        CCJSqlParserUtil.parse(sqlStr, parser -> parser
                .withSquareBracketQuotation(false)
                .withAllowComplexParsing(true)
                .withTimeOut(60000));
    }

[manticore-projects]

Seems to be Google BigQuery.
Lucky you as I am going to implement this special FORMAT clause.

[manticore-projects]

I have implemented Google BigQuery CAST with FORMAT clause and in the result the query does not fail anymore after 1 minute.

Instead it literally runs forever, likely because of the way how you call FUNCTION (which depends on expensive semantic LOOKAHEAD amplifying when nested deeply).

I can only encourage you to write more reasonable SQL. e.g. instead of calling SUBSTR (TRIM(A1.CLOSE_TM), x) a million times, you could just Split() your string into characters and then access this array.

Unfortunately I don't have the time to dig deeper into this particular statement right now. You can try to remove logical blocks until it parses normally in order to isolate the particular logical block where it starts hanging. Once we get this analysis, we can certainly do something about in the Grammar.

Good luck.

[young0098]

Thanks. This is old code, I have to paser it. I change cast expression manually, delete format keywords. The timeout exception is remain. If I want to pasrse this query successfully, How many timeout I should set？
INSERT INTO
C01_INDIV_TELBK_CUST_INFO_H_T2 (PARTY_ID, PARTY_SIGN_STAT_CD, SIGN_TM, CLOSE_TM)
SELECT
A1.PARTY_ID,
A1.PARTY_SIGN_STAT_CD,
CAST(
(
CASE
WHEN A1.SIGN_TM IS NULL
OR A1.SIGN_TM = '' THEN CAST(
CAST(CAST('UsGgJQoxuH' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
WHEN CHARACTERS (TRIM(A1.SIGN_TM)) <> 19
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 2, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 3, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 4, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 1) > '1'
OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 7, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 1) > '3'
OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) < '0'
OR SUBSTR (TRIM(A1.SIGN_TM), 10, 1) > '9'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 4) = '0000'
OR SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '00'
OR SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '00'
OR SUBSTR (TRIM(A1.SIGN_TM), 1, 1) = '0' THEN CAST(
CAST(CAST('airHppDlix' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
ELSE (
CASE
WHEN (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 29
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) = '02'
)
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) < 31
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <> '02'
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) <= 12
)
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 9, 2) AS INTEGER) = 31
AND SUBSTR (TRIM(A1.SIGN_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')
) THEN CAST(A1.SIGN_TM AS TIMESTAMP)
WHEN SUBSTR (TRIM(A1.SIGN_TM), 6, 2) || SUBSTR (TRIM(A1.SIGN_TM), 9, 2) = '0229'
AND (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 400 = 0
OR (
CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR (TRIM(A1.SIGN_TM), 1, 4) AS INTEGER) MOD 100 <> 0
)
) THEN CAST(A1.SIGN_TM AS TIMESTAMP)
ELSE CAST(
CAST(CAST('airHppDlix' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
END
)
END
) AS DATE
),
CAST(
(
CASE
WHEN A1.CLOSE_TM IS NULL
OR A1.CLOSE_TM = '' THEN CAST(
CAST(CAST('UsGgJQoxuH' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
WHEN CHARACTERS (TRIM(A1.CLOSE_TM)) <> 19
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 2, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 3, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 4, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 1) > '1'
OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 7, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 1) > '3'
OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) < '0'
OR SUBSTR (TRIM(A1.CLOSE_TM), 10, 1) > '9'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) = '0000'
OR SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '00'
OR SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '00'
OR SUBSTR (TRIM(A1.CLOSE_TM), 1, 1) = '0' THEN CAST(
CAST(CAST('airHppDlix' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
ELSE (
CASE
WHEN (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 29
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) = '02'
)
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) < 31
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <> '02'
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) <= 12
)
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) AS INTEGER) = 31
AND SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) IN ('01', '03', '05', '07', '08', '10', '12')
) THEN CAST(A1.CLOSE_TM AS TIMESTAMP)
WHEN SUBSTR (TRIM(A1.CLOSE_TM), 6, 2) || SUBSTR (TRIM(A1.CLOSE_TM), 9, 2) = '0229'
AND (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 400 = 0
OR (
CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR (TRIM(A1.CLOSE_TM), 1, 4) AS INTEGER) MOD 100 <> 0
)
) THEN CAST(A1.CLOSE_TM AS TIMESTAMP)
ELSE CAST(
CAST(CAST('airHppDlix' AS DATE) AS DATE) || ' 00:00:00' AS TIMESTAMP
)
END
)
END
) AS DATE
)
FROM
T01_PTY_SIGN_H_T1 A1
WHERE
A1.PARTY_SIGN_TYPE_CD = 'CD_021'
AND A1.ST_DT <= CAST('oXKthfFlJH' AS DATE)
AND A1.END_DT > CAST('oXKthfFlJH' AS DATE)
GROUP BY
1,
2,
3,
4

[manticore-projects]

I really don't know, you can only try out by yourself.