RE: MySQL to MSSQL Help

[Mark A Kruger]

Here's some help with the conditional logic. You will have to test it of
course - I'm not sure I got every logic item right. It's far more readable
than the nested ifs though :)  I'm not sure about the REGEX in the where
clause. You can do REgex's in mssql but I believe you would need an add on
extended function ... I recall one called xp_regex_... something. IF you
google around you might find it. Good luck!

 
-Mark
 
Mark A. Kruger, CFG, MCSE
(402) 408-3733 ext 105
www.cfwebtools.com <http://www.cfwebtools.com/> 
www.coldfusionmuse.com <http://www.coldfusionmuse.com/> 
 <http://www.necfug.com/> www.necfug.com



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,
           s.Title +
              CASE
                WHEN m.MediaTypeID = 2 THEN + ' - ' + s.Artist
                WHEN m.MediaTypeID = 3 THEN p.PlatformName + m.MediaTypeName
                END
              AS SuperTitle,
             CASE
                WHEN o.country = 'United Kingdom' AND i.BuyersPrice *
i.Quantity > 49.99 THEN 'SD'
                WHEN o.country = 'United Kingdom' AND i.buyersPrice *
i.Quantity > 34.99 THEN 'RD'
                WHEN o.country = 'United Kingdom' AND isNull(f.flatsortcode)
THEN 'XX'
                WHEN o.country = 'United Kingdom' AND i.buyersPrice *
i.Quantity > 39.99 THEN 'IS'
                WHEN o.country IN ('Austria','Belgium','Denmark',
 
'Finland','France','Germany','Ireland','Italy',
 
'Luxembourg','Netherlands','Portugal','Spain',
                                   'Sweden','Switzerland')
                                        THEN 'EUR'
                WHEN o.country IN ('Australia','Brazil','Canada','Hong
Kong','Iceland',
                                   'Japan','Norway','Republic of
Korea','Mexico','New Zealand',
                                   'South Africa','USA')
                                       THEN 'ROW',
             ELSE  '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 




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