Dennis,
I had considered that option, but I'm trying to solve this problem without needing to change the structure in databases in three locations that are in constant use with STATICDB ON. Meanwhile, an alternate solution has materialized. What I really wanted was some variation on the SELECT in the view to permit me to change only the view definition and nothing else. That view gets defined each time the user invokes the piece of code that uses it, so fixing the view would have been the simplest solution. But if I name the temp table the same as the view and retain the column names, I don't need to change forms and such, so that's a good second choice. 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 Dennis McGrath Sent: Friday, June 05, 2009 1:29 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: SELECT problem Add an autonumber column to the table. If you autonumber column is xID then SELECT * from viewname Where xID = (SELECT MAX(xID) from FROM tablea T1, table2 T2,table3 T3,table4 T4 + WHERE conditionsetC ) Simplify the subselect if that makes sense. Dennis McGrath _____ From: [email protected] [mailto:[email protected]] On Behalf Of Emmitt Dove Sent: Friday, June 05, 2009 12:14 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: SELECT problem 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]
