Converting Iif statement from access to sql 2008 sql studio

Question

I am trying to add an iif cormula (from a prevuious query written in access) with 2 condition in 2008 SQL studio and I cant seem to get it right.. help?

the original iif statement was

((IIf([Qry_MSC Postal Code Conversion]![Current Service]=

[Qry_MSC Postal Code Conversion]![Coverage] Or [Qry_MSC Postal Code Conversion]![Coverage]="","","no match"))="no match"))

 

the complete query is below;

exec

 

dbo.DropTempTable

'#Qry_MSC_match_to_postal_code'

SELECT

 

distinct

a

.Merchant AS Merch_Num,

DMS_Merch_Num

= case when a.Merchant like 'E%' then '301'+Right(a.Merchant,14

)

 

when a.Merchant like 'C%' then '302'+Right(a.Merchant,14

)

 

ELSE '304'+Right(a.Merchant,14) end

,

a

.[Postal-Code],

a

.[Current Service],

a

.

Coverage

into

 

#Qry_MSC_match_to_postal_code

FROM

 

[#Qry_MSC_Postal_Code_Conversion] a

WHERE

 

a.[Current Service]<>

'Courrier'

Case

 

when a.current service = a.

coverage

 

then a.coverage=

''

 

else 'no match' end

;

---

Sophie B.

Answer 1

the original iif statement was

((IIf([Qry_MSC Postal Code Conversion]![Current Service]=

[Qry_MSC Postal Code Conversion]![Coverage] Or [Qry_MSC Postal Code Conversion]![Coverage]="","","no match"))="no match"))

 

---

Sophie B.

Hi Sophie,

The statement you post is actually equal to the following meaning:

[Qry_MSC Postal Code Conversion]![Current Service]<>[Qry_MSC Postal Code Conversion]![Coverage] and [Qry_MSC Postal Code Conversion]![Coverage]<>""

in your statement, the part "IIf([Qry_MSC Postal Code Conversion]![Current Service]=[Qry_MSC Postal Code Conversion]![Coverage] Or [Qry_MSC Postal Code Conversion]![Coverage]="","","no match")" must return "no match", then it will be equal to "no match" at the right side of the "=" operator, so the entire statement can be evaluated to true. So, the whole statement can be converted to the one above i provide. The statement should be easily added in your sql query. There is no need to "case.. when..." logical statement.

I feel a little hard to completely understand the statment you provided, once you do, your quesiton will be easy to answer. :)

thanks,
Jerry

Answer 2

Hi jerry,

 yes I see what you are saying and that does indeed make sense since I want to see any records where 

[#Qry_MSC_Postal_Code_Conversion]

.[Current Service] <>

[Qry_MSC Postal Code Conversion]

.

[Coverage] 

OR

[Qry_MSC Postal Code Conversion]

.[Coverage] is null

so I re-wrote the query as per below but I am now getting  "inccorect syntax near the keyword into" I cant seem to see what is wrong with my into statement to create a temp table.

 

exec

dbo.DropTempTable

'#Qry_MSC_match_to_postal_code'

SELECT

DISTINCT

a

.Merchant AS Merch_Num,

DMS_Merch_Num

= case when a.Merchant like 'E%' then '301'+Right(a.Merchant,14)

 

when a.Merchant like 'C%' then '302'+Right(a.Merchant,14)

 

ELSE '304'+Right(a.Merchant,14) end,

a

.[Postal-Code],

a

.[Current Service],

a

.Coverage

where

[#Qry_MSC_Postal_Code_Conversion].[Current Service] <> [Qry_MSC Postal Code Conversion].[Coverage]

or

[Qry_MSC Postal Code Conversion].[Coverage]is

null

into

#Qry_MSC_match_to_postal_code

FROM

[#Qry_MSC_Postal_Code_Conversion] a

thank you again Jerry :)

---

Sophie B.

Answer 3

I have not been able to understand the question well but I think

IIF([Qry_MSC Postal Code Conversion]![Current Service]= [Qry_MSC Postal Code Conversion]![Coverage] Or [Qry_MSC Postal Code Conversion]![Coverage]="","","no match")

would function well if its used in the select query or u can use it in the where clause as stated by jerry. Please elaborate a bit more on the question to find an apt solution.

---

Database consultant NY, USA - Intelcs.com