Home » SQL & PL/SQL » SQL & PL/SQL » View: duplicate record
View: duplicate record [message #256007] Thu, 02 August 2007 13:54 Go to next message
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 #256009 is a reply to message #256007] Thu, 02 August 2007 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DISTINCT

Regards
Michel
Re: View: duplicate record [message #256013 is a reply to message #256009] Thu, 02 August 2007 14:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And check if you maybe missed out on some columns of the foreign key - unique key pairs used for the joins.
Re: View: duplicate record [message #256025 is a reply to message #256013] Thu, 02 August 2007 15:31 Go to previous messageGo to next message
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 #256026 is a reply to message #256025] Thu, 02 August 2007 15:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Do you think "distinct" can help here, Michel?


Just put in querry and try it.
Re: View: duplicate record [message #256067 is a reply to message #256007] Thu, 02 August 2007 21:34 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: number format
Next Topic: issue with quotes
Goto Forum:
  


Current Time: Sat Dec 03 20:13:53 CST 2016

Total time taken to generate the page: 0.15229 seconds