Re: SQL - UNION statement

suisum_at_ecn.ab.ca schrieb:
>
> Hi all:
>
> Anyone know what the purpose of the following SQL statement? Why we need
> UNION?
>
> CURSOR td_curs IS
> SELECT sum(expen_amount) expen_amount,
> e.disb_no,
> d.description
> from expense e,
> descriptions d,
> disb_table dt
> where e.client_no = :top.client_no
> and e.matter_no = :top.matter_no
> and e.new_journal_type = 'XX'
> and e.disb_no = '000'
> and e.disb_code = d.record_no
> and e.tran_date <= to_date(:TOP.RE_DATE, :TOP.DATE_FORMAT)
> and e.disb_no = dt.disb_no
> group by e.disb_no, description
> UNION
> SELECT sum(expen_amount) expen_amount,
> e.disb_no,
> dt.disb_descrip
> from expense e,
> descriptions d,
> disb_table dt
> where e.client_no = :top.client_no
> and e.matter_no = :top.matter_no
> and e.new_journal_type = 'XX'
> and e.disb_no != '000'
> and e.disb_code = d.record_no
> and e.tran_date <= to_date(:TOP.RE_DATE, :TOP.DATE_FORMAT)
> and e.disb_no = dt.disb_no
> group by e.disb_no, dt.disb_descrip
> order by 2;
>
> --
> Best regards,

As far as I understood the statement, you need the union, since for e.disb_no = '000' description is taken from table 'descriptions ' and for
e.disb_no != '000' description is taken from 'disb_table'. under circumstances you can speed up your query:

• expen_amount is a column of the table 'expense'
  • it always exists a row in 'disb_table' where disb_no = '000' then you can ommit the table 'disb_table' in the from clause of the first part of your union as well as in the where clause.
  • there are no records in 'expense' having a disb_code that doesn't exists in the record_no column of the table 'descriptions'. then you can ommit the table 'descriptions' in the from clause of the second part of your union as well as in the where clause.

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Tue Dec 08 1998 - 04:20:41 CST