Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Don't add the system fields like $Field to the table box.