Code Proof

[#1]

Good day,

If you could have a look over the old code and let me know if you agree with my translation into SQL i would greatly appreciate it as im not confident on the results im getting.

This what i have been given.

pricing

SELECT

`stock_management1`.`st_prodcode`,

`stock_management1`.`st_sdesc`,

`stock_management1`.`st_mstockist`,

`stock_management1`.`APS_rol`,

`stock_management1`.`APS_eoq`,

`stock_management1`.`APS_ms`

FROM  

`pricing`.`stock_management` `stock_management1`

WHERE 

(`stock_management1`.`st_mstockist`='BRA'

OR `stock_management1`.`st_mstockist`='FCS'

OR `stock_management1`.`st_mstockist`='FKE')

AND `stock_management1`.`APS_ms`>0

AND (`stock_management1`.`st_prodcode`>='A'

AND `stock_management1`.`st_prodcode`<='WZZZZZ999')

EXTERNAL JOIN

stock_management1.st_prodcode={?APS: ITEM1.ST_Prodcode}

AND stock_management1.st_prodcode={?FCS: stocktake1.S_ProdCode}

AND stock_management1.st_prodcode={?CENTRAL: Command.mv_PRODCODE}

COMMAND

SELECT mv_PRODCODE , SUM(mv_Qty) AS TOTAL_INTRANSIT FROM OUTSTANDING_STV ISS INNER JOIN MVMNT SI ON SI.MV_TRANNO=ISS.IS_INVNO WHERE R_BRANCH='APS' GROUP BY MV_PRODCODE;

This is my translated code.

SQL SELECT

    `APS_eoq`,

    `APS_ms`,

    `APS_rol`,

    stock_management.`st_mstockist`,

    stock_management.`st_prodcode`,

    stock_management.`st_sdesc`

FROM  pricing.`stock_management`

Left Join (aps.`item`, cstores.`stocktake`, central.`mvmnt`)

ON pricing.`stock_management`.`st_prodcode` = aps.`item`.`ST_Prodcode`

AND pricing.`stock_management`.`st_prodcode` = cstores.`stocktake`.`s_prodcode`

AND pricing.`stock_management`.`st_prodcode` IN (SELECT MV_PRODCODE

FROM central.`outstanding_stv`

INNER JOIN central.`mvmnt` on central.`mvmnt`.`MV_TRANNO` = central.`outstanding_stv`.`IS_INVNO`

WHERE R_BRANCH="APS")

WHERE `stock_management`.`APS_ms`>0

AND `stock_management`.`st_mstockist`="BRA"

OR `stock_management`.`st_mstockist`="FCS"

OR `stock_management`.`st_mstockist`="FKE"

LIMIT 90;

The reason for my question is that im getting a lot of duplicate rows as seen in the attached document.

Thank you for any advice or help offer!

[Gysbert_Wassenaar]

Don't add the system fields like $Field to the table box.