0

Input query:

SELECT a.ml_STi_File_id as File_ID, a.ML_STI_SRC_SYS_CD as Src_CD, concat(a.EVENT_CD,a.BUS_AREA_CD) as BE
FROM FFI_ERR_RLT_TRN a, summary_tbl b 
WHERE  a.ml_sti_src_sys_cd = b.ml_sti_src_sys_cd 
AND a.ml_sti_load_dttm = b.ml_sti_load_dttm
AND a.ml_sti_cycle_cd = b.ml_sti_cycle_cd 
AND a.ml_sti_file_id = b.ml_sti_file_id 
AND a.ml_sti_srcsys_dttm = b.ml_sti_srcsys_dttm 
AND a.ML_STI_CYCLE_CD = '0519201701'
group  BY   a.ML_STI_SRC_SYS_CD, a.ml_STi_File_id, a.BUS_AREA_CD,a.EVENT_CD, a.ml_sti_cycle_cd
order by a.ml_sti_src_sys_cd desc;

input:

FILE_ID  SRC BE
-------- --- ------------------------
00428589 631
00428581 623 FTE
00428581 623 FTI
00428581 623 PRP
00428581 623 SPA
00428568 029

output which is required:

FILE_ID  SRC BE
-------- --- ------------------------
00428589 631
00428581 623 PRP
00428581 623 SPA
00428568 029

Can someone help me with this?

Thanks in advance.

I'm using oracle SQL.

The input data is fetched using above query. From the input data I have to filter out the output as show above

HDJEMAI
  • 9,436
  • 46
  • 67
  • 93
mohana
  • 25
  • 3
  • 3
    Why GROUP BY when no aggregate functions are used? – jarlh Jun 02 '17 at 12:35
  • 1
    What criteria are used to determine which rows should be included? –  Jun 02 '17 at 12:36
  • 1
    What does the input data look like? What is the requirement (what are the logical steps that lead to desired output, and must be implemented in SQL code)? How do you expect the volunteers to figure that out - by reading a query that doesn't show the input data and doesn't follow the needed logic? Do you understand why that may be a problem? –  Jun 02 '17 at 12:39
  • @mohana - Not as comment please. edit question and add as formated text – Oto Shavadze Jun 02 '17 at 12:52
  • @jarlh im new to sql – mohana Jun 02 '17 at 12:58
  • @mathguy i have used a query which provides input data as shown above. From the input i have to exclude rows wherever src is '623, 625, 626 & 628' and BE is 'FTE/ FTI' and rows wherever '623' and BE is 'PYT' – mohana Jun 02 '17 at 12:59
  • `SELECT DISTINCT` is used to remove duplicate rows. – jarlh Jun 02 '17 at 12:59
  • What you showing now us as `input`, was current output from your query, right? – Oto Shavadze Jun 02 '17 at 13:04
  • if i have 2 rows: {00428581 623 FTE} & {00428581 623 FTE} distinct will return one row among these. But i would want to neglect such a combination @jarlh – mohana Jun 02 '17 at 13:08
  • @Oto Shavadze yes input is the ouput of the query mentioned. – mohana Jun 02 '17 at 13:10

1 Answers1

0

If what you show as "inputs" is the result of an existing query, you can filter out the undesired rows by adding conditions to your existing WHERE clause. Something like

... [top part of your query here] ...
WHERE
...
AND (    concat(a.EVENT_CD,a.BUS_AREA_CD) is null
      or (a.ML_STI_SRC_SYS_CD, concat(a.EVENT_CD,a.BUS_AREA_CD))
         not in
         ( (623, 'FTE'), (623, 'FTI'), (623, 'PYT'),
           (625, 'FTE'), (625, 'FTI'),
           (626, 'FTE'), (626, 'FTI'),
           (628, 'FTE'), (628, 'FTI')
         )
    )
.......

Note that I specifically handled the case when BE is null (those rows should not be excluded, based on what you described). I did not handle SRC_CD being null, but if it can be null in your data, you need to handle it as well. Also note that aliases given in the SELECT clause cannot be used in the WHERE clause, you need to use the full definition as I have shown (or you can use a subquery - outer query structure, which is less efficient).

It would be much better if all the exceptions (what's in the NOT IN condition) was stored in a table; then the NOT IN condition would be much simpler, referencing that table instead of hard-coding the exceptions.