Thanks for your help. Using the project followed by inserts produced the results that I needed. Very fast and very flexible.
John -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Thompson Technology Consultants Sent: Sunday, February 13, 2005 1:49 AM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Project and sub-select with distinct The method that I have found to accomplish what you are doing and find it works very fast is: Project Temp PRICE_BK from UPC_Master using (column list)+ where limit = 0 (This will create a temp table with no rows.) Insert into PRICE_BK (MFG_STYLE) from UPC_Master + Values MFG_STYLE group by MFG_STYLE + Where MFG_STYLE IS NOT NULL The above will fill the temp table with all unique MFG_STYLE records and it will process very fast. Since the other fields may hold multiple values, you can update the fields in this table with which ever values you need. Such as Update PRICE_BK Set (colname) = T2.colname from PRICE_BK T1, + UPC_MASTER T2 where T1.MFG_STYLE = T2.MFG_STYLE and + T1.etc, etc. (use the where clause to isolate what values you want) You could include other columns in the INSERT command, but remember that the group by column MUST include all the columns listed in the SELECT. So if you have one MFG_STYLE value with more than one value of another field in Group by statement, you would get multiple rows of MFG_STYLE in your temp table. Thus you may need to use the update command. I have found that the speed is extremely fast if you use indexed columns. I believe a good practice to follow to help speed up things is project the blank table first, then insert, then create the indexes, then do the updates. The insert will run faster if conducted before the indexes are built. Then the update will run faster if you use indexed columns in your where clause. Hope that helps! -Bob Thompson Technology Consultants 276 Chippewa LaPorte, IN 46350 219-324-2605 (Phone & Fax) 219-363-7441 (Cell) http://ttc.inc.home.comcast.net -----Original Message----- From: John Engwer [SMTP:[EMAIL PROTECTED] Sent: Saturday, February 12, 2005 9:41 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Project and sub-select with distinct 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
