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