#1 IIF to CASE

[yshmidt]

I have a SQL statement in the VFP code, which is using IIF() function. I was trying to configure the parser to conver this function to the appropriate "CASE" statement in SQL, but could not do it. Here is what I did:
m.oConfig=createobject("VFPSQLPARSER.CONFIGURATION.1")
oConvertor=createobject("VFPSQLPARSER.PARSER.1")
oConvertor.ConfigureWith(m.oConfig)
m.oConfig.Add("iif","CASE WHEN %1 THEN %2 ELSE %3 END")
m.sSQL=[SELECT UPPER(Warehouse) as warehouse,Whno,gldivno,UniqWh,Wh_gl_nbr,Autolocation,IIF(Default,'B','A') AS cDefault from warehous where !EMPTY(whno)]
m.sSQL2=oConvertor.Convert(m.sSQL)
?m.ssql2
Please let me know if I am doing anything wrong.

Thank you
Yelena
yshmidt@hotmail.com

[Remus Rusanu]

Logged In: YES
user_id=36698
Originator: NO

Hello Yelena,

Unfortunately I don't even have a VFP to test with anymore, have stopped working on this project for many years now.
The configuration you add looks like the right one. What converted output do you get?

Thanks,
~ Remus

[yshmidt]

Logged In: YES
user_id=2026088
Originator: YES

Remus,
Thank you for prompt response.
This is what the string looks like after conversion
SELECT {fn UCASE(Warehouse)} AS warehouse,Whno,gldivno,UniqWh,Wh_gl_nbr,Autolocation,IIF(Default,'B','A') AS cDefault FROM warehous WHERE NOT (whno=' ')
It seems that my conversion was wrong and the program just ignored it. Unfortunately I am not familiar yet with C++ and it will be hard for me to find the place in the code. Can you help me with it or point me in the right direction please? I am in the process of adding SQL server and MY SQL in addition to the DBF data to our product as additional back end. I need to convert my code, I have a lot of SQL command using IIF() and ICASE() and NVL() functions. You have most of the parsing done and I was hoping that I could use your library instead writing my own parser. If not I was thinking to use your parser and then write additional parser in VFP to add missing conversion. Not very elegant solution though.
I appreciate any help I can get.
Thank you
Yelena.