| Author |
 Topic  |
|
|
theKid27
Starting Member
16 Posts |
 Posted - 02/24/2013 : 22:38:44
|
Hi Experts/Members,
I'm trying to to convert the below Ms.Access query to SQL 2008 server query
Ms. 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
India
47093 Posts |
Posted - 02/24/2013 : 23:16:34
|
the equivalent syntax in t-sql is
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'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
theKid27
Starting Member
16 Posts |
Posted - 02/25/2013 : 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
Flowing Fount of Yak Knowledge
3829 Posts |
|
|
theKid27
Starting Member
16 Posts |
Posted - 02/25/2013 : 18:12:38
|
| nope i m using sql 2008 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47093 Posts |
Posted - 02/26/2013 : 00:00:52
|
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 (SHIP_MEANING.[MEANING] not Like '%RDC%'
and SHIP_MEANING.[MEANING] NOT Like '%CALI%' )
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 02/26/2013 00:40:55 |
|
|
|
theKid27
Starting Member
16 Posts |
Posted - 02/26/2013 : 01:27:17
|
| probs solved thanks all :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47093 Posts |
Posted - 02/26/2013 : 01:29:46
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
SQL IIF -Please Help *urgent from Ms.Access 2 SQL
