AND or OR in SQL Statement 2

[tmcneil]

I have the SQL statement that I am not sure of the syntax in the WHERE clause.

SELECT a.ANN_EVENT_ID,
       b.ANN_EVENT_NUM,
       b.ANN_EVENT_TYPE_ID,
       DECODE(a.TYPE, 'ADHOCMBTA', 'ADHOC', 'LIVEMBTA', 'LIVE', 'STOPMBTA', 'STOP', a.TYPE)
       AS TYPE,
       ANN_EVENT_PKG.GetStations(a.ANN_EVENT_ID, a.time_stamp,b.ANN_EVENT_NUM) AS STATIONS,
       b.NAME AS EVENT_TYPE
       FROM ANN_EVENT_MSG a,
       (select a.ann_event_id, a.ann_event_num,
               a.ann_event_type_id, MIN(a.time_stamp) as min_time_stamp,
               b.name
        from ann_event a,
             ann_event_type b
        where a.ann_event_type_id = b.ann_event_type_id
        group by a.ann_event_id, a.ann_event_num, a.ann_event_type_id, b.name) b
WHERE a.ANN_EVENT_ID = b.ANN_EVENT_ID AND
      b.NAME = 'SUSPEND_MAN_ANN_SCHEDULE' AND
      b.NAME = 'SUSPEND_MAN_ANN_STATION' AND
      b.NAME = 'SUSPEND_AUTO_ANN_STATION'
ORDER BY a.ANN_EVENT_ID, b.ANN_EVENT_NUM;

If I search for only 'SUSPEND_MAN_ANN_SCHEDULE', I will have a recordset returned. However, if I add anymore AND's after that one, I will get no records returned. If I use OR, the SQL Worksheet just hangs and does not return anything. So what's the correct syntax to execute this PL/SQL.

Thanks,
Todd

 

[carp]

Adding more AND conditions on the same column will return no rows because b.NAME can only have one value at a time. What your SQL (NOT PL/SQL, by the way) is saying is the logical equivilant to saying "Show me a crayon that is green and red and burnt sienna".
Also, the alias "TYPE" is a poor choice, since TYPE is a reserved word.

Finally, I would recommend using different table aliases in your inline view to avoid confusing yourself or other developers (if not the SQL engine!).

Try this:

SELECT a.ANN_EVENT_ID,
       b.ANN_EVENT_NUM,
       b.ANN_EVENT_TYPE_ID,
       DECODE (a.TYPE, 'ADHOCMBTA', 'ADHOC', 
                       'LIVEMBTA', 'LIVE', 
                       'STOPMBTA', 'STOP', 
                        a.TYPE) AS A_EVENT_TYPE,
       ANN_EVENT_PKG.GetStations(a.ANN_EVENT_ID,  
                                 a.time_stamp,
                                 b.ANN_EVENT_NUM) 
                                       AS STATIONS,
       b.NAME AS B_EVENT_TYPE
FROM ANN_EVENT_MSG a,
     (select c.ann_event_id, 
             c.ann_event_num,
             c.ann_event_type_id, 
             MIN(c.time_stamp) as  min_time_stamp,
             d.name
        from ann_event c,
             ann_event_type d
       where c.ann_event_type_id = d.ann_event_type_id
       group by c.ann_event_id, 
                c.ann_event_num,       
                c.ann_event_type_id, 
                d.name) b
WHERE a.ANN_EVENT_ID = b.ANN_EVENT_ID AND
     (b.NAME = 'SUSPEND_MAN_ANN_SCHEDULE' OR
      b.NAME = 'SUSPEND_MAN_ANN_STATION' OR
      b.NAME = 'SUSPEND_AUTO_ANN_STATION')
ORDER BY a.ANN_EVENT_ID, b.ANN_EVENT_NUM;

 

[BJCooperIT]

b.NAME only contains one value, so how can it simultaneously match three literals? Are you sure you don't want the "OR" (where b.NAME is any one of these values?

WHERE a.ANN_EVENT_ID = b.ANN_EVENT_ID 
AND   (b.NAME = 'SUSPEND_MAN_ANN_SCHEDULE' OR
       b.NAME = 'SUSPEND_MAN_ANN_STATION' OR
       b.NAME = 'SUSPEND_AUTO_ANN_STATION')

Or even better:

WHERE a.ANN_EVENT_ID = b.ANN_EVENT_ID 
AND   b.NAME in('SUSPEND_MAN_ANN_SCHEDULE',
                'SUSPEND_MAN_ANN_STATION',
                'SUSPEND_AUTO_ANN_STATION')

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[BJCooperIT]

Sorry Carp - I got interrupted while composing a reply and we cross-posted. Totally agree regarding the aliases, but I like to name them a little more meaningfully than just A, B, C. NAME is also a reserved word. My version would be more like:

SELECT aem.ann_event_id,
       aet.ann_event_num,
       aet.ann_event_type_id,
       decode(aem.TYPE,
              'ADHOCMBTA', 'ADHOC',
              'LIVEMBTA',  'LIVE',
              'STOPMBTA',  'STOP',
              aem.TYPE) event_type,
       ann_event_pkg.getstations
         (aem.ann_event_id,
          aem.time_stamp,
          aet.ann_event_num) stations,
       aet.event_name
FROM   ann_event_msg aem,
       (SELECT ae.ann_event_id,
               ae.ann_event_num,
               ae.ann_event_type_id,
               MIN(ae.time_stamp) min_time_stamp,
               et.NAME event_name
        FROM   ann_event      ae,
               ann_event_type et
        WHERE  ae.ann_event_type_id = et.ann_event_type_id
        GROUP  BY ae.ann_event_id,
                  ae.ann_event_num,
                  ae.ann_event_type_id,
                  et.NAME) aet
WHERE  aem.ann_event_id = aet.ann_event_id
AND    aet.event_name IN('SUSPEND_MAN_ANN_SCHEDULE',
                         'SUSPEND_MAN_ANN_STATION',
                         'SUSPEND_AUTO_ANN_STATION')
ORDER  BY aem.ann_event_id,
          aet.ann_event_num;

 

[carp]

You're right on both counts!
But NAME is already the name of a column in the table so I can't change that, and I was trying to match the coding style to minimize invasiveness. But you and I are seeing this the same way, apparently - it's nice to have company in the asylum!