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:261031
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
