Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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!