SQL obtain all lines ?

Creating tables and queries

SQL obtain all lines ?

Postby arfgh » Sat Jan 05, 2019 4:04 pm

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
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Postby Villeroy » Sat Jan 05, 2019 4:20 pm

HSQL 2:
Code: Select all   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Postby arfgh » Sat Jan 05, 2019 4:35 pm

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"
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Postby UnklDonald418 » Sat Jan 05, 2019 6:22 pm

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".
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.6 & LibreOffice 6.1.1.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1005
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SQL obtain all lines ?

Postby Villeroy » Sat Jan 05, 2019 8:04 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Postby arfgh » Sun Jan 06, 2019 1:32 pm

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 - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Postby Villeroy » Tue Jan 08, 2019 1:33 pm

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.
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Postby Villeroy » Tue Jan 08, 2019 4:55 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Postby arfgh » Wed Jan 09, 2019 3:17 pm

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 - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Postby Villeroy » Wed Jan 09, 2019 5:44 pm

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 3 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Postby arfgh » Wed Jan 09, 2019 6:52 pm

yes, i learned some, but i have pending the table union skills. My strong point is on macros...
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Postby Villeroy » Wed Jan 09, 2019 8:37 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Postby arfgh » Wed Jan 09, 2019 11:02 pm

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 - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Postby arfgh » Thu Jan 10, 2019 12:40 am

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 - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Postby arfgh » Fri Jan 11, 2019 5:56 pm

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 - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm

Re: SQL obtain all lines ?

Postby Villeroy » Fri Jan 11, 2019 7:14 pm

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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26117
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQL obtain all lines ?

Postby chrisb » Fri Jan 11, 2019 9:30 pm

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 3 times
open office 4.1.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 176
Joined: Mon Jun 07, 2010 4:16 pm

Re: SQL obtain all lines ?

Postby arfgh » Sat Jan 12, 2019 2:02 pm

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...
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 403
Joined: Tue Mar 05, 2013 6:44 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests