Without looking too much at the query, the first thing I would do is change the IFs to CASEs.
http://www.databasejournal.com/features/mssql/article.php/3288921 Adrian -----Original Message----- From: James Smith [mailto:[EMAIL PROTECTED] Sent: 18 November 2006 18:54 To: CF-Talk Subject: MySQL to MSSQL Help Desperate problem, I have to convert a complex query from MySQL to MSSQL. I have been using MSSQL for about a week so my knowledge is limited. The query is... --------------------------------------------------------- SELECT o.OrderNumber, o.OrderID, o.MessageDate, o.PostageHandeling, o.DispatchTo, o.AddressLine1, o.AddressLine2, o.TownCity, o.County, o.PostCode, o.Country, i.BuyersPrice, i.Quantity, i.SKU, q.Location, m.MediaTypeName, m.MediaTypeID, m.MediaMass, p.PlatformName, s.Title, s.Artist, concat(s.Title, IF(m.MediaTypeID = 2,concat(' - ', s.Artist),''), ' (', IF(m.MediaTypeID = 3,p.PlatformName,m.MediaTypeName), ')') AS SuperTitle, if(o.country = 'United Kingdom',if(i.BuyersPrice * i.Quantity > 49.99,"SD",IF(i.buyersPrice * i.Quantity > 34.99,"RD",IFNULL(f.flatsortcode,'XX'))), if(i.BuyersPrice * i.Quantity > 39.99,"IS", if(o.country IN ('Austria','Belgium','Denmark','Finland','France','Germany','Ireland','Italy ','Luxembourg','Netherlands','Portugal','Spain','Sweden','Switzerland'),'EUR ', if(o.country IN ('Australia','Brazil','Canada','Hong Kong','Iceland','Japan','Norway','Republic of Korea','Mexico','New Zealand','South Africa','USA'),'ROW', 'CHK' ) ) ) ) AS BagNumber FROM amazonemailitemdetails i JOIN amazonemailorderdetails o ON (i.OrderID = o.OrderID) JOIN StockQuantities q ON (q.StockID = i.SKU) JOIN stockitemdetails s ON (s.ItemID = q.ItemID) JOIN MediaTypes m ON (m.MediaTypeID = s.MediaType) JOIN Platforms p ON (p.PlatformID = s.Platform) LEFT JOIN flatsortareas f ON (left(o.PostCode,CASE o.PostCode REGEXP '^[a-z|A-Z][0-9]' when 1 then 1 else 2 END) = f.postcodearea) WHERE o.Printed = 0 ORDER BY q.Location --------------------------------------------------------- All of this must remain in the query, no processing can be done after the query is finished. This query runs fine in MySQL but the format of "IF" statements in MSSQL is very diferent and the documentation I have here is no help at all. This is also going to fail on the REGEXP in the last join, does anyone know how this can be achieved in MSSQL? This is fairly urgent, in fact I need this working by Monday morning or all hell will break loose. -- Jay ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:261032 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
