CODEWITHSUNDEEP
sqloracle-database
Soha Mahmoud
Soha Mahmoud

Reputation: 15

Duplication in the output of sql query

so I found a data duplication after running this query

 SELECT PRHA.REQUISITION_NUMBER  REQUISITION_NUMBER,
 PRLA.LINE_NUMBER  LINE_NUMBER,
 PRLA.QUANTITY  QUANTITY,
 PRLA.UNIT_PRICE  UNIT_PRICE,
 PRLA.UNIT_PRICE*PRLA.QUANTITY  AMOUNT,
 ESIV.ITEM_NUMBER  ITEM_NUMBER,
 ESIV.DESCRIPTION  DESCRIPTION,
 UOM.DESCRIPTION UNIT_OF_MEASURE

 FROM EGP_SYSTEM_ITEMS_VL ESIV,
 POR_REQUISITION_LINES_ALL PRLA,
 POR_REQUISITION_HEADERS_ALL PRHA,INV_UNITS_OF_MEASURE_VL  UOM

 WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID 
 AND PRLA.ITEM_ID=ESIV.INVENTORY_ITEM_ID 
 AND UOM.UOM_CODE = PRLA.UOM_CODE 
 AND PRHA.REQUISITION_NUMBER=:PR_NUMBER

how to solve it ?

Upvotes: 1

Views: 293

Answers (1)

JohnHC
JohnHC

Reputation: 11195

If the whole row is a duplicate, use DISTINCT

select DISTINCT -- removes duplicates iff the entire row matches another
       PRHA.REQUISITION_NUMBER  REQUISITION_NUMBER,
       PRLA.LINE_NUMBER  LINE_NUMBER,
       PRLA.QUANTITY  QUANTITY,
       PRLA.UNIT_PRICE  UNIT_PRICE,
       PRLA.UNIT_PRICE*PRLA.QUANTITY  AMOUNT,
       ESIV.ITEM_NUMBER  ITEM_NUMBER,
       ESIV.DESCRIPTION  DESCRIPTION,
       UOM.DESCRIPTION UNIT_OF_MEASURE

from  EGP_SYSTEM_ITEMS_VL ESIV
inner join POR_REQUISITION_LINES_ALL PRLA -- explicit join syntax for the 21st century
  on PRLA.ITEM_ID=ESIV.INVENTORY_ITEM_ID
inner join POR_REQUISITION_HEADERS_ALL PRHA
    on PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
inner join INV_UNITS_OF_MEASURE_VL  UOM
    on UOM.UOM_CODE = PRLA.UOM_CODE

where PRHA.REQUISITION_NUMBER=:PR_NUMBER

If it is just some of the row, ie, some columns, then post sample data from all tables

Upvotes: 1

Related Questions