Hi,
I'd like to build a report based on a complicated request, like :
SELECT someting FROM table t
WHERE t.a = (SELECT ........) OR t.a = (SELECT ......) AND t.b = (SELECT.......)
etc...
Could I store the (select ......) results into variables and use the variables in my request ?
(some of these select would need to be duplicated in my request so using vars would remove the duplicate selects)
Thanks !
[Solved] Split report request ?
-
- Posts: 16
- Joined: Fri Feb 20, 2015 10:58 pm
[Solved] Split report request ?
Last edited by contremaitre on Tue Mar 17, 2015 10:45 pm, edited 1 time in total.
Openoffice 4.3 Kubuntu 14.10
Re: Split report request ?
Have you been investigating the possibility to use (left) joins i.s.o. the given approach? Many people might help you to rewrite your query and reduce the complexity, so please give a full example of what you like to achieve.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 16
- Joined: Fri Feb 20, 2015 10:58 pm
Re: Split report request ?
Ok here is a sample :
Code: Select all
SELECT "B"."Total", "A"."Marque", "A"."Largeur", "A"."Hauteur", "A"."Diametre", "A"."Charge", "A"."Vitesse"
FROM "tPneus" "A"
INNER JOIN
( SELECT SUM( "tSorties"."QteSortie" ) "Total", "tSorties"."RefPneu"
FROM "tSorties"
WHERE
( "tSorties"."DteSortie" > ( SELECT "DateDébut" FROM "tRapport" )
OR ( SELECT "DateDébut" FROM "tRapport" ) IS NULL )
AND
( "tSorties"."DteSortie" > ( SELECT "DateFin" FROM "tRapport" )
OR ( SELECT "DateFin" FROM "tRapport" ) IS NULL )
GROUP BY "tSorties"."RefPneu" ) "B"
ON "A"."IdPneu" = "B"."RefPneu"
Openoffice 4.3 Kubuntu 14.10
Re: Split report request ?
Your query will only work when "tRapport" contains one and only one row. With this fact we can change the ( SELECT SUM(.... ) ) "B" into
The trick is that the FROM "tSorties", "tRapport" results in a Cartesian or Cross join.
An other way to write this (assume that 1900-01-01 can be used as below the lowest date for DteSortie:
Code: Select all
SELECT SUM( "tSorties"."QteSortie" ) "Total", "tSorties"."RefPneu"
FROM "tSorties", "tRapport"
WHERE
( "tSorties"."DteSortie" > "tRapport"."DateDébut"
OR "tRapport"."DateDébut" IS NULL )
AND
( "tSorties"."DteSortie" > "tRapport"."DateFin"
OR "tRapport"."DateFin" IS NULL )
GROUP BY "tSorties"."RefPneu"
An other way to write this (assume that 1900-01-01 can be used as below the lowest date for DteSortie:
Code: Select all
SELECT SUM( "tSorties"."QteSortie" ) "Total", "tSorties"."RefPneu"
FROM "tSorties", (
SELECT CASE WHEN
COALESCE( "tRapport"."DateDébut", '1900-01-01')
> COALESCE("tRapport"."DateFin", '1900-01-01')
THEN
COALESCE( "tRapport"."DateDébut", '1900-01-01')
ELSE
COALESCE( "tRapport"."DateFin", '1900-01-01')
END AS "FilterDate"
FROM "tRapport" ) "X"
WHERE "tSorties"."DteSortie" > "X"."FilterDate"
GROUP BY "tSorties"."RefPneu"
Last edited by eremmel on Tue Mar 17, 2015 10:57 pm, edited 3 times in total.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 16
- Joined: Fri Feb 20, 2015 10:58 pm
Re: Split report request ?
tRapport contains one and only one row because this is a table used to store filter parameters for my report request.
Thanks for the request simplification !
Thanks for the request simplification !
Openoffice 4.3 Kubuntu 14.10