CODEWITHSUNDEEP
sqloracle-database
Hakan özel
Hakan özel

Reputation: 131

Priorities in a select query with oracle

I use the following query for a report.

      SELECT P.PIECE_ID,spd.IS_MAIN_DEFECT,spd.PIECE_DEFECT_NUM_ID
      FROM piece P , STY_PIECE_DEFECT spd ,STY_DEFECT_CATALOGUE sdc ,piece_history ph , 
      piece_history_out pho, PLANT_CONFIG pc,STY_QUALITY sq 
      (...join and where clauses)
      GROUP BY P.PIECE_ID,spd.IS_MAIN_DEFECT,spd.PIECE_DEFECT_NUM_ID ORDER BY 1 desc

The output :

enter image description here

But I want to see the following result in my output ;

IS_MAIN_DEFECT will be checked as a priority.

If it is 'Y' then I will only see that row in the table else it is 'N' then I will only see that min(piece_defect_num_id) row in the table.

For example : I need to see that piece_defect_num_id as 141,900 for '20190158GA' ,

If all IS_MAIN_DEFECT values are 'N' for '20190158GA' then I need to see that piece_defect_num_id as 141,476.

Upvotes: 0

Views: 73

Answers (1)

micklesh
micklesh

Reputation: 417

an analytic function could be helpful in this case

  SELECT P.PIECE_ID
  , spd.IS_MAIN_DEFECT
  ,spd.PIECE_DEFECT_NUM_ID
  -- check if defect is the main one or not
  , CASE WHEN spd.is_main_defect = 'N' THEN
      min(spd.piece_defect_num_id) OVER (partition by p.PIECE_ID)
    ELSE NULL END as min_defect

  FROM piece P , STY_PIECE_DEFECT spd ,STY_DEFECT_CATALOGUE sdc ,piece_history ph , 
  piece_history_out pho, PLANT_CONFIG pc,STY_QUALITY sq 
  (...join and where clauses)
  GROUP BY P.PIECE_ID,spd.IS_MAIN_DEFECT,spd.PIECE_DEFECT_NUM_ID ORDER BY 1 desc

It's hard to provide a workable query without full table/query definitions, however an example could be found here

To reiterate the advice mentioned by @Gordon Linoff - try using ANSI JOIN syntax if possible

Upvotes: 1

Related Questions