[Solved] Split report request ?

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

[Solved] Split report request ?

Post by contremaitre »

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 !
Last edited by contremaitre on Tue Mar 17, 2015 10:45 pm, edited 1 time in total.
Openoffice 4.3 Kubuntu 14.10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Split report request ?

Post by eremmel »

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)
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Split report request ?

Post by contremaitre »

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Split report request ?

Post by eremmel »

Your query will only work when "tRapport" contains one and only one row. With this fact we can change the ( SELECT SUM(.... ) ) "B" into

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" 
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", (
  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)
contremaitre
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Split report request ?

Post by contremaitre »

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 !
Openoffice 4.3 Kubuntu 14.10
Post Reply