>lSelectWay = AskNo("Do you want to use SQL-Select solution?") &&.T. && Set to true if you prefer select solution > >IF m.lSelectWay > ltStartTime = DATETIME() > LOCAL lcWhere, lcOldANSI > lcOldANSI = SET("Ansi") > SET ANSI OFF > > lcWhere = [Styles.Style NOT IN ("F","S","X","D","M", "CD", "PK") ] + ; > [and NOT Styles.Style LIKE "%.%"] && [Styles.In_Stock <=1 ] > > DO CASE > CASE m.cact = "A" && Active > lcWhere = m.lcWhere + [ and Styles.DisContinu = "A"] > > CASE m.cact = "I" && Inactive > lcWhere = m.lcWhere + [ and Styles.DisContinu = "I"] > > CASE m.cact = "B" && Both > lcWhere = m.lcWhere + [ and Styles.DisContinu IN ("A","I")] > ENDCASE > > USE styles INDEX styles IN 0 && Style > USE or_items INDEX or_style, or_items IN 0 && Style, PON + Style > USE orders INDEX orders IN 0 && PON > > LOCAL loThermo, lcClass > lcClass = "cusSQLThermo" > loThermo = CREATEOBJECT(m.lcClass) && we need visual indication for long select > > SELECT styles.STYLE as Style, styles.RESERVED AS nReserved, styles.in_stock AS nInStock ; > FROM Styles WHERE &lcWhere AND Reserved >= In_Stock INTO CURSOR curStyles readwrite >* INDEX ON Style TAG cStyle >*!* >*!* SELECT PON, Style as cStyle, qty, shiped, can_qty FROM Or_Items ; >*!* WHERE Style in (select cStyle FROM curStyles) INTO CURSOR curOr_Items readwrite >*!* INDEX ON PON TAG PON >*!* INDEX ON cStyle TAG cStyle >*!* >*!* SELECT PON, Acc, Approved, App_Date FROM Orders ; >*!* WHERE PON in (select PON FROM curOr_Items) ; >*!* INTO CURSOR curOrders readwrite >*!* INDEX ON PON TAG PON >*!* >*!* SELECT curStyles.cStyle, curStyles.nReserved, curStyles.nInStock, ; >*!* SUM(IIF(curOrders.acc = "HOUSE" AND (NOT curOrders.Approved == "" OR NOT curOrders.app_date = {}), ; >*!* curOr_items.qty - curor_items.shiped - curor_items.can_qty, 000000000)) AS nHouseOrder ; >*!* FROM curStyles INNER JOIN curOr_Items ON curStyles.cStyle = curOr_Items.cStyle ; >*!* INNER JOIN curOrders ON curOr_Items.PON = curOrders.PON ; >*!* GROUP BY 1,2,3 HAVING (nHouseOrder + nInStock) - nReserved <=0 ; >*!* INTO CURSOR dummy READWRITE >*!* ** Close unused cursors >*!* USE IN curOr_Items >*!* USE IN curStyles >*!* USE IN curOrders >*!* > SELECT or_Items.Style, sum(or_items.qty-or_items.shiped-or_items.can_qty) as nHouseOrder ; > FROM Or_Items WHERE Or_Items.Style IN (select Style FROM curStyles) ; > AND Or_Items.PON + Or_Items.Style IN ; > (select PON FROM Orders WHERE orders.acc = "HOUSE" AND (NOT approved == "" OR NOT app_date = {})) ; > GROUP BY 1 INTO CURSOR curOr_Items > > SELECT curStyles.Style FROM curStyles LEFT JOIN curOr_Items ON ; > curStyles.Style = curOr_Items.Style ; > WHERE (nReserved - nStock) >= NVL(nHouseOrder,0) INTO CURSOR Dummy ; > ORDER BY 1 > >*!* SELECT or_items.Style, SUM(IIF(orders.acc = "HOUSE" AND (NOT approved == "" OR NOT app_date = {}), ; >*!* or_items.qty-or_items.shiped-or_items.can_qty, 000000000)) AS nHouseOrder ; >*!* FROM orders INNER JOIN Or_Items ON Orders.PON = Or_Items.PON ; >*!* WHERE Style in (select Style FROM curStyles) ; >*!* GROUP BY 1 INTO CURSOR curTemp READWRITE >*!* >*!* INDEX ON Style TAG cStyle >*!* >*!* SELECT curStyles.Style FROM curStyles ; >*!* INNER JOIN curTemp ON curStyles.Style = curTemp.Style ; >*!* WHERE (nHouseOrder + nInStock) - nReserved <=0 INTO CURSOR Dummy > RELEASE loThermo > USE IN curStyles > USE IN curOr_Items > SELECT dummy >post sys(3054,12) and RECCOUNT() info