1

Trying To create an inventory view. I have created this query using MS Access query builder since I am no pro at SQL and this is pretty convoluted. When translating this into MYSQL syntax I get an error on the third line " ((COALESCE(items_received.Qty_Received, 0) - COALESCE(items_sold.Qty_Sold, 0))) AS QtyOnHand" The error return is 1054 Unknown Column 'QtyOnHandinfield list`. I assume I might need to do an additional SELECT statement but not sure how to put it in. Any help is appreciated.

SELECT 
    `products`.`cat_num` AS `Product`,
    ((COALESCE(`items_received`.`Qty_Received`, 0) - COALESCE(`items_sold`.`Qty_Sold`, 0))) AS `QtyOnHand`,
    COALESCE(`item_demand`.`Item_Demand`, 0) AS `Demand`,
    COALESCE(`items_allocated`.`Qty_Allocated`, 0) AS `Allocated`,
    COALESCE(`items_backordered`.`Qty_Backordered`, 0) AS `Backordered`,
    If(`QtyOnHand` - `Allocated` - `Backordered` < 0,
        0,
        `QtyOnHand` - `Allocated` - `Backordered`) AS `Available`,
    COALESCE(`products`.`min_production_qty`, 0) AS `InMultiples`,
    COALESCE(`products`.`target_inv_level`, 0) AS `TargetLevel`,
    If(`Available` < `Demand`,
        ABS(`Available` - `Demand`),
        0) AS `Shortage`,
    If(`Available` < `TargetLevel`,
        If((`Shortage` + (`TargetLevel` - `Available`)) <= `InMultiples`,
            `InMultiples`,
            ABS(CEIL(-(`Shortage` + (`TargetLevel` - `Available`)) / `InMultiples`))),
        0) AS `QuantityToPack`
FROM
    `items_allocated`
        RIGHT JOIN
    (`items_sold`
    RIGHT JOIN (((`items_received`
    RIGHT JOIN (`items_onHold`
    RIGHT JOIN (`products`
    LEFT JOIN `items_backordered` ON `products`.`cat_num` = `items_backordered`.`cat_num`) ON `items_backordered`.`cat_num` = `products`.`cat_num`) ON `items_received`.`cat_num` = `products`.`cat_num`)
    LEFT JOIN `items_unallocated` ON `products`.`cat_num` = `items_unallocated`.`cat_num`)
    LEFT JOIN `item_demand` ON `products`.`cat_num` = `items_demand`.`cat_num`) ON `items_sold`.`cat_num` = `products`.`cat_num`) ON `items_allocated`.`cat_num` = `products`.`cat_num`
GROUP BY `products`.`cat_num`
ORDER BY `products`.`cat_num`;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.