[Solved] Combine two SELECTs from input table and write to output table

[gam01hr]

Hello,
my application reads input table "tbl_names",
do some manipulation with text and put it to the output table "tbl_output". The question is how should I combine two SELECT command together to produce the output. The manipulation with text is not important in this question, the aim is the combining two SELECTs

Code: Select all

table: "tbl_names"
indexes: "id","name","surname" 
1,Alexander,Shuterland,
2,Charlotte,Robertson,
3,William,Mackensie,

Shall produce output

Code: Select all

table: "tbl_output"
indexes: "id","name","surname","name_code","surname_code"
1,Alexander,Shuterland,Alex,Shuter,
2,Charlotte,Robertson, Char,Robert,
3,William,Mackensie,   Will,Mack  ,

Manipulation step1 is working well

Code: Select all

select "id",
case when LOCATE(r, 'Alexander')!=0 then 'Alex'
when LOCATE(r, 'Charlotte')!=0 then 'Char'
when LOCATE(r, 'William')!=0 then 'Will' end
from
(select "id", "name" r from "tbl_names")
# returns
1,Alex,
2,Char,
3,Will,

Manipulation step2 is also working well

Code: Select all

select "id",
case when LOCATE(r, 'Shuterland')!=0 then 'Shuter'
when LOCATE(r, 'Robertson')!=0 then 'Robert'
when LOCATE(r, 'Mackensie')!=0 then 'Mack' end
from
(select "id", "surname" r from "tbl_names")
#
returns:
1,Shuter,
2,Robert,
3,Mack  ,

Please what strategy would you recommend to combine two SELECTs Step1 and Step 2 and INSERT it to "tbl_output" ? Thank you...

Code: Select all

create table "tbl_output"
(
"id" INTEGER,
"name" VARCHAR(20),
"surname" VARCHAR(20),
"name_code" VARCHAR(20),
"surname_code" VARCHAR(20)
)

[eremmel]

It is rather uncommon to store calculated results in a database and replicate data.
The content of your tbl_output can easily be the result of the following query:

Code: Select all

select "id", "name", "surname"
, case when LOCATE("name", 'Alexander')!=0 then 'Alex'
  when LOCATE("name", 'Charlotte')!=0 then 'Char'
  when LOCATE("name", 'William')!=0 then 'Will' end  as "name_code"
, case when LOCATE("surname", 'Shuterland')!=0 then 'Shuter'
  when LOCATE("surname", 'Robertson')!=0 then 'Robert'
  when LOCATE("surname", 'Mackensie')!=0 then 'Mack' end as "surname_code"
from "tbl_names"

When you like to map each individual to a unique code, you better of by filling this in by hand.

But assume that this list will grow over time when more persons are added and codes are not unique. So it might be better to have a lookup/mapping table ("map_table")with to columns:
"map_from" and "map_to".
You query might then look like:

Code: Select all

select "id", "name", "surname"
, coalesce(NM."map_to", '**unmapped**') as "name_code"
, coalesce(SM,"map_to", '**unmapped**') as "surname_code"
from "tbl_names" N
left join map_table NM on N."name" = NM."map_from"
left join map_table SM on N."surname" = SM."map_from"

Note: coalesce() function replaces unmapped names with '**unmapped**' string. Your database might not support this but replace it then with:
case when field is not null then field else '**unmapped**' end

[Sliderule]

I would like to mirror what user eremmel wrote above, wise advise.

Just as an FYI, you might consider, rather than using the LOCATE function, you might want to consider LIKE statement, AND if you use CASE WHEN as defined in HSQL documentation:

CASE WHEN

case when statement

The simple case statement uses a <case operand> as the predicand of one or more predicates. For the right part
of each predicate, it specifies one or more SQL statements to execute if the predicate evaluates TRUE. If the ELSE
clause is not specified, at least one of the search conditions must be true, otherwise an exception is raised.
<simple case statement> ::= CASE <case operand> <simple case statement when
clause>... [ <case statement else clause> ] END CASE

<simple case statement when clause> ::= WHEN <when operand list> THEN <SQL statement list>
<case statement else clause> ::= ELSE <SQL statement list>

Therefore . . . in your case ( pun intended ) it might look like:

Code: Select all

CASE "name" 
   WHEN LIKE 'Alexander%' THEN 'Alex'
   WHEN LIKE 'Charlotte%' THEN  'Char'
   WHEN LIKE 'William%' THEN 'Will' 
   ELSE SPACE(0) 
END as "name_code"

Explanation:

1. The above form of CASE . . . END only needs the "name" entered once in the syntax, AND place does NOT leave a NULL but rather a text string with a length of zero . . . SPACE(0) if not found.
2. The OpenOffice / LibreOffice Base Parser . . . will NOT accept it as written, but, the HSQL database back-end will. Therefore, by running the Query, you will have to send it directly to the database engine. Of course, if it were a part of an INSERT OR MERGE statement, the PARSER is not used.
3. Try it in a SELECT Query, to confirm the syntax.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[gam01hr]

Thank you very much for feedback. I learned here something new.
I.
@Sliderule
I tried your example, but in my case it didn't work. I'm not sure whether LIKE is allowed to be used in SELECT <select-list> place.

Code: Select all

