Trouble with UNION for two queries

Beaver2 · October 8, 2024, 8:27am

I want to generate a report that is a union of two queries. Each query works just fine by itself and returns the results I expect. I’ve carefully checked that all the column names match in the final output of each query. When I try to put them together I just can’t get it to work. Any help would be much appreciated, and an explanation of what’s going on under the hood would also help me with learning Base!

QUERY 1:

SELECT null as “job_id”, null as “task_id”, null as “task_part_id”, null as “sequence_number”, null as “material”, “Tool”.“name” as “part_description”, null as “length”, null as “width”, null as “quantity”, null as “receive_direct_to_installation”, null as “source”, “StorageLocation”.“name” as “location”, null AS “weight”, 0 as “running_total”, 10 AS “category”, null AS “material_source_id” FROM “Bring” join “Tool” on “Bring”.“tool_id”=“Tool”.“id” join “StorageLocation” on “Tool”.“storage_location_id”=“StorageLocation”.“id” where “Bring”.“programme_id”= :programme_id

QUERY 2:

SELECT “v”.“job_id”, “v”.“task_id”, “v”.“task_part_id”, “v”.“sequence_number”, “v”.“material”, “v”.“part_description”, “v”.“length”, “v”.“width”, “v”.“quantity”, “v”.“receive_direct_to_installation”, “v”.“source”, “v”.“location”, “v”.“length” * “v”.“width” * “v”.“depth” * “v”.“density” * “v”.“quantity” / 1000000 AS “weight”, “r”.“running_total”, CEILING( “r”.“running_total” / “c”.“coefficient” ) AS “category”, “MaterialSource”.“id” AS “material_source_id” FROM “query_ManufacturingListSetup” “v” JOIN ( SELECT “coefficient” FROM “Coefficient” WHERE “id” = 4 ) “c” ON 1 = 1 JOIN ( SELECT “v1”.“sequence_number”, “v1”.“job_position_in_programme”, SUM( “v2”.“w” ) AS “running_total” FROM ( SELECT “sequence_number”, “job_position_in_programme” FROM “query_ManufacturingListSetup” ) “v1” JOIN ( SELECT “sequence_number”, “job_position_in_programme”, “length” * “width” * “depth” * “density” * “quantity” / 1000000 AS “w” FROM “query_ManufacturingListSetup” ) “v2” ON ( “v1”.“job_position_in_programme” > “v2”.“job_position_in_programme” ) OR ( “v1”.“job_position_in_programme” = “v2”.“job_position_in_programme” AND “v1”.“sequence_number” >= “v2”.“sequence_number” ) GROUP BY “v1”.“job_position_in_programme”, “v1”.“sequence_number” ) “r” ON “v”.“sequence_number” = “r”.“sequence_number” AND “v”.“job_position_in_programme” = “r”.“job_position_in_programme” INNER JOIN “TaskPart” ON “v”.“task_part_id” = “TaskPart”.“id” INNER JOIN “Task” ON “TaskPart”.“task_id” = “Task”.“id” INNER JOIN “Job” ON “Task”.“job_id” = “Job”.“id” INNER JOIN “TaskPartType” ON “TaskPart”.“task_part_type_id” = “TaskPartType”.“id” LEFT JOIN “MaterialSource” ON “TaskPart”.“material_source_id” = “MaterialSource”.“id” WHERE “Job”.“programme_id” = :programme_id AND “TaskPart”.“imaginary” = FALSE AND NOT “TaskPartType”.“supplier_id” IN ( 7 ) AND NOT ( “TaskPartType”.“supplier_id” IN ( 1, 2 ) AND “TaskPart”.“length” <= 2000 AND “TaskPart”.“width” <= 620 ) AND NOT ( “TaskPartType”.“supplier_id” NOT IN ( 1, 2, 9 ) AND ( “TaskPart”.“delivery_date” IS NULL OR “TaskPart”.“delivery_date” > CURRENT_DATE ) ) AND ( “category” < 3 OR ( “category” > 2 AND ( “TaskPart”.“delivery_date” IS NULL OR “TaskPart”.“delivery_date” < CURRENT_DATE ) ) ) OR ( “receive_direct_to_installation” = TRUE AND NOT “v”.“location” = ‘Van pigeonhole long shelves’ ) AND “TaskPart”.“material_source_id” <> 30 ORDER BY “category” ASC, “running_total” DESC

RobertG · October 8, 2024, 8:36am

First query contains a parameter. So you have to execute this query through GUI. But UNION could only be executed if you press “Execute SQL directly”. Set the parameter by a table instead. I call this kind of table tbl_Filter. Should only show one row, for you for example “ID” (boolean) and “programme_id”. So you could write
(SELECT "programme_id" FROM "tbl_Filter" WHERE "ID" = TRUE) instead of :programme_id

Beaver2 · October 11, 2024, 9:19am

Thanks for this Robert, since I need to use this query regularly I think I might set it up in a form using a table “filter_Programme” as you suggest. Hopefully I should then be able to get it to return results from both the tables and then print it off (a hard-copy print-out is what I need to end up with).

Beaver2 · October 22, 2024, 9:58am

I just wanted to say that I’ve gotten this form up and running, and it works fine. The limitation is that I can only print off one page, but thankfully the data involved doesn’t exceed that in any of the use cases I have.

Wanderer · October 23, 2024, 5:29am

Did you create a report with above query or how do you print? Directly from the query?
.
In some cases I drag the query into a Calc-sheet and modify the data for print in a second one.

Beaver2 · October 23, 2024, 6:33am

I made a form which has “filter_Programme” as the main form. I then ran two sub-forms off that with table controls to display the output of the two queries. Since I can fit it all onto one page I’m then able to print off a hard-copy and use it on-site (I’m a carpenter). I’ve not found a way to directly print off a query, although I’ve definitely had situations where it would be handy and I’d love to know how to do it!