On 8/7/06, DRE <[EMAIL PROTECTED]> wrote: > Hi, I've run across this view in a sql 7 db. Please note right after > the from clause, there looks like a table name before any of the join > parameters and lots of parenthesis in the same area. I've never seen > this and I couldnt find it in any of my sql books. Can anybody share > any insight? It seems to run in 7 but not in 2k? > > DRE > > SELECT item.itemID AS ProdID, > numOfBlank + itemName AS ProdName, > catalogCategoryGroup + ' - ' + catalogCategory AS ProdCat, > item.itemDesc AS ProdDesc, > CASE WHEN classCartPrice.priceList = NULL AND > itemCartPrice.priceList = NULL > THEN '0' ELSE CASE WHEN classCartPrice.priceList IS NULL > THEN itemCartPrice.priceList ELSE classCartPrice.priceList END > END AS priceList, > CASE WHEN classCartPrice.memberPriceList = NULL AND > itemCartPrice.memberPriceList = NULL > THEN '0' ELSE CASE WHEN classCartPrice.memberPriceList IS NULL > THEN itemCartPrice.memberPriceList ELSE classCartPrice.memberPriceList > END END AS memberPriceList, 1 AS VendorID, > 'Bradford Publishing' AS Vendor, item.dlvryType, > '' AS DlvryFmt, '' AS FilePath, '' AS FileName, '' AS FileSize, > 0 AS ShipCost, 10 AS DaysToExp, 1 AS Taxable, > 0 AS catDiscount, item.classID, class.class > FROM ((class RIGHT JOIN > (catalogCategoryGroup RIGHT JOIN > ((item LEFT JOIN > itemToCatalogCategory ON > item.itemID = itemToCatalogCategory.itemID) LEFT JOIN > catalogCategory ON > itemToCatalogCategory.catalogCategoryID = > catalogCategory.catalogCategoryID) > ON > catalogCategoryGroup.catalogCategoryGroupID = > catalogCategory.catalogCategoryGroupID) > ON class.classID = item.classID) LEFT JOIN > classCartPrice ON class.classID = classCartPrice.classID) LEFT > JOIN > itemCartPrice ON item.itemID = itemCartPrice.itemID >
It looks like those are probably valid joins...just not the usual way of presenting them. I don't know why 2k would treat this any different than 7 (I just tested a similar join in 2k and it worked fine)...are you getting a specific error message? BTW, the CASE statements could be rewritten with COALESCE, which should be faster... COALESCE(classCartPrice.priceList,itemCartPrice.priceList,'0') AS priceList, COALESCE(classCartPrice.memberPriceList,itemCartPrice.memberPriceList,'0') AS memberPriceList -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:249064 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
