Reordering query results according to a calculated field

English
#1

I’ve updated the post in response to RobertG, I’d inadvertently uploaded the wrong version of the database. I’ve also copied into the main question the SQL code of the query that is functional.

Hello, I’m using LibreOffice 7.5.7.1 and I’m building a database for my carpentry business. I’ve got a database structure more or less worked out, and I’ve loaded it with a little bit of test data to help me figure out my queries. I’m very much a novice, and I’ve been using ChatGPT to help me write queries.

JARVISTest.odb (44.3 KB)

My particular question is how to reorganise a query that I’ve written (ChatGPT really) such that entries with “category”=2 come first, ordered by “label” descending, then “category”=3 come second, ordered by “label” descending, then “category”=4 come third, ordered by “label” descending, and then “category”=1 entries are listed last, ordered by “label” ascending. The query is called “query_PowellsLoadFromCoefficient”, and the SQL is all on the database as well as below. As a note, programme_id=2 is the programme that has some test data associated with it.

SELECT “TaskPart”.“length”, “TaskPart”.“width”, “TaskPart”.“quantity”, “TaskPartType”.“name” AS “material”, “TaskPart”.“id” AS “label”, ( “TaskPart”.“length” * “TaskPart”.“width” * “TaskPartType”.“depth” * “Density”.“density” * “TaskPart”.“quantity” ) / 1000000 AS “weight”, ( SELECT SUM( ( “tp”.“length” * “tp”.“width” * “tpt”.“depth” * “d”.“density” * “tp”.“quantity” ) / 1000000 ) FROM “TaskPart” AS “tp” INNER JOIN “TaskPartType” AS “tpt” ON “tp”.“task_part_type_id” = “tpt”.“id” INNER JOIN “Density” AS “d” ON “tpt”.“density_id” = “d”.“id” INNER JOIN “Task” AS “t” ON “tp”.“task_id” = “t”.“id” INNER JOIN “Job” AS “j” ON “t”.“job_id” = “j”.“id” INNER JOIN “Programme” AS “p” ON “j”.“programme_id” = “p”.“id” WHERE “tpt”.“supplier_id” = 1 AND “p”.“id” = :programme_id AND “tp”.“id” <= “TaskPart”.“id” ) AS “running_total”, CEILING( ( SELECT SUM( ( “tp”.“length” * “tp”.“width” * “tpt”.“depth” * “d”.“density” * “tp”.“quantity” ) / 1000000 ) FROM “TaskPart” AS “tp” INNER JOIN “TaskPartType” AS “tpt” ON “tp”.“task_part_type_id” = “tpt”.“id” INNER JOIN “Density” AS “d” ON “tpt”.“density_id” = “d”.“id” INNER JOIN “Task” AS “t” ON “tp”.“task_id” = “t”.“id” INNER JOIN “Job” AS “j” ON “t”.“job_id” = “j”.“id” INNER JOIN “Programme” AS “p” ON “j”.“programme_id” = “p”.“id” WHERE “tpt”.“supplier_id” = 1 AND “p”.“id” = :programme_id AND “tp”.“id” <= “TaskPart”.“id” ) / ( SELECT “coefficient” FROM “Coefficient” WHERE “id” = 6 ) ) AS “category” FROM “TaskPart” INNER JOIN “TaskPartType” ON “TaskPart”.“task_part_type_id” = “TaskPartType”.“id” INNER JOIN “Density” ON “TaskPartType”.“density_id” = “Density”.“id” INNER JOIN “Task” ON “Task”.“id” = “TaskPart”.“task_id” INNER JOIN “Job” ON “Task”.“job_id” = “Job”.“id” INNER JOIN “Programme” ON “Job”.“programme_id” = “Programme”.“id” WHERE “TaskPartType”.“supplier_id” = 1 AND “Programme”.“id” = :programme_id ORDER BY “TaskPart”.“id”

I’ve asked ChatGPT to write the necessary SQL for me, but it’s SQL doesn’t work and I couldn’t work out how to fix it. I can also post that SQL on here if that’s useful.

The reason this query needs to be reordered is that it dictates how how I sort my sheet materials into sequenced loads for me to take to site (my van can only carry so much!). I think it’s feasible for me to import this data into a spreadsheet and do what I need to do there, but I’d rather keep it all in the database if at all possible so as to avoid user error.

#2

There is no query with this name in the example database. Please show the code, which you (or ChatGPT) created.

I have never used ChatGPT. Use the documentation for Base: Base Guide 7.2

#3

@Beaver2,
I think that we are talking about the query “query_VanLoadWorking”.
.
I assume that you are happy with the result set and simply wish to re-order the contents.
to be honest I don’t understand the logic.
.
the structure and composition of your query is absolutely appalling!
.
I have added 4 queries to your database:
“qVanloadSetup_sql” and “qVanLoadOutput_sql” are saved in direct mode and are included to assist readability.
“qVanloadSetup” is the data-source for “qVanLoadOutput”, they are duplicates of the above and are saved in design view.
.
just double click on “qVanLoadOutput”.
Response1.odb (45.5 KB)

#4

Thanks so much for this, it all works!

I’m afraid I made a mistake when I first posted and put the wrong version of the database, hence why the query that I was talking about was not actually there. I’ve put the right database version on the original post now, which contains the right query (“query_PowellsLoadFromCoefficient”). The only difference is that rather than the thresholds that increment at 250 intervals being hard-coded into the query it instead takes the data from the “Coefficient” table, entry #6 which has a value of “250” in the “coefficient” column. I wanted to do it this way in case I needed to update the value of “250” in the future (it relates to the payload I can carry in my van).

Would you be happy to update the queries you very kindly wrote for me to incorporate the coefficient information that I missed out in the first place?

#5

right click on query “qVanLoadOutput”
select ‘Edit in SQL View’
delete the contents
paste this code
save
.
repeat for “qVanLoadOutput_sql”
before saving ensure menu:>Edit>‘Run SQL command directly’ is selected (preserves formatting)

select
	"v"."programme_id", "v"."job_id", "v"."length", "v"."width", "v"."quantity", "v"."name" "material", "v"."task_part_id",
	"v"."length" * "v"."width" * "v"."depth" * "v"."density" * "v"."quantity" / 1000000 "weight",
	"r"."running_total",
	ceiling("r"."running_total" / "c"."coefficient") "category"
from
	"qVanLoadSetup" "v",
	(select "coefficient" from "Coefficient" where "id" = 6) "c"
join
	(
		select
			"v1"."programme_id", "v1"."job_id", "v1"."task_part_id", sum("v2"."w") "running_total"
		from
		(	select "programme_id", "job_id", "task_part_id" from "qVanLoadSetup") "v1"
			join
			(
				select "programme_id", "job_id", "task_part_id", "length" * "width" * "depth" * "density" * "quantity" / 1000000 "w"
 				from "qVanLoadSetup"
			) "v2"
				on "v1"."programme_id" = "v2"."programme_id" and "v1"."job_id" = "v2"."job_id" and "v1"."task_part_id" >= "v2"."task_part_id"
		group by
			"v1"."programme_id", "v1"."job_id", "v1"."task_part_id"
	) "r"
	on "v"."programme_id" = "r"."programme_id"
	and "v"."job_id" = "r"."job_id"
	and "v"."task_part_id" = "r"."task_part_id"

order by
	case when "category" > 1 then "category" else "category" + 50 end,
	case when "category" > 1 then -"task_part_id" else "task_part_id" end
#6

Thank you, that’s brilliant!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.