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

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.

O.k experts, I need an explaination. I figured this out but not sure why it is working. I modified the pending syntax to read AND (pending is null or pending <> 3). I thought I would be more accurate by adding the status in that equation but I guess not. Received on Mon Jun 26 2006 - 13:54:36 CDT