Try this: -- This will create an empty table PROJECT <temp table> FROM <real table> + USING <column list> + WHERE LIMIT = 0 -- You could use a SELECT DISTINCT here INSERT INTO <temp table> (<column list> + SELECT DISTINCT (<column list>) + FROM <real table> -- But my person preference (with no justification whatsoever) is INSERT INTO <temp table> (<column list> + SELECT (<column list>) + FROM <real table> GROUP BY <column list>
--- John Engwer <[EMAIL PROTECTED]> wrote: > Thanks for all of your replies. > > What I was trying to do is create a temp table with distinct styles from the > UPC_MASTER table. There are multiple rows for each style in the UC_MASTER > table. What I thought I was doing was using the sub-select distinct to > eliminate duplicate styles. However, after reading the responses and after > thinking this through I can see that this code will not work. > > I will have to come up with a different solution. As a temporary work > around, I projected the UPC_MASTER to a temp table and then deleted the > duplicate styles. The only problem is that it takes a long time to del dups > from 350,000 records. > > Any suggestions will be appreciated. > > John > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Lawrence > Lustig > Sent: Saturday, February 12, 2005 9:52 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - Re: Project and sub-select with distinct > > > Is it possible to use a sub-select with a project command? When I execute > > the following project, it executes but ignores the sub-select (no errors). > > > > PROJECT TEMPORARY PRICE_BK FROM UPC_MASTER + > > USING MFGCODE,MFG_STYLE,MFG_DESC,W_PRICE,MARKUP,Retail_Price,In_Stock_QTY > + > > WHERE MFG_STYLE IN (SEL DISTINCT MFG_STYLE FROM UPC_MASTER + > > WHE MFG_STYLE IS NOT NULL) > > Looks to me like the command is executing correctly, returning the expected > data and not encountering any errors. > > In effect, you are asking for a temporary table containing all rows from > UPC_MASTER where the value in the MFG_STYLE column is found in the MFG_STYLE > column of UPC_MASTER -- which is guaranteed to be ALL the rows in UPC_STYLE > (at > least, all rows with a non-NULL MFG_STYLE). > > In general, a sub-select is used to obtain a set of rows from one table > based > on information in another table. While you can use a sub-select against the > same table, I can't off the top of my head think of anything you could do in > the sub-select that you couldn't do more easily without the sub-select using > a > normal WHERE clause. In the case you gave above, you could more simply > render > the statement as: > > PROJECT TEMPORARY PRICE_BK FROM UPC_MASTER + > USING MFGCODE,MFG_STYLE,MFG_DESC,W_PRICE,MARKUP,Retail_Price,In_Stock_QTY + > WHERE MFG_STYLE IS NOT NULL > > -- > Larry > > ===== Albert Berry Management Consultant RR2 - 1252 Ponderosa Drive Sparwood BC, V0B 2G2 Canada (250) 425-5806 (250) 425-7259 (708) 575-3952 (fax) [EMAIL PROTECTED]
