Page 1 of 1

SQL obtain all lines ?

PostPosted: Sat Jan 05, 2019 4:04 pm
by arfgh
hey there friends.

taking in consideration this simple query:

Code: Select all   Expand viewCollapse view
select distinct "Colors" from "Items"


"Colors" may have 1 or several lines with colors into, exmple:

Code: Select all   Expand viewCollapse view
White
Red
Blue


I just need to obtain all distinct color entries but with the detail that "Colors" may have a color for each line. Char(10) as line change.
So i want to query and obtain all results that are distinct but with that kind of read each line on "colors".
By the moment i have no idea how can i do that from SQL....

help

Re: SQL obtain all lines ?

PostPosted: Sat Jan 05, 2019 4:20 pm
by Villeroy
HSQL 2:
Code: Select all   Expand viewCollapse view
SELECT REPLACE(GROUP_CONCAT("Text"),',',CHAR(10))AS "Concat" FROM "Table"

Re: SQL obtain all lines ?

PostPosted: Sat Jan 05, 2019 4:35 pm
by arfgh
hey there Villeroy :)
unfortunatelly that query is throwing access is denied: group_concat
can you please specify more about the working of that ?

what i want to do is strip data and give a result for each line.

AOO last version here, embedded db.

edit: at the moment i have this that aproachs the wanted, but unfortunatelly only take the first line:
Code: Select all   Expand viewCollapse view
SELECT distinct left("Colors",locate(char(10),"Colors")) FROM "Items"

Re: SQL obtain all lines ?

PostPosted: Sat Jan 05, 2019 6:22 pm
by UnklDonald418
I just need to obtain all distinct color entries but with the detail that "Colors" may have a color for each line. Char(10) as line change.

If your database was normalized, you could use
Code: Select all   Expand viewCollapse view
SELECT * FROM "Colors"

A normalized database would have a table "Colors" with the color name and any other data associated with that color and "Items" would simply have a Foreign Key column "ColorID".

Re: SQL obtain all lines ?

PostPosted: Sat Jan 05, 2019 8:04 pm
by Villeroy
I got that wrong last night. GROUP_CONCAT (HSQL2) concatenates separated strings to concatenated strings. With a little help of Calc you may be able to normalize your data. Dump the strings, split in rows, transpose rows to columns, merge columns eliminate duplicates, paste into prepared database table with PK, generate intermediate table for n-m-relation. Compose an SQL statement which dumps all PKs of single words into the intermediate table having a matching substring in the old field together with the PKs of the other table. This takes some time and effort.

Re: SQL obtain all lines ?

PostPosted: Sun Jan 06, 2019 1:32 pm
by arfgh
finally i did figure how to split all results with the char(10) as line separator. The problem, 'Distinct' only works with a single column. So, if we have a result with 4 columns, just a line on each, is there a way to use 'Distintct' in order to have a final result in a single column with all the diferent terms on the 4 ?
@UnklDonald418 you didnt understand the problem...
@Villeroy i use base, not calc.

Re: SQL obtain all lines ?

PostPosted: Tue Jan 08, 2019 1:33 pm
by Villeroy
arfgh wrote:@Villeroy i use base, not calc.

Calc can be a helpful tool to import, manipulate and paste back database data in more visual ways.

With HSQL2 you would have built-in functions for arrays and regular expressions.
With HSQL1 I played with a table "Data" having a primary key "ID" and a multi-line comment field "REM":

Query1 (direct SQL, to be stored as "View1"):
Code: Select all   Expand viewCollapse view
SELECT
   casewhen("L1" > 0, SUBSTR( "REM", 1, "L1"-1), "REM" ) AS "S1",
   casewhen("L1" > 0, SUBSTR( "REM", "L1" +1, ABS("L2" - "L1")), '' )  AS "S2",
   casewhen("L1">0 AND "L2">0, SUBSTR( "REM", "L2" +1, ABS("L3" - "L2")), '') AS "S3",
   casewhen("L1">0 AND "L2">0 AND "L3">0, SUBSTR( "REM", "L3" +1), '') AS "S4",
   "ID","REM",L1,L2,L3
FROM (
   SELECT
      LOCATE( CHAR( 10 ), "REM" ) AS "L1",
      LOCATE( CHAR( 10 ), "REM", LOCATE( CHAR( 10 ), "REM" ) +1) AS "L2",
      LOCATE( CHAR( 10 ), "REM", LOCATE( CHAR( 10 ), "REM", LOCATE( CHAR( 10 ), "REM" ) +1) +1) AS "L3",
      "ID", "REM"
   FROM "Data"
) AS "L"

The inner SELECT selects 3 numbers L1,L2,L3 into record set "L" marking the positions of up to 3 times CHAR(10).
The outer SELECT selects up to 4 substrings S1,S2,S3,S4 based on the positions L1,L2,L3 of record set L.
With Windows line breaks the query needs some modification since Windows line breaks consist of 2 characters.

Query2 (direct SQL referring to "View1"):
Code: Select all   Expand viewCollapse view
SELECT "S"."S1", "S"."ID" FROM (
  SELECT "S1", "ID" FROM "View1"
  UNION SELECT "S2", "ID" FROM "View1"
  UNION SELECT "S3", "ID" FROM "View1"
  UNION SELECT "S4", "ID" FROM "View1"
) AS "S"
WHERE COALESCE ( RTRIM( LTRIM( "S1" ) ), '' ) != ''

This returns unique combinations of strings and record IDs without empty strings nor blank strings.

Re: SQL obtain all lines ?

PostPosted: Tue Jan 08, 2019 4:55 pm
by Villeroy
In order to turn this into a many-to-many relation:
Create a table, say "REMARKS" (text, auto-id) and a linking table, say "DATA_REM" (2 integers as primary key) storing which remarks belong to which data record.
Create a view to SELECT DISTINCT "S1" FROM "View2", copy the view and paste the S1 field into the remarks table.
Create another view to select the remark-IDs and the corresponding data-IDs based on the common strings in the remarks and View2:
Code: Select all   Expand viewCollapse view
SELECT "View2"."ID" AS "DID", "REMARKS"."ID" AS "RID" FROM "View2" JOIN "REMARKS" ON "View2"."S1" = "REMARKS"."REM"

Paste this view to the linking table.

Re: SQL obtain all lines ?

PostPosted: Wed Jan 09, 2019 3:17 pm
by arfgh
i'm trying to understand what you did there exactly, because at the momenti didnt get it. L1 ? L2 ? etc etc explain please. My data source come from a single column, lines separed by char(10). And i want to split them in 4 results, because 4 lines, and at finally do 'distinct' on the 4 obtained columns and obtain a new column with the distinct results on it.

Re: SQL obtain all lines ?

PostPosted: Wed Jan 09, 2019 5:44 pm
by Villeroy
After all those years you should have learned some SQL.
I had to do some more debugging. The first query had a logical flaw.

Re: SQL obtain all lines ?

PostPosted: Wed Jan 09, 2019 6:52 pm
by arfgh
yes, i learned some, but i have pending the table union skills. My strong point is on macros...

Re: SQL obtain all lines ?

PostPosted: Wed Jan 09, 2019 8:37 pm
by Villeroy
But why can't you write a macro that parses a field of values?

Re: SQL obtain all lines ?

PostPosted: Wed Jan 09, 2019 11:02 pm
by arfgh
because in this case is a query for other uses, the results to be used in the own db creation, etc.
Examining your example...

Re: SQL obtain all lines ?

PostPosted: Thu Jan 10, 2019 12:40 am
by arfgh
Villeroy, yes, your first query is very intuitive. I got some near result but still persist the last problem i am having. Just having the 4 results in 4 columns, we have to do a last checking using 'distinct' and obtain a final single column with the distinct results from the 4 columns... I see that is a problem now....

