All Forums Database
tmw 7 posts Joined 04/15
07 Apr 2015
View removes duplicate records?

I have a view that transforms some records from an older table.  Here's a snippet of it:

Replace view tablename_vw as

sel case when top_level = 'CPO' then 'PO' else top_level end as top_level, 

test_date, test_id, control, ctrl_rslt

from tablename a where ctrl_rslt in ('PASS','FAIL')


sel top_level,

qa_date as test_date, test_id, control, ctrl_rslt 

from other_tablename


It seems that when I have two identical records in tablename (which is a legitimate duplicate, the exact same test was done twice on the same subject), only one of those shows up in tablename_vw.
I thought that avoiding the group by would allow duplicates, but have I missed something?  How to I keep the duplicate records in my view, similar to the source table?
Thank you!

dnoeth 4628 posts Joined 11/04
07 Apr 2015

UNION defaults to DISTINCT, you must override it using UNION ALL.


tmw 7 posts Joined 04/15
07 Apr 2015

That did it.  
Dieter, I've leveraged many of your answers to others in the past, and really appreciate your help, both here and all the other places.  Thank you.

You must sign in to leave a comment.