[Solved] Split report request ?

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Split report request ?

Postby contremaitre » Tue Mar 17, 2015 8:46 pm

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

Re: Split report request ?

Postby eremmel » Tue Mar 17, 2015 9:42 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1029
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Split report request ?

Postby contremaitre » Tue Mar 17, 2015 9:50 pm

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"
Openoffice 4.3 Kubuntu 14.10
contremaitre
 
Posts: 16
Joined: Fri Feb 20, 2015 10:58 pm

Re: Split report request ?

Postby eremmel » Tue Mar 17, 2015 10:30 pm

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" 
Last edited by eremmel on Tue Mar 17, 2015 10:57 pm, edited 3 times in total.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1029
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Split report request ?

Postby contremaitre » Tue Mar 17, 2015 10:44 pm

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


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest