Page 1 of 1

[Solved] Split report request ?

PostPosted: Tue Mar 17, 2015 8:46 pm
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 !

Re: Split report request ?

PostPosted: Tue Mar 17, 2015 9:42 pm
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.

Re: Split report request ?

PostPosted: Tue Mar 17, 2015 9:50 pm
by contremaitre
Ok here is a sample :
Code: Select all   Expand viewCollapse view
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"

Re: Split report request ?

PostPosted: Tue Mar 17, 2015 10:30 pm
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   Expand viewCollapse view
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   Expand viewCollapse view
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" 

Re: Split report request ?

PostPosted: Tue Mar 17, 2015 10:44 pm
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 !