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 ?

2

1 Answer

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.