[Solved] Padding Numeric Result for Concatenation/Increment

Discuss the database features

[Solved] Padding Numeric Result for Concatenation/Increment

Postby cazbym » Tue Feb 20, 2018 2:47 am

Hello all!

I'm trying to create a unique reference which consists of a 3 char code, plus another 3 char code and finally a 3 digit number

I have:

Three tables 1,2 & 3, each with IDs and Codes
The tables are linked with Table 1 being the parent of Table 2 and the grandparent of Table 3

My aim is for each time a new record is created in Table 3, a unique number is generated which will take the format of "Table 1"."Code" plus "Table 2"."Code" plus a 3 digit number which auto-increments for each new record in Table 3.

So Number should look something like this:

ABC-XYZ-001
ABC-XYZ-002

ABY-XYZ-003

DEF-GHI-001
DEF-GHI-002

Each time a new record is created with the same Table 1 Code and Table 2 Code, then the 001 part of the number should increment by 1.

ABC and XYZ are VARCHAR, and I have a 1 which is the result of a Count of Table 3 IDs.
So I need a number to have 3 digits and to be able to increment it.

I can only find ways to pad the number by changing the format, which fails as soon as it is concatenated. I also need to retain as numeric,so that I can increment by 1.
Any help greatly appreciated.
Last edited by cazbym on Tue Feb 20, 2018 9:46 pm, edited 2 times in total.
Windows 10
OOo 4.1.5
connected to
External HSQLDB 2.3.2 DB
cazbym
 
Posts: 40
Joined: Wed Apr 15, 2009 2:52 pm

Re: Padding Numeric Result for Concatenation & Increment

Postby Sliderule » Tue Feb 20, 2018 4:58 pm

Since you are using HSQL Version 2.3.0 as an external database, and, NOT HSQL Embedded ( HSQL Version 1.8.0.10 ), you could use something like the following:

Code: Select all   Expand viewCollapse view
CONCAT_WS('-', "Table 1"."Code", "Table 2"."Code", LPAD("Table 3"."ID",3,'0'))


The available functions, including, CONCAT_WS and LPAD ( see HSQL documentation PDF that you downloaded, or, http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html for an explanation of the functions.

You might also want to consider using Triggers, both an INSERT and UPDATE if you want to 'store' the value, rather than having it available via a Query and/or a View.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1153
Joined: Thu Nov 29, 2007 9:46 am

Re: [SOLVED]Padding Numeric Result for Concatenation & Incre

Postby cazbym » Tue Feb 20, 2018 9:44 pm

Many thanks SlideRule. :D

I had searched for "padding" through the HSQLDB documentation, but obviously not hard enough. :oops: I am definitely going for the Trigger on creating the record and using a macro to insert and update, but I run through the query route first to help me get a hold of the syntax. I find the GUI helps me enormously with understanding the SQL.

In this case, I (hopefully), simplified it for readability, but what I am actually doing is trying to create an incremental number which is specific to the Table 1 Code & Table 2 Code combination, so in fact, rather than using and padding the ID field from Table 3, I am padding the Count of the ID + 1, filtered by Table 1 and Table 2 Codes, to generate the new number. Hope that makes sense. Anyway, very grateful for your exceedingly speedy input!
Windows 10
OOo 4.1.5
connected to
External HSQLDB 2.3.2 DB
cazbym
 
Posts: 40
Joined: Wed Apr 15, 2009 2:52 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests