View duplicating rows
I have a table of sales data which is correct and is created once every day.
Query4 INVOICE_ID INVOICE_LINE_NO GL_ACCOUNT_ID SHIP_QTY AMOUNT INVOICE_DATE PART_ID 298506 5 0000-3205 0 12 10/2/2013 FREIGHT 298507 7 0000-3205 0 19.38 10/2/2013 FREIGHT 298508 2 0000-3205 0 33.99 10/2/2013 FREIGHT
The INVOICE_ID, INVOICE_LINE_NO is the key field.
When I use this table in a view I get:
Query3 INVOICE_ID INVOICE_LINE_NO GL_ACCOUNT_ID SHIP_QTY AMOUNT INVOICE_DATE PART_ID 298506 5 0000-3205 0 12 10/2/2013 FREIGHT 298506 5 0000-3205 0 12 10/2/2013 FREIGHT 298507 7 0000-3205 0 19.38 10/2/2013 FREIGHT 298507 7 0000-3205 0 19.38 10/2/2013 FREIGHT 298508 2 0000-3205 0 33.99 10/2/2013 FREIGHT 298508 2 0000-3205 0 33.99 10/2/2013 FREIGHT
Obviously, my FREIGHT total is doubled. How do I create a view that does not duplicate the rows. I am using the standard database so I do not have Materialized Views that can have constraints.
Query4 INVOICE_ID INVOICE_LINE_NO GL_ACCOUNT_ID SHIP_QTY AMOUNT INVOICE_DATE PART_ID 298506 5 0000-3205 0 12 10/2/2013 FREIGHT 298507 7 0000-3205 0 19.38 10/2/2013 FREIGHT 298508 2 0000-3205 0 33.99 10/2/2013 FREIGHT
The INVOICE_ID, INVOICE_LINE_NO is the key field.
When I use this table in a view I get:
Query3 INVOICE_ID INVOICE_LINE_NO GL_ACCOUNT_ID SHIP_QTY AMOUNT INVOICE_DATE PART_ID 298506 5 0000-3205 0 12 10/2/2013 FREIGHT 298506 5 0000-3205 0 12 10/2/2013 FREIGHT 298507 7 0000-3205 0 19.38 10/2/2013 FREIGHT 298507 7 0000-3205 0 19.38 10/2/2013 FREIGHT 298508 2 0000-3205 0 33.99 10/2/2013 FREIGHT 298508 2 0000-3205 0 33.99 10/2/2013 FREIGHT
Obviously, my FREIGHT total is doubled. How do I create a view that does not duplicate the rows. I am using the standard database so I do not have Materialized Views that can have constraints.
0