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