View removes duplicate records?

Database
tmw
Enthusiast

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')

Union 

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!

2 REPLIES
Senior Apprentice

Re: View removes duplicate records?

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

tmw
Enthusiast

Re: View removes duplicate records?

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.