[Solved] A second line per record- sometimes!

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

[Solved] A second line per record- sometimes!

Post by MSPhobe »

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.
Last edited by RoryOF on Mon Nov 13, 2017 7:18 pm, edited 4 times in total.
Reason: Added green tick [RoryOF, Moderator]
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

OT: A second line per record- sometimes!

Post by RoryOF »

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
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: A second line per record- sometimes!

Post by MSPhobe »

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.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: A second line per record- sometimes!

Post by RoryOF »

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
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: A second line per record- sometimes!

Post by F3K Total »

Hi,
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
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: A second line per record- sometimes!

Post by MSPhobe »

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...
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"
(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

Code: Select all

SELECT "Title" FROM "Books" WHERE "Title" = 'The Hobbitt'
... but the following refuses, as described. Ideas??

(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;
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?
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: A second line per record- sometimes!

Post by chrisb »

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.

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
NOTE: "RANK" is simply an assigned field name.
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
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: A second line per record- sometimes!

Post by MSPhobe »

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".

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 337 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
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: [SOLVED] A second line per record- sometimes!

Post by MSPhobe »

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.
OpenOffice 4.1.1 on Windows 7, plus others on XP. "Native", and as FE to MySQL
User avatar
MrProgrammer
Moderator
Posts: 4883
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [SOLVED] A second line per record- sometimes!

Post by MrProgrammer »

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.
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.
Attachments
201711121620.ods
(9.31 KiB) Downloaded 342 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).
User avatar
MSPhobe
Posts: 93
Joined: Tue Jan 15, 2013 6:05 pm
Location: Usually UK, sometimes USA(CT)

Re: [SOLVED] A second line per record- sometimes!

Post by MSPhobe »

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.
If the data originates in Base, use View → Data Sources in Calc and drag the data into the spreadsheet.
... 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.)

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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED] A second line per record- sometimes!

Post by Villeroy »

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)
Attachments
biblio_conditional_out.odt
(10.33 KiB) Downloaded 346 times
biblio_conditional.odt
(10.3 KiB) Downloaded 367 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
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] A second line per record- sometimes!

Post by chrisb »

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.

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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] A second line per record- sometimes!

Post by Villeroy »

Code: Select all

SELECT "Title" || COALESCE(CHAR(13) || '--' || "Comments",'') AS "X" FROM "Books"
shows the conditional 2nd line in old style reports and in Calc.
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
Post Reply