If Statement in a Pass-Through Query

[Kenln]

I have a Pass-Through query in VBA. All (most) of the syntax works fine but I am having a problem with one of the select statements.

        MySql = MySql + "IIf(UOM='E',((COST*QTY)),IIf(UOM='C',((COST*QTY)/100),IIf(UOM='M',((COST*QTY)/1000)))) "

Everythinge seems to be working, but I can't get even a simple If statement to work. Of course this works well in a stardard Access query but since this is a pass-through I'm doing something wrong. If it helps we are using an AS-400 as a back-end.

 

[Bodisathva]

remember that when you execute a pass through query, all processing takes place on the server... IIF is an Access function that the server won't be able to interpret. You need to construct an SQL If-Else section or complete work on the recordset after you get the return from the server.

 

[Kenln]

Do you know the correct syntax for an SQL If-Else?

I have tried IIF, If, If-Then, etc...

 

[pbaldy]

I don't know about AS 400, but the equivalent on SQL Server would be the CASE statement. See if the AS 400 supports that.

CASE WHEN ... THEN ... ELSE ... END

 

[Kenln]

Would it look like

Dim MySql As String
     MySql = "Select "
     MySql = MqSql + " Blah As Blah, "
     MySql = MySql + "Case When UOM = 'C' Then (Cost * Qty)/100 End"

If I have the example right can I build a case 4 deep, i.e. a case within a case?
E = * 1
C = /100
M = /1000
Else = Fixed Amount???

 

[pbaldy]

Else is the equivalent of the false part of an IIf(). That one looks okay. I'd test a simple one to make sure the back end recognized it before trying to build a complicated one. Add an Else just to be sure:

MySql = MySql + "Case When UOM = 'C' Then (Cost * Qty)/100 Else 0 End AS Whatever"

 

[modest]

What baldy is sayin is right. more than likely your server will accept it because i believe the AS/400 uses iSeries SQL. The AS/400 is a DB2 database, right?

MySql = _
MySql + _
"CASE WHEN UOM='E' THEN COST*QTY " & _
"     WHEN UOM='C' THEN COST*QTY)/100 " & _
"     WHEN UOM='M' THEN COST*QTY)/1000) " & _
"END"

example:
Select FirstName, LastName, CASE WHEN FirstName='Brian' THEN 'Okay' WHEN FirstName='Sam' Then 'Not Okay' ELSE 'Doesnt Matter' END FROM TableName;

 

[Kenln]

I believe you are right. DB2 definitely iSeries SQL I'm not sure but the Case When End worked.

pbaldy and modest, I REALLY appreciate your help.Thank you many times.

Now if IsNull() works the same I can solve this problem.