Ask Your Question

Base: 2 problems after splitting DB (& upgrading to 2.4 HSQLDB)

asked 2017-10-27 17:07:28 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Base: 2 problems after splitting DB (& upgrading to 2.4 HSQLDB):

  1. Tables: The existing columns cannot be amended. On my original embedded DB, I had formatted the (autovalue) ID fields to have leading zeros. This formatting has been lost, and I can't amend the column to reinstate it. I can add a new column.

  2. SQL code. So that leading zeros are displayed in query results and in forms, I originally used the following code

    SELECT RIGHT(100000 + "c"."person_id", 4) || ' ' || "p"."1st_name" || ' ' || "p"."surname" "Donator", etc etc.

    However, the inclusion of RIGHT (which I presume has become obsolete) produces the following error message:

SQL Status: 42561 Error code: -5561

incompatible data type in conversion in statement [SELECT RIGHT( 100000 + "c"."person_id", 4 ) || ' ' || "p"."1st_name" || ' ' || "p"."surname" "Donator", etc etc.

I've spent the morning googling for alternatives and the closest i was able to ascertain was COALESCE. Tried that in place of RIGHT, but that produces a result which includes the leading 1, i.e. 1000008 instead of 0008).

Lastly, can you point in the direction of a source that not only list the keyword (for HSQLDB 2.4), but also give description of what they do, their function, I guess?

edit retag flag offensive close merge delete


Your question raised a question concerning your tables structure. You wrote: "I had formatted the (autovalue) ID fields to have leading zeros." If the ID field is supposed to be the Primary Field (PK), it does not need to be formatted since the only purpose of it is to link tables. If the ID field is a "regular" field (Not a PK) then formatting is OK. (Additionally, a field used in a table for a Foreign Key must match the PK in the referenced table.)

Steve R. gravatar imageSteve R. ( 2017-10-27 21:40:29 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-10-27 17:46:36 +0200

Ratslinger gravatar image

updated 2017-10-27 18:39:53 +0200


You are correct that split DB's cannot edit table structure in the IDE. You can edit using an ALTER statement if needed but for what you are asking this does not apply. What you want is a change in the display format. This is done in various ways. For example, double click on the table to display the data. Next, right click the column heading and choose Column Format. Then choose the formatting you want. Form controls have their own properties for display.

Your statement about using RIGHT is incorrect. This works without problem in v2.4 as I have just re-tested it. The error is something else in the statement.

You can get the v2.4 pdf document here.


In your SQL, you may need to CAST a field or two to another type in order for it to work.

edit flag offensive delete link more

answered 2017-10-28 20:12:25 +0200

johnh009 gravatar image

Thank you for your prompt response. As you suggested, I've changed the relevant columns format to show leading zeros.

But I'm still stuck with RIGHT! I've created a small table "test", contents as below:

id name surname 0000 fred smith 0001 joe soap 0002 fred bloggs 0003 charlie farley 0004 piggy malone 0005 alias gruntfutik 0006 jon jones 0007 athur moon

SELECT id || ' ' || name || ' ' || surname AS "Person" FROM "test"


Person 0 fred smith 1 joe soap 2 fred bloggs 3 charlie farley 4 piggy malone 5 alias gruntfutik 6 jon jones 7 athur moon

SELECT RIGHT(10000 + id, 4) || ' ' || name || ' ' || surname AS "Person" FROM "test"

produces the following error:

The data content could not be loaded.

SQL Status: 42561 Error code: -5561

incompatible data type in conversion in statement [SELECT RIGHT( 10000 + "id", 4 ) || ' ' || "name" || ' ' || "surname" AS "Person" FROM "test"]

I think this test proves the the RIGHT function doesn't work in my environment, though this code did work within the embedded environment.

Needless to say, I'm still looking and experimenting to find a solution - getting closer I think!

edit flag offensive delete link more


I believe you are stuck looking in the wrong spot. First, just try a simple RIGHT:

SELECT RIGHT("name", 4) FROM "test"

Does that work?

Ratslinger gravatar imageRatslinger ( 2017-10-28 20:22:57 +0200 )edit

Then try:

SELECT RIGHT(10000 + CAST("id" as varchar(5)), 4) || ' ' || "name" || ' ' || "surname" AS "Person" FROM "test"
Ratslinger gravatar imageRatslinger ( 2017-10-28 20:37:36 +0200 )edit

Perfect. Thank you very much for your help. Much appreciated.

johnh009 gravatar imagejohnh009 ( 2017-10-29 11:23:40 +0200 )edit

@johnh009 I have asked this on your last question after you stated the answer was correct. I am again asking here, and hopefully it won't be ignored as was the last:

If this answers your question please click on the ✔ (upper left area of my answer).

Ratslinger gravatar imageRatslinger ( 2017-10-29 13:32:11 +0200 )edit

@Ratslinger. Apologies for omitting to click on the tick (✔) - done on both posts.

Thanks again for your help.

johnh009 gravatar imagejohnh009 ( 2017-10-31 22:04:29 +0200 )edit

@johnh009 You're welcome. Glad to help anytime. Please understand I ask people to "click on the tick (✔)" so that others searching for this problem may see that an answer has been provided which satisfied the question. Thanks for your help. As an aside, it does raise your karma a bit. For me, the karma means/does nothing.

Ratslinger gravatar imageRatslinger ( 2017-10-31 22:48:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-10-27 17:07:28 +0200

Seen: 47 times

Last updated: Oct 28 '17