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.
Author |
Topic |
theKid27
Starting Member
21 Posts |
Posted - 2013-02-24 : 22:38:44
|
Hi Experts/Members, I'm trying to to convert the below Ms.Access query to SQL 2008 server queryMs. Access:-((IIf([SHIP_MEANING].[MEANING] Like "*EXP*","Direct",IIf([SHIP_MEANING].[MEANING] Like "*RDC*" Or [SHIP_MEANING].[MEANING] Like "*CALI*","RDC","Direct")))="Direct")) My conversion to SQL:(TB_OTS_CUST_BASE_DATA.[SHIPPING_METHOD_CODE] LIKE '%EXP%''Direct''%RDC%') OR (TBM_SHIP_MEANING.LOOKUP_CODE LIKE '%CALI%''%RDC%''%DIRECT%'))I couldn't get the correct answer but it doesn't show me error.Kindly hope that you all could help on this. Thank you. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-24 : 23:16:34
|
the equivalent syntax in t-sql isCASEWHEN ([SHIP_MEANING].[MEANING] Like '%EXP%') THEN 'Direct'WHEN ([SHIP_MEANING].[MEANING] Like '%RDC%' Or [SHIP_MEANING].[MEANING] Like '%CALI%' ) THEN 'RDC'ELSE 'Direct'END = 'Direct'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
theKid27
Starting Member
21 Posts |
Posted - 2013-02-25 : 01:23:52
|
Hi visakh16 Thanks for helping to convert into t-sql, however my output is still wrong. Do you have any idea on it. Ms.Access:- UPDATE SHIP_MEANING RIGHT JOIN Cust ON SHIP_MEANING.LOOKUP_CODE = Cust.SHIPPING_METHOD_CODE SET Cust.[Measure Line] = 'Okay', Cust.[Measure Header] = 'Okay'WHERE (((Cust.[Customer Measure Line])="NotOkay") AND ((IIf(IsNull([Cust].[AD]),False,CDate([Cust].[AD])+[Cust].[TT]-CDate([Cust].[RD])>-3))<>False) AND ((Cust.[Unit]) In ("M1","M2","M2")) AND ((IIf(IsNull([Cust].[AD]),False,CDate([Cust].[AD])>CDate("24/Nov/2008")))<>False) AND ((IIf(IsNull([Cust].[AD]),False,CDate([Cust].[AD])-CDate([Cust].[SSD])<-7))<>False) AND ((Cust.Country)="US") AND ((IIf([SHIP_MEANING].[MEANING] Like "*EXP*","Direct",IIf([SHIP_MEANING].[MEANING] Like "*RDC*" Or [SHIP_MEANING].[MEANING] Like "*CALI*","RDC","Direct")))="Direct")) T-SQL:-UPDATE Cust SET Cust.[Measure Line] = 'Okay', Cust.[Measure Header] = 'Okay' FROM Cust INNER JOIN SHIP_MEANING ON SHIP_MEANING.LOOKUP_CODE = Cust.SHIPPING_METHOD_CODE WHERE ((Cust.[Measure Line] = 'NotOkay') AND (Cust.[Unit] IN ('M1','M2','M2')) AND (Cust.Country='US') AND (Cust.[AD] IS NOT NULL) AND (Cust.[AD] > CONVERT(datetime,'24/11/2008',103)) AND (Cust.[AD]-Cust.[SSD]<-7) AND (Cust.[AD]+Cust.TT-Cust.[RD]>-3) AND (CASE WHEN (SHIP_MEANING.[MEANING] Like '%EXP%') THEN 'Direct'WHEN (SHIP_MEANING.[MEANING] Like '%RDC%' Or SHIP_MEANING.[MEANING] Like '%CALI%' ) THEN 'RDC'ELSE 'Direct'END = 'Direct') )Thanks |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
theKid27
Starting Member
21 Posts |
Posted - 2013-02-25 : 18:12:38
|
nope i m using sql 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 00:00:52
|
[CODE]UPDATE CustSET Cust.[Measure Line] = 'Okay',Cust.[Measure Header] = 'Okay'FROM Cust INNER JOIN SHIP_MEANING ON SHIP_MEANING.LOOKUP_CODE = Cust.SHIPPING_METHOD_CODEWHERE ((Cust.[Measure Line] = 'NotOkay')AND (Cust.[Unit] IN ('M1','M2','M2'))AND (Cust.Country='US') AND (Cust.[AD] IS NOT NULL)AND (Cust.[AD] > CONVERT(datetime,'24/11/2008',103))AND (Cust.[AD]-Cust.[SSD]<-7)AND (Cust.[AD]+Cust.TT-Cust.[RD]>-3)AND (SHIP_MEANING.[MEANING] not Like '%RDC%' and SHIP_MEANING.[MEANING] NOT Like '%CALI%' ) [/CODE]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
theKid27
Starting Member
21 Posts |
Posted - 2013-02-26 : 01:27:17
|
probs solved thanks all :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 01:29:46
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|