Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hello,

One item you do not state is the HSQL version used. If the embedded v1.8 (Base default) it is very old and does not offer the capabilities of newer DB's.

Please look at the following post -> HSQLDB v1.8: Help having two records per row returned by the Select statement . Fred Toussi offers what seems to be what you need - using a 'Sequence' and possibly a View. Even shows a 'Select' statement (not quite what you need but close).

Another approach there is from Sliderule Smith (also links in one of the comments).

Hello,

One item you do not state is the HSQL version used. If the embedded v1.8 (Base default) it is very old and does not offer the capabilities of newer DB's.

Please look at the following post -> HSQLDB v1.8: Help having two records per row returned by the Select statement . Fred Toussi offers what seems to be what you need - using a 'Sequence' and possibly a View. Even shows a 'Select' statement (not quite what you need but close).

Another approach there is from Sliderule Smith (also links in one of the comments).

Edit 2018-06-16:

Modified sample from @pierre-yves samyn by changing date on rec id #4 to 03/15/10. Using routine by Toussi here is result:

image description

Attached is modified sample from @pierre-yves samyn and includes query RecordDayDiff and appropriate Views created for this.

Sample ---- C:\fakepath\Rec1VSRec2DateDiff.odb