lundi 29 juin 2015

Eliminate duplicate rows containing blanks

I have a condition where I have one header and multiple lines for an item.
I am attempting to add the values from the line join to my header query.
Valid values for this PGM_CD field are S, L, V, Blank etc.

If a line has a value on one line and blank on the others, I need to report the actual value at the header level and not blank values.
ie - line 1 is blank, line 2 is V, I want to report V.
Since blank is a valid value on some, I can't eliminate blanks in my subquery completely.

How do I interrogate all the lines to find that they are either all blank, where I would report blank at the header level or that there is a value on one of the lines in which case I would report that value at the header?

Here's a subset of my query

SELECT 
hdr.field1
hdr.field2
hdr.field3
au.PGM_CD   (this field can have the blanks, v, l, etc. values)
 FROM HEADER hdr
 JOIN (SELECT DISTINCT l.ID, PGM_CD
  FROM LINE l 
  LEFT JOIN AUTH a
  ON l.ID = a.ID) AS au

Aucun commentaire:

Enregistrer un commentaire