descrlong

[Charllotte Hurt <CHurt@YAR.MIRACOSTA.CC.CA.US>]

I will have to assemble my code again and try it to get the exact error.  
Descrlong is described as type LONG.  Thanks for your responses.  I'll get back 
to you after I try the union again.

>>> ahoor@YAHOO.COM 07/06/01 09:06AM >>>
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/