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

[johnh009]

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?

[SteveR]

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.)

[Ratslinger]

Hello,

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.

EDIT:

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

[johnh009]

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”

produces:

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!

[Ratslinger]

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]

Then try:

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

[johnh009]

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

[Ratslinger]

@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).

[johnh009]

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

Thanks again for your help.

[Ratslinger]

@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.