How to write a sql query for below condition

Question

I have two query which is selecting some data from same table

query1 :

select rownum rn , error_data_log 
from ext_tab_log 
where error_data_log like'error%' 
ORDER BY rn, error_data_log ;

Result :

+----+--------------------------+
| RN |  error_data_log          |
+----+--------------------------+
| 1  |  error processing column |
+----+--------------------------+

query 2 :

select rownum rn , error_data_log 
from ext_tab_log 
where error_data_log like 'KUP-04101%' 
ORDER BY rn, error_data_log ;

Result :

+----+----------------------------------------------+
| RN | error_data_log                               |
+----+----------------------------------------------+
| 1  | KUP-04101: record 1 rejected in file abc.txt |   
| 2  | KUP-04101: record 8 rejected in file abc.txt |  
| 3  | KUP-04101: record 9 rejected in file abc.txt |
+----+----------------------------------------------+

How can we write a sql query to obtain below result:

+----+----------------------------------------------+ 
| RN | error_data_log                               |
+----+----------------------------------------------+
| 1  | error processing column                      |
| 2  | KUP-04101: record 8 rejected in file abc.txt |
+----+----------------------------------------------+

Any reason for retreiving `KUP-04101: record 8 rejected in file abc.txt`? — Ullas, Apr 29 '15 at 07:32
Your specs are not clear. What happens to "KUP-04101: record 1 rejected in file abc.txt" and "KUP-04101: record 9 rejected in file abc.txt". If you don't want them in your result set you'll have to specify the filter. — Rob van Laarhoven, Apr 29 '15 at 07:33
actualy i dont want first and last record from the result of query 2 . — y2j, Apr 29 '15 at 07:34
@RobertMerkwürdigeliebe Actualy i fetching result from table using filter 'KUP-04101' .after filter i got these result . after that also want to filter first and last record from above result — y2j, Apr 29 '15 at 07:38
All this doesn't make sense. The rownum is the number when Oracle happens to grap that row. There is no implicit order in a table, so that rownum is kind of random. As rownum is the order in which the rows are in the result list, `order by rownum` simply doesn't do anything. And as there are no duplicate rownum in a query, it makes no sense to order by rownum plus something. Having said this, what shall be the criteria which 'error%' rows to show, and what shall be the criteria which 'KUP-04101%' rows to show? And in which order shall these rows be shown? — Thorsten Kettner, Apr 29 '15 at 08:12

score 1 · Accepted Answer · answered Apr 29 '15 at 07:52

If i understand you correctly, you can try something like this:

select rownum rn , error_data_log
from

    select rownum rn , error_data_log, 1 As QueryNum
    from ext_tab_log 
    where error_data_log like'error%' 

    UNION ALL

    select rownum rn , error_data_log, 2 QueryNum
    from ext_tab_log 
    where error_data_log like 'KUP-04101%' 
    group by rn , error_data_log, QueryNum
    having rn > 1 and rn < max(rn)

) UnionSelect   
ORDER BY QueryNum, rn, error_data_log ;

Note: sql written directly here, I didn't try it myself.

score 1 · Answer 2 · answered Apr 29 '15 at 07:53

WITH KUP-04101 AS
(SELECT error_data_log
 , CASE
     WHEN LEAD(error_data_log) OVER (ORDER BY error_data_log) IS NULL
      THEN 'Last'
     WHEN LAG(error_data_log) OVER (ORDER BY error_data_log) IS NULL 
      THEN 'First'
   END first_and_last
  FROM ext_tab_log 
  WHERE error_data_log LIKE 'KUP-04101%')
SELECT error_data_log 
FROM KUP-04101 
WHERE first_and_last NOT IN ('First','Last')
UNION
SELECT error_data_log 
FROM ext_tab_log 
WHERE  error_data_log LIKE 'error%' 
ORDER BY error_data_log ;

How to write a sql query for below condition

2 Answers2