>>Use styles in 0 index Styleidx1,Styleidx2,Styleidx3.. >>Use or_items in 0 index Or_idx1,idx2,Or_idx3.. >>Use curstyles in 0 index cur_idx1,cur_idx2,cur_idx3.. >>Use orders in 0 index ordersidx1,ordersidx2,ordersidx3.. >>.. >>Select ...>>
>>>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 >>>