Hi,
I'm new to H2 and need to run some SQL requests originally written for
Oracle.
One request involves the TO_CHAR and TO_DATE Oracle functions.
As TO_DATE is not yet supported I have added an alias for it. Not yet
tested but at least H2 knows about my alias.
However, I think I'm facing two problems with TO_CHAR (using latest H2
version, 1.4.182, 2014-10-17).
Considering this table:
CREATE TABLE CCH (
id NUMBER ( 8 ) NOT NULL,
since TIMESTAMP NOT NULL,
until TIMESTAMP,
CONSTRAINT PK119 PRIMARY KEY (id)
);
An example of request is:
SELECT
id,
TO_CHAR(since,'YYYY/MM/DD HH24:MI:SS'),
TO_CHAR(until,'YYYY/MM/DD HH24:MI:SS')
FROM CCH
WHERE (since >= TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS') ) ORDER BY id;
Problem 1:
---------------
When running this request, I get the following exception:
org.h2.jdbc.JdbcSQLException: Column "TO_CHAR(SINCE,YYYY/MM/DDHH24:MI:SS)"
not found [42122-176]
which makes me think H2 does not evaluate the TO_CHAR function in SELECT
statements. Am I right? Should I create a new defect ticket for this? Sadly
I have no idea how to provide a patch to fix this, if it's confirmed it's a
bug.
Problem 2:
---------------
To be sure H2 supports the format written in my request I made a small Java
Program:
import java.math.BigDecimal;
import org.h2.util.ToChar;
public class Test {
public static void main(String[] args) {
System.out.println(ToChar.toChar(new BigDecimal(0), "YYYY/MM/DDHH24:MI:SS",
null));
}
}
Unfortunately it seems this format is not yet supported as I get:
org.h2.jdbc.JdbcSQLException: Invalid TO_CHAR format "YYYY/MM/DDHH24:MI:SS"
[90010-182]
Same question, should I create a new defect ticket for this? I think I
could maybe write a patch for this point.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
