MySQL script to SQL help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MySQL script to SQL help

Author

Topic

dhilling
Starting Member

3 Posts

Posted - 2013-11-01 : 09:53:28

I have this select script that we are migrating to SQL. However, I can't seem to get it converted to work. even tried SQL migration no luck.

SELECT carriers.CarrierName,carrier_zips.CarrierID,carrier_zips.SCAC,carrier_zips.ServiceTerminalCode AS DestServiceTerminalCode,carrier_zips.DirectIndirectServiceCode AS DestDirectIndirectServiceCode,carrier_zips.DaysPastTerminalInbound AS DestDaysPastTerminalInbound,_origZips.OrigServiceTerminalCode,_origZips.OrigDirectIndirectServiceCode,_origZips.OrigDaysPastTerminalOutbound,IFNULL(carrier_terminal_matrix.DaysOrigTermToDestTerm,0) AS DaysOrigTermToDestTerm,IF(IFNULL(carrier_terminal_matrix.DaysOrigTermToDestTerm,0)=0,0,carrier_terminal_matrix.DaysOrigTermToDestTerm+carrier_zips.DaysPastTerminalInbound+_origZips.OrigDaysPastTerminalOutbound) AS TotalDays,IF(_origZips.OrigDirectIndirectServiceCode<>'D','I',IF(carrier_zips.DirectIndirectServiceCode<>'D','I','D')) AS ServiceType FROM carriers, carrier_zips, (SELECT carrier_zips.CarrierID AS OrigCarrierID,carrier_zips.ServiceTerminalCode AS OrigServiceTerminalCode,carrier_zips.DirectIndirectServiceCode AS OrigDirectIndirectServiceCode,carrier_zips.DaysPastTerminalOutbound AS OrigDaysPastTerminalOutbound FROM carrier_zips WHERE carrier_zips.ZipCode=?OrigZip) AS _origZips LEFT JOIN carrier_terminal_matrix ON (carrier_zips.CarrierID=carrier_terminal_matrix.CarrierID AND carrier_terminal_matrix.OriginTerminalCode=_origZips.OrigServiceTerminalCode AND carrier_terminal_matrix.DestinationTerminalCode=carrier_zips.ServiceTerminalCode) WHERE carrier_zips.ZipCode=?DestZip AND carriers.CarrierID=carrier_zips.CarrierID AND carrier_zips.CarrierID=_origZips.OrigCarrierID;

any help converting it to work in sql would be great!

Thanks Doug

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-01 : 10:06:50

Change IF into CASE

IF(carrier_zips.DirectIndirectServiceCode<>'D','I','D'))

into

CASE when carrier_zips.DirectIndirectServiceCode<>'D' then 'I' else 'D' end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources