Re: I believe I tried a union with no success. I received an error because of the descrlong field, whic

[Arjan Hoornstra <ahoor@YAHOO.COM>]

Well, for using a union you have to make sure that you have the exact
same number of clumns in your select list... in the same order and the
same datatype. That's why I added the 'null' in the second select.
Other than that I can;t think of anything why you would get an error
(it's not a special column, is it?) for a descrlong except I see that
it is the column  right before the descr254a column....

But maybe you can copy the errormessage(s)? They may help... if it
doesn't work like this.

Arjan



--- Charllotte Hurt <CHurt@YAR.MIRACOSTA.CC.CA.US> wrote:
> I believe I tried a union with no success.  I received an error
> because of the descrlong field, which I do need to as part of my
> select.  Apparetnly union and descrlong don't mix.
>
> >>> Arjan Hoornstra <ahoor@yahoo.com> 07/06/01 08:37AM >>>
> Hi Charlotte,
>
> The problem is that you have a few things to consider:
> - you want to see all rows of table B whether they join with F or
> not.
> That sounds like an outerjoin.
> - you want to select a column from the outer-table (f). Does not have
> to be a problem.
> - you want to use a max(date). Sounds like a 'group by', but if you
> use
> it in a subquery it has to be correlated (inner join).
> - you have additional statements for the outer table.
>
> So, if you would use the outer join you can not use the max function.
> The other solution given (by Debbie Pavlak) will give you the error
> like 'You can not use a inner join on a table that is also in an
> outer
> join'. Because you have to decide which it is...
> It's a common problem but you need a work around...
>
> There is however one other possibility, and that is the UNION
> statement.
> (ansi sql, works for every database platform)
>
> Then it looks like this in pseudo code:
>
> select everything, f.descr254a
> from   tables a,b,c,d,e and f
> where  clause
> and    b.rqrmnt_group = f.rqrmnt_group     ! first the join part
> and    f.effdt  = ( SELECT MAX(f1.EFFDT)
>                     FROM ps_rq_grp_tbl f1
>                     WHERE f1.rqrmnt_group = f.rqrmnt_group
>                     AND f1.EFFDT <= sysdate
>                    )
> and f.eff_status = 'A'
> UNION
> select everything, null     ! now the others where there is no row in
> f
> from   tables a,b,c,d,e
> where  clause
> and    not exists (select 'x'
>                    from ps_rq_grp_tbland   f2
>                    where f2.rqrmnt_group = b.rqrmnt_group ! index on
> f2.rqrmnt_group?
>                   )
>
> But performance wise, depends on the number of rows in the 6 tables
> but
> I would try the #table....
>
> Good luck
>
> Arjan
>
> PS Why not by the sqr-usergroup?
>
> --- Charllotte Hurt <CHurt@yar.miracosta.cc.ca.us> wrote:
> > Thanks for responding.  I will consider your suggestion, but I was
> > hoping that I could accomplish what I needed with mods only to this
> > sql.  Maybe that isn't possible?
> >
>
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail
> http://personal.mail.yahoo.com/


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/