SQL errors

[dfauber]

I receive the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token = was not valid. Valid tokens: ) ,.

when using this sql statement:

SELECT * FROM wtecdlib.wqesmpllog WHERE IIF(PDTerm = '6 Months', IIF(EntryDate < '9/25/2002' - 180, DteRvd < ('9/25/2002' - 180) or DteRvd is null,0), IIF(EntryDate < '9/25/2002' - 365, DteRvd < ('9/25/2002' - 365) or DteRvd is null),0)

this statement is generated through asp/vbscript code. I have tried several different things in place of the '=' and received the same error on them all. This statement was taken directly out of an Access DB and just made to work in asp. Is there something I am missing, why doesn't this work???? Can someone please help me? Thanks in advance!

Nathan

 

[jen89]

What database are you using? I'm not sure, but I don't think you can use the IIF statement in SQL, it is a VB or VBA thing. Can you rewrite your where criteria and test that? Or comment it out starting from the where and see if that works?

 

[dfauber]

The DB I am reading from is on an AS/400, it is a DB2 DB. I have tried to comment out all but the first IIF statement and that didn't work. If you are right and you cannot use the IIF statement, is there an alternative that would accomplish the same thing? I basically need to evaluate a field 'PDTerm' (will either contain the string '6 months' or '12 months'). If PDTerm contains say '6 months' I need to compare the DDate field and see if it is more than 6 months from today and if so, select it. I could try two seperate selects but I need to display all the results in one sorted html table. I'm not the best with SQL so any help would be appreciated. Thanks!