If statment to SQL ??

ahmed_zedan · #1 (**permalink**) 01-19-06, 07:00

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;

healdem · #2 (**permalink**) 01-19-06, 07:20

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.

disruptivehair · #3 (**permalink**) 01-20-06, 18:21

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.

Peter.Vanroose · #4 (**permalink**) 01-24-06, 08:24

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech