[Solved] A second line per record- sometimes!
[Solved] A second line per record- sometimes!
PS- Eventually an answer was found. Near the bottom one of my posts includes a downloadable copy of the database, with "answer".
===== Original first post from here....
Imagine that I have a REALLY simple table, as follows. Data about a book collection. To make this question concise, we'll take as a given that no title appears twice, and thus the title field can be the table's primary key.
Each record has two fields (text) The second, "Comment", is sometimes empty.
TITLE / COMMENT
The Hobbit / Second edition
Goblet of Fire
Yearling / Pristine dust jacket
The Spire
Winnie-The-Pooh
Seven Pillars of Wisdom
Moby Dick / Rockwell illustrations
My question: What approach would you take, to generate a report as below, please?
The Hobbit
-- Second edition
Goblet of Fire
Yearling
-- Pristine dust jacket
The Spire
Winnie-The-Pooh
Seven Pillars of Wisdom
Moby Dick
-- Rockwood illustrations
Note: No blank line in cases where there is no comment about a particular book.
===== Original first post from here....
Imagine that I have a REALLY simple table, as follows. Data about a book collection. To make this question concise, we'll take as a given that no title appears twice, and thus the title field can be the table's primary key.
Each record has two fields (text) The second, "Comment", is sometimes empty.
TITLE / COMMENT
The Hobbit / Second edition
Goblet of Fire
Yearling / Pristine dust jacket
The Spire
Winnie-The-Pooh
Seven Pillars of Wisdom
Moby Dick / Rockwell illustrations
My question: What approach would you take, to generate a report as below, please?
The Hobbit
-- Second edition
Goblet of Fire
Yearling
-- Pristine dust jacket
The Spire
Winnie-The-Pooh
Seven Pillars of Wisdom
Moby Dick
-- Rockwood illustrations
Note: No blank line in cases where there is no comment about a particular book.
Last edited by RoryOF on Mon Nov 13, 2017 7:18 pm, edited 4 times in total.
Reason: Added green tick [RoryOF, Moderator]
Reason: Added green tick [RoryOF, Moderator]
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
OT: A second line per record- sometimes!
A genuine Winnie-the-Pooh book would have EH Shepard illustrations,
The V&A museum, London will have a major Pooh exhibition
Winnie-the-Pooh: Exploring a Classic, V&A London, 9 December 2017 – 8 April 2018
The V&A museum, London will have a major Pooh exhibition
Winnie-the-Pooh: Exploring a Classic, V&A London, 9 December 2017 – 8 April 2018
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: A second line per record- sometimes!
I think Rory knew this, but, just in case...
For Winnie's record, there was no comment, i.e. nothing in the record's second field. And thus the report I want goes straight to the next book.
The "Rockwell Illus." applied only to the Moby Dick.
For Winnie's record, there was no comment, i.e. nothing in the record's second field. And thus the report I want goes straight to the next book.
The "Rockwell Illus." applied only to the Moby Dick.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
Re: A second line per record- sometimes!
What I meant was that a real Winnie-the-Pooh edition ought have EH Shepard illustrations and my implication was that the comment line to that effect had probably been omitted.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: A second line per record- sometimes!
Hi,
let's say your tables name is "Data", then this might be ok for you:
let's say your tables name is "Data", then this might be ok for you:
Code: Select all
SELECT
"RANK",
"TITLE"
FROM
(SELECT
COUNT (*) RANK,
TITLE,
COMMENT
FROM
"Data" X
LEFT JOIN "Data" Y
ON X.TITLE >= Y.TITLE
GROUP BY
X.TITLE,
X.COMMENT)
UNION SELECT
"RANK",
'-- '|| COMMENT
FROM
(SELECT
COUNT (*) RANK,
TITLE,
COMMENT
FROM
"Data" X
LEFT JOIN "Data" Y
ON X.TITLE >= Y.TITLE
GROUP BY
X.TITLE,
X.COMMENT)
WHERE
COMMENT IS NOT NULL
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: A second line per record- sometimes!
Thank F3K! After 40 minutes, of struggle, here's where I've got. (I haven't encountered RANK before)
Perhaps the fact that I am using Base 4.4.1 is relevant? I can find nothing about RANK in the "help". Posts here at the forum are mostly about databases with a "Rank" field, as in General/ Major/ Captain Lietenant.
ARGH! May have found the post that explains things... I am using the embedded HSQL engine. Sigh. Is there a way to "fix" things, without resorting to putting database in a different engine? Someone said...
(end of "argh"... returning to my adventure so far...)
The commented lines (in the code below) are an exampleof RANK I found at https://stackoverflow.com/
I was trying to get a simple instance of RANK working, to take me along the path of understanding your use. My table is called Books, and I've added a field, "Cost", to give SELECT RANK () an integer value to work on, in case that's the problem. I just get an error message when I try to run the query: "Data content could not be loaded. Access is denied: RANK in ((the rank stmt))"
I double checked the field name, and the case is as I've named the field.
I did manage a
... but the following refuses, as described. Ideas??
(I've tried selecting the menu item Edit -> Run SQL Command Directly before running the query)
Frustrated! Though I can't explain every bit of the much apprecicated answer, it "feels right" when I study it, follow through the bits I CAN follow through!
I have two books in the table which cost 5, and there are more than 5 books in the table. I assume (from unquoted bits at StackOverflow) that RANK, as I've used it, is looking for records with a 5 in the field "Cost"? But clearly I'm missing something, 'cause a list of the books costing 5 could be done witha simple "WHERE", couldn't it?
Perhaps the fact that I am using Base 4.4.1 is relevant? I can find nothing about RANK in the "help". Posts here at the forum are mostly about databases with a "Rank" field, as in General/ Major/ Captain Lietenant.
ARGH! May have found the post that explains things... I am using the embedded HSQL engine. Sigh. Is there a way to "fix" things, without resorting to putting database in a different engine? Someone said...
Something like the following can be used to generate a ranking table by counting how many values are bigger (or smaller).
Ranking primary key "ID" of a "Data" table by its "Value" column:
Code: Select all
Code: Select all Expand view
SELECT "D1"."ID","D1"."Value", COUNT("D2"."Value") AS "RANK"
FROM "Data" "D1" JOIN "Data" "D2" ON "D1"."Value"<="D2"."Value"
GROUP BY "D1"."ID","D1"."Value"
ORDER BY "RANK"
The commented lines (in the code below) are an exampleof RANK I found at https://stackoverflow.com/
I was trying to get a simple instance of RANK working, to take me along the path of understanding your use. My table is called Books, and I've added a field, "Cost", to give SELECT RANK () an integer value to work on, in case that's the problem. I just get an error message when I try to run the query: "Data content could not be loaded. Access is denied: RANK in ((the rank stmt))"
I double checked the field name, and the case is as I've named the field.
I did manage a
Code: Select all
SELECT "Title" FROM "Books" WHERE "Title" = 'The Hobbitt'
(I've tried selecting the menu item Edit -> Run SQL Command Directly before running the query)
Code: Select all
--SELECT RANK(1550) WITHIN GROUP
-- (ORDER BY salary, commission_pct) "Rank"
-- FROM employees;
SELECT
RANK(5) WITHIN GROUP
(ORDER BY Cost) "Rank"
FROM Books;
I have two books in the table which cost 5, and there are more than 5 books in the table. I assume (from unquoted bits at StackOverflow) that RANK, as I've used it, is looking for records with a 5 in the field "Cost"? But clearly I'm missing something, 'cause a list of the books costing 5 could be done witha simple "WHERE", couldn't it?
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
Re: A second line per record- sometimes!
MSPhobe, this should work as requested. replace the four instances of "My Table" with the name of your table.
a union query must be executed in direct mode >> menu:Edit>Run SQL command directly.NOTE: "RANK" is simply an assigned field name.
a union query must be executed in direct mode >> menu:Edit>Run SQL command directly.
Code: Select all
select TITLE from
(
select TITLE,
(select count(*) * 2 from "My Table" where TITLE < M.TITLE) RANK
from "My Table" M
union select
'-- ' || COMMENT,
(select count(*) * 2 + 1 from "My Table" where TITLE < M.TITLE)
from "My Table" M
where COMMENT is not null
)
order by RANK
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
Re: A second line per record- sometimes!
THANK YOU SO MUCH!... this was way above my pay grade, but something I was "ready" for... if only I could get through the "trivial" things getting in the way.
I've "tweaked" a few things, sometimes "unnecessarily"... in particular replaced "RANK" with "Place_In_Table". Sometimes "necessarily" to match my originally poorly described database. This to save the next newbie confusing the arbitrary column name for result of query with the "RANK" operator? function? found in some SQLs... but not the one embedded in Open Office.
Improvements to database description...
The table is called "Books". The fields are called "Title" and "Comments"... I unwisely put those words in upper case earlier in an attempt to convey that they were names for the columns. In the table, the field names were as said earlier in this paragraph. (And OO Base cares! "Comments" is not treated as if it were "COMMENTS", etc.)
I am working with the "standard" embedded HSQL, and OO 4.4.1 (Nov 17, on a Win7 machine.)
"The secrets", for anyone reading this thread later...
a) Once you are in the query window for your query, click "Edit" in the menu, and on the sub-menu that opens, be sure "Run SQL Command Directly" "button" is "down".
b) The following things matter, if the code below is going to work: The data must be in a table called "Books", the fields must be "Title" and "Comments". You will find that those names are case-sensitive, at least in some instances. Be safe! Use exactly the case all the time, and then you'll use it when it matters. (I apologise, by the way, if my case conventions are rubbish, and totally at odds with "normal" SQL programming.)
I think that's it. Please do post a reply if you find it doesn't work for you, or you spot something else a newbie would not "just know".
I've "tweaked" a few things, sometimes "unnecessarily"... in particular replaced "RANK" with "Place_In_Table". Sometimes "necessarily" to match my originally poorly described database. This to save the next newbie confusing the arbitrary column name for result of query with the "RANK" operator? function? found in some SQLs... but not the one embedded in Open Office.
Improvements to database description...
The table is called "Books". The fields are called "Title" and "Comments"... I unwisely put those words in upper case earlier in an attempt to convey that they were names for the columns. In the table, the field names were as said earlier in this paragraph. (And OO Base cares! "Comments" is not treated as if it were "COMMENTS", etc.)
I am working with the "standard" embedded HSQL, and OO 4.4.1 (Nov 17, on a Win7 machine.)
"The secrets", for anyone reading this thread later...
a) Once you are in the query window for your query, click "Edit" in the menu, and on the sub-menu that opens, be sure "Run SQL Command Directly" "button" is "down".
b) The following things matter, if the code below is going to work: The data must be in a table called "Books", the fields must be "Title" and "Comments". You will find that those names are case-sensitive, at least in some instances. Be safe! Use exactly the case all the time, and then you'll use it when it matters. (I apologise, by the way, if my case conventions are rubbish, and totally at odds with "normal" SQL programming.)
I think that's it. Please do post a reply if you find it doesn't work for you, or you spot something else a newbie would not "just know".
Code: Select all
select "Title" as "TitlePlusComment" from
(
select "Title",
(select count(*) * 2 from "Books" where "Title" < M."Title") Place_In_Table
from "Books" M
union select
'-- ' || "Comments",
(select count(*) * 2 + 1 from "Books" where "Title" < M."Title")
from "Books" M
where "Comments" is not null
)
order by Place_In_Table
- Attachments
-
- FDB028.odb
- FDB028-with query
- (3.77 KiB) Downloaded 418 times
Last edited by MSPhobe on Mon Nov 13, 2017 11:59 am, edited 3 times in total.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
Re: [SOLVED] A second line per record- sometimes!
PS...
See also...
viewtopic.php?f=42&t=87881&p=431752#p431752
The answer there may be the best answer for specifically what I was asking about... but the answer arrived at in the thread this is part of illustrates some interesting stuff of general usefulness.
See also...
viewtopic.php?f=42&t=87881&p=431752#p431752
The answer there may be the best answer for specifically what I was asking about... but the answer arrived at in the thread this is part of illustrates some interesting stuff of general usefulness.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: [SOLVED] A second line per record- sometimes!
The attachment shows how to accomplish this using very simple formulas in Calc instead of a complex query in Base. If the data originates in Base, use View → Data Sources in Calc and drag the data into the spreadsheet.MSPhobe wrote:What approach would you take, to generate a report as below, please? … Note: No blank line in cases where there is no comment about a particular book.
- Attachments
-
- 201711121620.ods
- (9.31 KiB) Downloaded 432 times
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: [SOLVED] A second line per record- sometimes!
MrProgrammer's speadsheet solution is very neat! Well worth a look at for all sorts of reasons. And small, compact, simple... looking at it can be a quick matter.
For a "one off", it would probably be the way to go. Especially if you were looking to do similar jobs more than once, with data from various sources.
In my want, a database will gradually fill with records, over years. From time to time, I will want a fresh hardcopy, with all of the new material along with the old.
Unless I am missing a way to build a button to deal with the transfer of the data to a (suitable) worksheet (macro?), I suspect that my ultimate solution may be something harder to build, but easier to use.
Not entirely because I am lazy. Having a simple way to do something reduces the chances of making mistakes while doing it.
The first time I tried, OO crashed... in a nastly way... but maybe it was going to happen anyway. Long time since it has happpened to me. (The reminder to back up restore points as you go along was worth the pain.)
Second attemp- worked just fine.
Another way? In OO Base, rt-click on table name, click "Copy".
Open spreadsheet. Right-click om a cell, click "paste"
For a "one off", it would probably be the way to go. Especially if you were looking to do similar jobs more than once, with data from various sources.
In my want, a database will gradually fill with records, over years. From time to time, I will want a fresh hardcopy, with all of the new material along with the old.
Unless I am missing a way to build a button to deal with the transfer of the data to a (suitable) worksheet (macro?), I suspect that my ultimate solution may be something harder to build, but easier to use.
Not entirely because I am lazy. Having a simple way to do something reduces the chances of making mistakes while doing it.
... is a neat way to do that. The database must be "registered" (not a big deal, but in case someone is trying this with an unregistered database.)If the data originates in Base, use View → Data Sources in Calc and drag the data into the spreadsheet.
The first time I tried, OO crashed... in a nastly way... but maybe it was going to happen anyway. Long time since it has happpened to me. (The reminder to back up restore points as you go along was worth the pain.)
Second attemp- worked just fine.
Another way? In OO Base, rt-click on table name, click "Copy".
Open spreadsheet. Right-click om a cell, click "paste"
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
Re: [SOLVED] A second line per record- sometimes!
A quick test with the flat "Bibliography.biblio" table, a text table with database fields and conditional paragraphs.
I added a couple of comments to the empty "Annote" field (4th field in the data source navigator) and printed a selection of 10 rows as a form letter (aka mail merge, aka serial letter)
I added a couple of comments to the empty "Annote" field (4th field in the data source navigator) and printed a selection of 10 rows as a form letter (aka mail merge, aka serial letter)
- Attachments
-
- biblio_conditional_out.odt
- (10.33 KiB) Downloaded 429 times
-
- biblio_conditional.odt
- (10.3 KiB) Downloaded 455 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] A second line per record- sometimes!
just a couple of observations regarding your project.
if a user finds it necessary to suppress the display of any row when using Report Builder then maybe that user should not have selected that row in the first place.
the reason for any complexity within the query is due to the fact that we are using the embedded database with HSQLDB 1.8.0.10 which is over 8 years old.
the embedded database is fine for demos etc but as we all know sooner or later it will fail resulting in possible data loss.
it is sensible to split any database which contains valuable data and upgrade the database engine.
the latest version of HSQLDB is 2.4.0 but as this version contains a bug which impacts the UNION function HSQLDB 2.3.4 may be a better option.
upgrading enables the use of a simplified query. this query depends on the field "Title" being retained as your primary key.
if a user finds it necessary to suppress the display of any row when using Report Builder then maybe that user should not have selected that row in the first place.
the reason for any complexity within the query is due to the fact that we are using the embedded database with HSQLDB 1.8.0.10 which is over 8 years old.
the embedded database is fine for demos etc but as we all know sooner or later it will fail resulting in possible data loss.
it is sensible to split any database which contains valuable data and upgrade the database engine.
the latest version of HSQLDB is 2.4.0 but as this version contains a bug which impacts the UNION function HSQLDB 2.3.4 may be a better option.
upgrading enables the use of a simplified query. this query depends on the field "Title" being retained as your primary key.
Code: Select all
select "Title" "TitlePlusComment" from
(
select "Title", ROWNUM() * 2 "Place_In_Table" from "Books"
union select '-- ' || "Comments", ROWNUM() * 2 + 1 from "Books"
)
where "Title" is not null
order by "Place_In_Table"
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
Re: [Solved] A second line per record- sometimes!
Code: Select all
SELECT "Title" || COALESCE(CHAR(13) || '--' || "Comments",'') AS "X" FROM "Books"
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice