SQL obtain all lines ?

Creating tables and queries
Post Reply
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

SQL obtain all lines ?

Post by arfgh »

hey there friends.

taking in consideration this simple query:

Code: Select all

select distinct "Colors" from "Items"
"Colors" may have 1 or several lines with colors into, exmple:

Code: Select all

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
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Post by Villeroy »

HSQL 2:

Code: Select all

SELECT REPLACE(GROUP_CONCAT("Text"),',',CHAR(10))AS "Concat" FROM "Table"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Post 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

SELECT distinct left("Colors",locate(char(10),"Colors")) FROM "Items"
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL obtain all lines ?

Post 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

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".
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Post 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.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Post 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

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

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.
Last edited by Villeroy on Wed Jan 09, 2019 5:46 pm, edited 3 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Post 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

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Post 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.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Post 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.
Attachments
Split_Remarks.odb
Split field of multi-line text into separate values
(12.34 KiB) Downloaded 217 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Post by arfgh »

yes, i learned some, but i have pending the table union skills. My strong point is on macros...
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Post by Villeroy »

But why can't you write a macro that parses a field of values?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Post 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...
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Post 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....
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Post 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...
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Post by Villeroy »

Eliminate single line breaks

Code: Select all

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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: SQL obtain all lines ?

Post 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'.
Attachments
SplitString.odb
(13.26 KiB) Downloaded 225 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Post by arfgh »

Villeroy, easier way to fix the problem on L2 is doing this:

Code: Select all

   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...
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
Post Reply