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

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

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/