Re: Technical clarification in using UNION with If condition

[Arjan Hoornstra <ahoor@YAHOO.COM>]

The main problem, apart from what it is you want, is that the if
statement includes the from clauses. This means that is the IF is false
there is no from and where clause which of course results in a syntax
error.
I agree on using split up procedures, depending on the IF which you
call and which not...

Arjan

--- Dipu Sudhakaran <dipu@FOCUS5.NET> wrote:
> Hello All
> We are using brioreports with our application for generating online
> reports.Our application  is Java Based.
> presently we are working with label printing .In this the sqr is
> generating
> a label  for every user .There is no problem in the label as long as
> one
> record  represents one user .if multiple rows represents a single
> user is
> giving problem that is for every row it is printing a label.So to
> overcome
> this problem we used ' if ' condition to check the user ids. So the
> program
> will not print the label as long as the subsequent rows are same.If
> condition works fine for join SQL query but not for the union.Below
> is the
> code.please send me the reply ASAP for the same.If you have any other
> solution for the same requirement please let us know .
>
> begin-program
> !let $cmm ='type: conference' aaa ,
>   let $oldregid =0
> !  let $query ='
> do union_test
> end-program
>
> begin-heading 2
> print 'DEMO NUMBER' (,1)
> print 'GROUP ID' (,32)
> print 'FIRSTNAME'(,49)
> end-heading
>
> begin-footing 1
> page-number (1,1) 'Page'
> last-page () ' of '
> end-footing
>
> begin-procedure union_test
>
> begin-select
>   Next-Listing
> c.demo_no (0,15,8)
>   let $regid=&C.DEMO_NO
>
>  if($oldregid=$regid)
> 'type: conference' aaa (0,25,15)
> '' (0,45,10)
>
> substr(cp.reg_gp_nme,1,6) (0,75,10)
> FROM confn c,confn_profl cp,demo d
> where
> c.company_no=20005403 and c.event_id=2026 and c.demo_no='001015NL'
> AND c.lang = cp.lang
> AND c.company_no = cp.company_no
> AND c.event_id  = cp.event_id
> AND c.confn_cde  = cp.confn_cde
> UNION
> SELECT
>
> f.demo_no,
> 'type: function' aaa,
> '',
>
> substr(fp.func_des,1,6)
> FROM function f, func_profl fp,demo d
> where
> f.company_no=20005403 and f.event_id=2026 and f.demo_no='001015NL'
> AND f.lang = fp.lang
> AND f.company_no = fp.company_no
> AND f.event_id  = fp.event_id
> AND f.func_cde  = fp.func_cde
> UNION
> SELECT
> h.demo_no,
> 'type: hotel'aaa,
> d.last_nme,
>
> substr(hp.hotel_nme,1,6)
> FROM hotel h, hotel_profl hp, demo d
> WHERE
> h.company_no=20005403 and h.event_id=2026 and h.demo_no='001015NL'
> AND h.lang = hp.lang
> AND h.company_no = hp.company_no
> AND h.event_id  = hp.event_id
> AND h.hotel_cde  = hp.hotel_cde
> AND h.company_no = d.company_no
> AND h.event_id  = d.event_id
> AND h.demo_no  = d.demo_no
>  begin-select
>  end-select
>  end-if
> end-select
> end-procedure


__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - Buy the things you want at great prices.
http://auctions.yahoo.com/