> 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
