| View: duplicate record [message #256007] |
Thu, 02 August 2007 13:54  |
syang
Messages: 30
Registered: February 2007
|
Member |
|
|
Hello All,
We created a database view from multiple tables. Below is the view sql:
create view v_cbic_supplier_doc as
select
a.SUPLR_ID,
a.BIDDER_NUM,
d.BUSNS_TYPE_CD,
a.CASH_FLOW_RCPT_IND,
a.BAL_SHEET_RCPT_IND,
a.INCM_STMT_RCPT_IND,
a.SCHDL_L_RCPT_IND,
a.SCHDL_C_RCPT_IND,
a.CRED_RPT_RCPT_IND,
a.FORM_10K_RCPT_IND,
a.EXPNSN_LTR_RCPT_IND,
a.LGL_NTWRK_MBR_CNTRCT_RCPT_IND,
a.CRTFCTN_DCMTN_RCPT_IND,
a.FORMA_DCMTN_RCPT_STUS_CD,
a.FORMB_DCMTN_RCPT_STUS_CD,
b.LGL_BUSNS_NAME,
b.BUSNS_PHNE_NUM,
c.CNTCT_PRSN_1ST_NAME,
c.CNTCT_PRSN_LAST_NAME
from
supplier_documentation a,
dmepos_supplier b,
supplier_location_contact_pers c,
dmepos_supplier_location d
where
a.SUPLR_ID=b.SUPLR_ID and
a.SUPLR_ID=c.SUPLR_ID and
a.SUPLR_ID=d.SUPLR_ID
order by
a.SUPLR_ID;
The view is created but we saw some records that repeated several times. Please see the sample data below (supplier ID 290). How to get rid of the duplicated records?
SUPLR BIDDER_ BU CSH BAL INC SCHDL SCHDC CRED F10K EXPS NTWK CERT FA FB LGL_BUSNS_NAME BUSNS_PHNE FIRST_NAME LAST_NAME
----- ------- -- --- --- --- ----- ----- ---- ---- ---- ---- ---- -- -- ----------------- ---------- ---------- ----------
135 1000035 1 Y Y Y Y N Y N N N Y 02 02 Garmin Suppliers 2125551212 Henry Tilly
290 1000191 2 Y Y Y Y N Y N N N Y 01 02 Samsung 9087974498 Tilak Peddi
290 1000191 3 Y Y Y Y N Y N N N Y 01 02 Samsung 9087974498 Satya Lolla
290 1000191 2 Y Y Y Y N Y N N N Y 01 02 Samsung 9087974498 Satya Lolla
290 1000191 3 Y Y Y Y N Y N N N Y 01 02 Samsung 9087974498 Satya Lolla
290 1000191 2 Y Y Y Y N Y N N N Y 01 02 Samsung 9087974498 Satya Lolla
290 1000191 3 Y Y Y Y N Y N N N Y 01 02 Samsung 9087974498 Tilak Peddi
Thanks!
sy
|
|
|
|
|
|
|
|
| Re: View: duplicate record [message #256025 is a reply to message #256013] |
Thu, 02 August 2007 15:31  |
syang
Messages: 30
Registered: February 2007
|
Member |
|
|
Thanks for your reply!
We were able to get rid of some duplicate records by adding another condition b.LCTN_ID=c.LCTN_ID. See supplier id 290.
SUPLR BIDDER_ BTY CSH BAL INC SCHDL SCHDC CRED F10K EXPS NTWK CERT FA FB LGL_BUSNS_NAME LCTN_PHNE_ FIRST_NAME LAST_NAME
----- ------- --- --- --- --- ----- ----- ---- ---- ---- ---- ---- -- -- ----------------- ---------- ---------- ----------
135 1000035 1 Y Y Y Y N Y N N N Y 02 02 Garmin Supplies Henry Tilly
290 1000191 3 Y Y Y Y N Y N N N Y 01 02 Samsung Loc1 9087974498 Satya Lolla
290 1000191 3 Y Y Y Y N Y N N N Y 01 02 Samsung Loc1 9087974498 Tilak Peddi
290 1000191 2 Y Y Y Y N Y N N N Y 01 02 Samsung Loc2 9287390874 Satya Lolla
However, there are still 2 suppliers with BTY (Business type) "3" for supplier id 290. These 2 records are repeated. How can I get rid of the duplicate in this case? Do you think "distinct" can help here, Michel?
Thanks!
sy
|
|
|
|
|
|
| Re: View: duplicate record [message #256067 is a reply to message #256007] |
Thu, 02 August 2007 21:34 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Quote: | However, there are still 2 suppliers with BTY (Business type) "3" for supplier id 290. These 2 records are repeated.
|
As far as I can see these two records are NOT the same as they differ in last two columns taken from table supplier_location_contact_pers.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