SELECT "id",
CASE "name"
WHEN LIKE 'Alexander' THEN 'Alex'
WHEN LIKE 'Charlotte' THEN 'Char'
WHEN LIKE 'William' THEN 'Will' 
ELSE SPACE(0)
END as "name_code"
from "tbl_names"
# returns: error

Using LOCATE() instead of LIKE works

Code: Select all

select "id",
case when LOCATE("name", 'Alexander')!=0 then 'Alex'
when LOCATE("name", 'Charlotte')!=0 then 'Char'
when LOCATE("name", 'William')!=0 then 'Will' 
else '' end
from "tbl_names"
# returns:
1,Alex,
2,Char,
3,Will,

II.
@eremmel
Meanwhile I've seen this [1] video, where the same concept is used. Load data, do some manipulation on it and store it to another table.
I agree, there are other suitable tools available for data manipulation like gawk.
Thanks to that video this inspired me to use UPDATE ... SET command which solved my problem. It is not exactly two SELECTs JOIN approach (which I need to learn), but it works well. Link [1] www.youtube.com/watch?v=BwrWoP0Wr7w

Code: Select all

#step 1, copy "id", "name", "surname" to output table)
insert into "tbl_output" ("id", "name", "surname")
select * from "tbl_names"

Code: Select all

#step 2 manipulation on "name" and write to "name_code"
UPDATE "tbl_output" SET "name_code" = 
case when LOCATE("name", 'Alexander')!=0 then 'Alex'
when LOCATE("name", 'Charlotte')!=0 then 'Char'
when LOCATE("name", 'William')!=0 then 'Will' 
else '' end
# returns:
1,Alexander,Shuterland,Alex
2,Charlotte,Robertson, Char
3,William,Mackensie,   Will

Code: Select all

#step 3 manipulation on "surname" and write to "surename_code"
UPDATE "tbl_output" SET "surname_code" = 
case when LOCATE("surname", 'Shuterland')!=0 then 'Shuter'
when LOCATE("surname", 'Robertson')!=0 then 'Robert'
when LOCATE("surname", 'Mackensie')!=0 then 'Mack' 
else '' end
# returns:
1,Alexander,Shuterland,Alex, Shuter,
2,Charlotte,Robertson, Char, Robert,
3,William,Mackensie,   Will, Mack  ,

[eremmel]

@gam01hr, I'm glad you find your way with that video. Good to see how practical home grown knowledge finds its way to a larger audience and see/learn that you can use a database (tool) as if you are manipulating Calc sheets.

[Sliderule]

@Sliderule
I tried your example, but in my case it didn't work. I'm not sure whether LIKE is allowed to be used in SELECT <select-list> place.
CODE: SELECT ALL

Code: Select all

SELECT "id",
CASE "name"
WHEN LIKE 'Alexander' THEN 'Alex'
WHEN LIKE 'Charlotte' THEN 'Char'
WHEN LIKE 'William' THEN 'Will' 
ELSE SPACE(0)
END as "name_code"
from "tbl_names"

# returns: error

While I am glad you found a solution that works for you . . . for others reading this . . . what I posted above does work ( for me ) when entering it as I wrote it.

Code: Select all

CASE "name" 
   WHEN LIKE 'Alexander%' THEN 'Alex'
   WHEN LIKE 'Charlotte%' THEN  'Char'
   WHEN LIKE 'William%' THEN 'Will' 
   ELSE SPACE(0) 
END as "name_code"

User gam01hr did NOT include the character % as a part of the text string you are looking for . . . AND / OR . . . follow step 2 in my Explanation description.

Sliderule

[gam01hr]

Hello @Sliderule, thank you for clarification. I tried again. The example with LIKE works this time. It works even if the '%' char is not a part of the text string. The key was to run it on newer version 2.3.2 "split database". If I run the same code again on "embedded" Base HSQLDB I get error at step 3 & 4.
# step 1

Code: Select all

create table "tbl_names" 
(
"id" INTEGER,
"name" VARCHAR(20),
"surname" VARCHAR(20),
"name_code" VARCHAR(20),
"surname_code" VARCHAR(20)
)

# step 2

Code: Select all

insert into "tbl_names" values (1,'Alexander','Shuterland','','')
insert into "tbl_names" values (2,'Charlotte','Robertson','','')
insert into "tbl_names" values (3,'William','Mackensie','','')

# step 3

Code: Select all

select 
CASE "name" 
   WHEN LIKE 'Alexander%' THEN 'Alex'
   WHEN LIKE 'Charlotte%' THEN  'Char'
   WHEN LIKE 'William%' THEN 'Will' 
   ELSE SPACE(0) 
END as "name_code"
from "tbl_names"
# returns:
Alex,
Char,
Will,

# step 4

Code: Select all

SELECT * FROM "INFORMATION_SCHEMA"."SQL_IMPLEMENTATION_INFO"
# returns:
...
DBMS VERSION                 ,,2.3.2 
...

Edit1: In case for embedded db, acc. [1] determine the version of internal HSQLDB. I got in my case
CALL "org.hsqldb.Library.getDatabaseFullProductVersion"()
# returns:
1.8.0.10,
[1] https://wiki.openoffice.org/wiki/Docume ... th_Base%3F