Re: SQL obtain all lines ?

PostPosted: Fri Jan 11, 2019 5:56 pm
by arfgh
Villeroy, in the "S2" split calculation exist an error. When the "REM" has a single char(10) separing two lines, it fails.

I will get deep on it later...

Re: SQL obtain all lines ?

PostPosted: Fri Jan 11, 2019 7:14 pm
by Villeroy
Eliminate single line breaks
Code: Select all   Expand viewCollapse view
UPDATE "Data" SET "REM" = NULL WHERE "REM" = Char(10)

or add a WHERE clause to the query to handle all records ... WHERE "REM" != Char(10)

Re: SQL obtain all lines ?

PostPosted: Fri Jan 11, 2019 9:30 pm
by chrisb
Hi,
a couple of days after first browsing this topic i had a vague recollection of having tackled this issue before.
sure enough after having searched through some very old databases i found what i was looking for.

from memory i attempted a solution using the HSQL 1.8.0.10 functions 'LOCATE' & 'SUBSTR' as per Villeroy.
it's easy enough to extract substrings this way when they are small in number & we know the max number there may be.
unfortunately if we have more than 4 or 5 substrings the SQL becomes messy & difficult to read.
even if the substrings are successfully extracted we still have a problem in selecting distinct values & storing the results.
i concluded that using SQL alone was not a practical solution so wrote a macro which solved the issue.

to clarify:
we have a table field which contains a string of text.
the text contains substrings which are delimited using a separator.
if the separator was a comma then the string could look like this: 'Bill,Bob,Barry,Bartholomew,How Now Brown Cow'.
the separator can be any printable character. the OP uses char(10) (line feed). the data was likely input using a form with a multi line text box (as in the attachment).

how to use:
1) open the attachment. it contains one table, one form & one macro.
2) hit 'Tables' icon.
3) hit (Alt+F11) or menu:Tools>Macros>Organise Macros>Open Office Basic.
4) expand 'SplitString.odb'.
5) expand 'Standard'.
6) hit 'Module1'.
7) hit 'Edit'.
8) from keyboard hit 'F5' (execute).
9) from 'Base' main window hit>>> menu:View>Refresh Tables (only necessary when the created tables are not visible).
two newly created tables should now be visible.

to extract from your own tables:
i think it's best to drag your table and drop it into this database.
go to the macro & edit the following as necessary: 'sSeparator', 'sSourceTableName', 'sSourceFieldName'.

Re: SQL obtain all lines ?

PostPosted: Sat Jan 12, 2019 2:02 pm
by arfgh
Villeroy, easier way to fix the problem on L2 is doing this:

Code: Select all   Expand viewCollapse view
   SELECT
      LOCATE( CHAR( 10 ), "Rem" ) AS "L1",
      LOCATE( CHAR( 10 ), "Rem", LOCATE( CHAR( 10 ), "Rem" ) + 1) AS "L2",
      LOCATE( CHAR( 10 ), "Rem", LOCATE( CHAR( 10 ), "Rem", LOCATE( CHAR( 10 ), "Rem" ) + 1) + 1) AS "L3",
      "ID", "Rem"
   FROM (
      select concat("Rem",char(10)) as "Rem", ID from "data"
   )


just adding a last char(10). Then L2 give the good S2 string start. And i have seen no useful the ABS() usage... ?

Also Villeroy, your example gave me the way to use that table joins to again obtain a single column from 4 as data source, thx for such example :)
But getting deep on it, still there are some duplicates, and i cant understand why. Maybe because the same string come from non S1 column ? but even using 'distinct', that seems there is no needed in your example, doesnt change the fact that in the final results column, appears some duplicates.....

And also Villeroy, why you did use the table view instead of a single query ? i guess you did it to dont have to repeat several times parts of the code, please confirm.

chrisb, thx for your example but, in this case the wanted is a single SQL query to obtain all distinct values. In SQL the table joins skills are needed to achieve it. In basic is it easy...