IF in the Select statement

[DanNorris2000]

Trying to implement the following IF statement but without success in my syntax. Can anyone help?

Create sql view V650TY AS;
Select Ledger.pc,Ledger.uw,;
Contracts.ctrsts AS COMMUTED,;
Ledger.dev,Ledger.bookper,ledger.curr,;
IF(ledger.curr)="CAN" then ROUND(MTON(sum(Ledger.osloss * nRate)),2)AS OSLOS
else round(mton(sum(ledger.osloss)),2 as OSLOS;
endif,;
ROUND(MTON(sum(Ledger.osexp)),2)AS osexp,;

 

[weedz]

You can either implement a function call or use an immediate if.

luResult = IIF(condition, result1, result2)

In your case:

Create sql view V650TY AS;
Select Ledger.pc,Ledger.uw,;
Contracts.ctrsts AS COMMUTED,;
Ledger.dev,Ledger.bookper,ledger.curr,;
IIF(ledger.curr)="CAN" ,ROUND(MTON(sum(Ledger.osloss * nRate)),2), round(mton(sum(ledger.osloss)),2) as OSLOS;
,ROUND(MTON(sum(Ledger.osexp)),2)AS osexp,; etc.

HTH,
Weedz (Edward W.F. Veld)
My private project:

http://www.crowncap.demon.nl\info\crwnbase

Download the CrownBase source code !!

 

[DanNorris2000]

Looks goid except for a mysterious missing comma error I'm getting after I add the statement:
IIF(ledger.curr)="CAN",ROUND(MTON(sum(Ledger.osloss * nRate)),2),round(mton(sum(ledger.osloss)),2)as OSLOS,;
ROUND(MTON(sum(Ledger.osexp)),2)AS osexp,;

 

[derren]

You just need the extra parens around the statement:

IIF((ledger.curr)="CAN",ROUND(MTON(sum(Ledger.osloss * nRate)),2),round(mton(sum(ledger.osloss)),2)) as OSLOS Derren
[Mediocre talent - spread really thin]

 

[DSummZZZ]

It looks like it should be:

IIF(ledger.curr = "CAN"....

instead of
IIF(ledger.curr) = "CAN"...

Dave S.

 

[DanNorris2000]

Thanks,.. it's alive!