none
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.
    Tuesday, November 29, 2011 8:00 PM

All replies

  • 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

    • Proposed as answer by rok1 Sunday, December 04, 2011 5:34 AM
    Thursday, December 01, 2011 8:20 AM
    Moderator
  • 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.
    Tuesday, December 06, 2011 1:39 PM
  • 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
    • Edited by DBBlogger Wednesday, December 14, 2011 2:11 AM
    Wednesday, December 14, 2011 2:10 AM