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
