Re: Attention oracle experts. Need some help please with Selects

clinttoris_at_hotmail.com wrote:
> clinttoris_at_hotmail.com wrote:
> > Hello experts,
> >
> > I have a simple select that returns a recordset however within this
> > recordset I need perform a further filter. Let me show you some code
> > [code]
> > SELECT
> > status,
> > pending,
> > convert_utc_date(Arrival_Time,'EST'),
> > (CASE
> > when (trunc(sysdate - convert_utc_date(Arrival_Time,'EST'))) between 0
> > and 15 then '0-15'
> > when (trunc(sysdate - convert_utc_date(Arrival_Time,'EST'))) between
> > 16 and 30 then '16-30'
> > when (trunc(sysdate - convert_utc_date(Arrival_Time,'EST'))) between
> > 31 and 60 then '31-60'
> > when (trunc(sysdate - convert_utc_date(Arrival_Time,'EST'))) >= 61
> > then '61+'
> > End) AS "Tickets"
> > FROM table1
> > WHERE Assigned_To_Group_ = 'Mechanics'
> > AND Status not in (4,5)
> > AND Pending in (Select Pending from table1 where Status !=3 and
> > pending !=3)
> > order by status;
> >
> > The pending in syntax......I need to say something where based on the
> > recordset further filter all rows where the status = 3 and pending =3.
> > However what is happening is if I do not include the line AND Pending
> > in (Select Pending from table1 where Status !=3 and pending !=3) I get
> > 50 rows. These seems about right. If I add the line back I get 4
> > rows. This is not right. Any ideas? Thanks.

>

Which version/platform? Some patchsets in O8 did strange things to inline views, for me anyways. See if it works different by aliasing the subquery: AND Pending in (Select Pending from table1 b where b.Status !=3 and b.pending !=3)

I think the way you are doing it is implicitly self-joining table1 and thereby excluding more than you want. But then again, I never do well desk-checking sql, it might be easier if you gave an example script with field and table definitions and data insertion. More brainpower might be dedicated to solving it in this forum.

jg

--
@home.com is bogus.
No matter, matter is energy.
http://money.cnn.com/2006/06/21/technology/10dontmatter.biz2/index.htm