I try your query but stop in: USING
and this is the error I'm getting.
SQL error code = -104.
Token unknown - line 3, column 34.
using.

what I'm tryign to do is make only one query so then I can have a pagination because the way it is right now is kind of complicate to make
a pagination with this three different queries I did not wrote the queries
I'm trying to fix the report is 390 pages and I need to paginate in away that will be easy to go through and view it.

thanks a lot for you help.


David

--- On Thu, 9/17/09, masotti <masotti@...> wrote:

> From: masotti <masotti@...>
> Subject: Re: [firebird-php] SQL query help to generate tota - subtotal and grand total
> To: firebird-php@yahoogroups.com
> Date: Thursday, September 17, 2009, 3:51 AM
> Hi David,
>
> David ha scritto:
> > $Q1 = ibase_query ("select distinct(SERVE_DATE) from
> TRANS t inner join  STUDENT s on t.STUD_NO=s.STUD_NO
> where t.GENE=0 and t.ORDERED=1 $AndFromDate $AndToDate order
> by SERVE_DATE");
> >
> > $Q2 = ibase_query ("select distinct(AM_ROOM) from
> STUDENT");
> >
> > $Q3 = ibase_query ("select t.STUD_NO, s.LAST_NAME ,
> s.FIRST_NAME, s.GRADE  from TRANS t inner join 
> STUDENT s on t.STUD_NO=s.STUD_NO where t.GENE=0 and
> t.ORDERED=1 and s.AM_ROOM='$R2[0]' and t.SERVE_DATE='$R1[0]'
> $AndFromDate $AndToDate group by t.STUD_NO, s.LAST_NAME,
> s.FIRST_NAME, s.GRADE, t.SERVE_DATE  order by
> t.SERVE_DATE, s.LAST_NAME, s.FIRST_NAME");
> >
> > $Q4 = ibase_query ("select DESCRIPTION, sum(QUANTITY)
> from TRANS where STUD_NO=$R3[0] and GENE=0 AND ORDERED=1 and
> SERVE_DATE='$R1[0]' group by DESCRIPTION order by
> DESCRIPTION");
> >
> >   
> I'm not sure, perhaps you can obtain what you need using
> Firebird 2.1,
> with a COMMON TABLE EXPRESSION
>
> http://www.firebirdsql.org/rlsnotesh/rlsnotes210.html#rnfb210-cte
>
> and/or DERIVED TABLES
>
> http://www.firebirdsql.org/rlsnotesh/rlsnotes210.html#rnfb20x-dml-derived-tables
>
> Using only DERIVED TABLES, you don't need $q1 and $q2,
> because
> conmditions and tables are anyway in $q3.
> I'm not sure about GROUP BY in $q3: what are you tryinng to
> do?
> Something like (writing without testing)
>
> select t.STUD_NO, s.LAST_NAME , s.FIRST_NAME, s.GRADE,
> t.SERVE_DATE, R.DESCRIPTION, R.QTY
> from
>   (TRANS t inner join  STUDENT s USING
> (STUD_NO))
>   join
>   (SELECT STUD_NO, GENE, ORDERED, SERVE_DATE,
> DESCRIPTION, sum(QUANTITY)
>     from TRANS
>     group by 1, 2, 3, 4, 5
>   ) AS R (STUD_NO, GENE, ORDERED, SERVE_DATE,
> DESCRIPTION, QTY )
>   USING ( STUD_NO, GENE, ORDERED, SERVE_DATE )
> where t.GENE=0 and t.ORDERED=1 $AndFromDate $AndToDate
> order by t.SERVE_DATE, s.LAST_NAME, s.FIRST_NAME,
> description
>
> Ciao.
> Mimmo.
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>     mailto:firebird-php-fullfeatured@yahoogroups.com
>
>
>