And, yes, I realize that I could cut the third select out into its own view, created before view hotsheet, and use WHERE COUNT = LAST, then reference that view in the third SELECT. I was hoping to avoid the penalty of a view on a view.
Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of Emmitt Dove Sent: Friday, June 05, 2009 1:04 PM To: RBASE-L Mailing List Subject: [RBASE-L] - SELECT problem I have a view that is a 4-table SELECT, with two unions. In brief: SELECT somecolumns from T1, some from T2, some from T3 + FROM tablea T1, table2 T2,table3 T3,table4 T4 + WHERE conditionsetA + UNION + SELECT somecolumns from T1, some from T2, some from T3 + FROM tablea T1, table2 T2,table3 T3 + WHERE conditionsetB + UNION + SELECT somecolumns from T1, some from T2, some from T3 + FROM tablea T1, table2 T2,table3 T3,table4 T4 + WHERE conditionsetC "conditionsetA" and "conditionsetB" are mutually exclusive, and "conditionsetC" is designed to get results not in A or B. This works. But what I *really* want to do is only get the LAST row that meets conditionsetC. I've been using (MAX(column)) for that SELECT, but as it turns out, the numbers wrap at 99999, and so the newest row may be numbered lower than the row with the MAX value. How can I crack this nut? Here's the actual view; what I want to do is in the third SELECT avoid MAX and instead retrieve the last row from table lineordr that meets the rest of the criteria (and thereby also make the GROUP BY unnecessary): SET VAR vmhdate DATE = 01/01/2049 SET VAR vmhtime TIME = 00:00:00 -- get: 1. Orders with open ship orders -- 2. Orders with open releases but no ship orders -- 3. Distinct cuscode from closed orders where cuscode not yet selected DROP VIEW hotsheet CREATE VIEW hotsheet (ordcode,stencil,packndx,stdpalcd,+ shrldetl,qty2ship,qtyshipd,shpordcd,shpordd,shpord2shp,cuscode,+ readydt,putime,ordrstat) AS + SELECT T1.ordcode,ordprnv,packndx,stdpalcd,+ T2.shrldetl,T2.qty2ship,T2.qtyshipd,+ T3.shpordcd,shpordd,T3.qty2shp,+ T4.cuscode,readydt,putime, + T1.compart + FROM lineordr T1,shipreld T2,shpordd T3,shpordh T4 + WHERE T1.itmcode = .vitmcode AND T1.panelcd = .vpanelcd AND + T1.compart NOT IN ('C','X') AND + T2.ordcode = T1.ordcode AND T3.shrldetl = T2.shrldetl AND + T4.shpordcd = T3.shpordcd AND T4.readydt <> 01/01/80 + UNION + SELECT T1.ordcode,ordprnv,packndx,stdpalcd,+ T2.shrldetl,T2.qty2ship,T2.qtyshipd,+ 0,0,0,+ T3.cuscode,readydt,putime, + T1.compart + FROM lineordr T1,shipreld T2,shiprelh T3 + WHERE T1.itmcode = .vitmcode AND T1.panelcd = .vpanelcd AND + T1.compart NOT IN ('C','X') AND T2.ordcode = T1.ordcode AND + T2.qtyshipd < T2.qty2ship AND T2.shrldetl NOT IN (SELECT shrldetl + FROM lukewarm) AND T3.shprlndx = T2.shprlndx AND + T3.readydt <> 01/01/80 + UNION + SELECT (MAX(T1.ordcode)),ordprnv,packndx,stdpalcd,0,0,0,0,0,0,+ T3.cuscode,.vmhdate,.vmhtime, + T1.compart + FROM lineordr T1,shipreld T2,shiprelh T3 + WHERE T1.itmcode = .vitmcode AND T1.panelcd = .vpanelcd AND + T1.compart = 'C' AND T2.ordcode = T1.ordcode AND + T3.shprlndx = T2.shprlndx AND + T3.cuscode NOT IN (SELECT T4.cuscode FROM lineordr T1,shipreld T2,+ shpordd T3,shpordh T4 WHERE T1.itmcode = .vitmcode AND + T1.panelcd = .vpanelcd AND T1.compart NOT IN ('C','X') AND + T2.ordcode = T1.ordcode AND T3.shrldetl = T2.shrldetl AND + T4.shpordcd = T3.shpordcd AND T4.readydt <> 01/01/80) AND + T3.cuscode NOT IN (SELECT T3.cuscode FROM lineordr T1,shipreld T2,+ shiprelh T3 WHERE T1.itmcode = .vitmcode AND + T1.panelcd = .vpanelcd AND T1.compart NOT IN ('C','X') AND + T2.ordcode = T1.ordcode AND T2.qtyshipd < T2.qty2ship AND + T3.shprlndx = T2.shprlndx AND T3.readydt <> 01/01/80) + GROUP BY T3.cuscode,T1.ordprnv,T1.packndx,T1.stdpalcd,T1.compart Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected]
