Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    11

    Unanswered: If statment to SQL ??

    I have a statement written in microsoft access query, and I want to convert it to be applied on SQL Database or view or SQL query bilder or on ASP.Net
    microsoft Access Statement:

    Toal paid : IIf([TP$]>0,(Format([TP$],'Fixed') & ' $')) & IIf([TPU]>0,(Format([TPU],'Fixed') & ' Euro')) & IIf([TP$]+[TPU]=0,0)

    The result is a column as following

    30 $
    250 Euro
    20 $
    0 $

    How I can apply it in SQL view ? ?
    Thanks;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is this an ASP question, a SQL question, a SQL Server Question or JET Question?

    AFAK you can only use IIF in JET, its not available in any other SQL engine
    other SQL's use things such as case, switch etc.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by ahmed_zedan
    I have a statement written in microsoft access query, and I want to convert it to be applied on SQL Database or view or SQL query bilder or on ASP.Net
    microsoft Access Statement:

    Toal paid : IIf([TP$]>0,(Format([TP$],'Fixed') & ' $')) & IIf([TPU]>0,(Format([TPU],'Fixed') & ' Euro')) & IIf([TP$]+[TPU]=0,0)

    The result is a column as following

    30 $
    250 Euro
    20 $
    0 $

    How I can apply it in SQL view ? ?
    Thanks;
    Ahmed, SQL doesn't support IIF; it's a VBA function. You can duplicate its functionality by using CASE, though.

    For example:

    Value = CASE WHEN [expression] THEN [expression] ELSE [expression] END

    or

    VALUE = CASE WHEN columna.thing IS NULL THEN columnb.thing ELSE columna.thing END....for example.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ahmed_zedan
    IIf([TP$]>0,(Format([TP$],'Fixed') & ' $')) & IIf([TPU]>0,(Format([TPU],'Fixed') & ' Euro')) & IIf([TP$]+[TPU]=0,0)
    I am not familiar with Access; if I understand your query correctly, the standard SQL equivalent would be
    Code:
    SELECT CASE WHEN TP$ > 0 THEN TP$ || ' $'
                WHEN TPU > 0 THEN TPU || ' Euro'
                ELSE '-'
                END
    FROM   tablename
    (which assumes that TPU and TP$ will not both be strictly positive at the same time).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •