Sliderule Smith

TO_CHAR Numeric Value With S and Positive on Left

Fred: I suggest one additional modification to documentation for Release Candidate of HSQL Version 273. In file below, lines 2658-2659 should be: https://sourceforge.net/p/hsqldb/svn/HEAD/tree/base/trunk/doc-src/guide/builtinfunctions.xml change to ( add _TZ to the function name ): <para><literal>TO_TIMESTAMP_TZ ( &lt;char value expr&gt;, &lt;char value expr&gt; )</literal></para> Sliderule

Fred: A few notes in my 'testing' of the HSQL Version 2.7.3 BETA ( hsqldb-2.7.3-debug-rc1.jar ): Thanks very much for adding the additional TZ for use in function TO_CHAR to include a timestamp. Noted in the documentation ( I am looking at the PDF version ), two spelling errors: on page 107, TO_TIEMSTAMP_TZ should be TO_TIMESTAMP_TZ , and on page 30, TIEMSTAMP should be TIMESTAMP . On page 107, you discuss using JAVA: in the formatting for use with TO_CHAR , if I understand correctly. While I found...

Fred: Just to let you know, I was successfully able to use the latest ( 2024-04-30 ) SVN, HSQL Version 2.7.3, to confirm ROLLUP now works as expected. I appreciate your efforts very much. Sliderule

Fred: Just as a further observation ( issue / bug ) when using Group By ROLLUP( , for example, STDDEV_POP, AVG, MEDIAN etc, AND, use a CAST on the calculated result, ROLLUP will return an error, whereas, GROUP BY will not. For example, with my above Query, STDDEV_POP(BLOOD_SUGAR) as "STD DEV" -- This works ROLLUP or GROUP BY CAST(STDDEV_POP(BLOOD_SUGAR) as DECIMAL(10,5)) as "STD DEV Decimal" -- This does NOT work when ROLLUP used Conclusion, the ROLLUP error is NOT unique to a DATE and TIME column...

Per you request, below is a sample CREATE TABLE: CREATE TABLE BLDTEST03(TEST_DATE DATE NOT NULL,TEST_TIME TIME NOT NULL,BLOOD_SUGAR SMALLINT NOT NULL,GROUP_TIME VARCHAR(1) NOT NULL,PRIMARY KEY(TEST_DATE,TEST_TIME)); Below is a sample INSERT statement: INSERT INTO BLDTEST03(TEST_DATE,TEST_TIME,BLOOD_SUGAR,GROUP_TIME) VALUES('1999-12-28','7:35:00',167,'B'), ('1999-12-28','12:15:00',124,'L'), ('1999-12-28','17:16:00',33,'D'), ('1999-12-28','22:27:00',83,'O'), ('1999-12-29','7:17:00',204,'B'), ('1999-12-29','12:11:00',123,'L'),...

HSQL 2.7.2 With ROLLUP and TO_CHAR with MEDIAN or AVG on Type DATE or Time

I have a suggestion for HSQL Version 2.7.3 release. As of HSQL Version 2.7.2, a few modifications might be made to include 12 entries for INFORMATION_SCHEMA.SYSTEM_TABLES in the column REMARKS . By running the following query with HSQL Version 2.7.2 , you can identify 12 rows where the contents of REMARKS is either NULL or a length of zero. SELECT INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_SCHEM, INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_NAME, INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_TYPE, INFORMATION_SCHEMA.SYSTEM_TABLES.REMARKS...