Creating a Remote View with IIF()

[swguynos]

Hi,

I have a VFP remote view that has a where condition that says something like.

WHERE
Iif(?rnApp=1, .T., tblHeader.iappid=?riAppId)
(and similar ones .....)



I read that IIF() needs to be replaced with Case statements.
I tried it, but does not seem to work. Has anyone dealt with a similar situation? Any help would be appreciated.

 

[rgbean]

The in-line case (ICASE) was introduced in VFP 9.0, but in your example, I'm not sure it would really improve readability or query speed.

As long as your code works well, why would you want to change it?

Rick

 

[swguynos]

This is a remote view. SQL Server does not like IIF() or ICase().

 

[Mike Lewis]

SqGuynos,

With a remote view, you must use the syntax and functions recognised by your back end. IIF() and the new ICASE() are both VFP functions, and are not necessarily recognised by the back end.

SQL Server's equivalent of ICASE() is CASE ... WHEN ... END. Other servers are different.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[swguynos]

MikeLewis,

You are right and I know that. But as I said, using case..when..end in my query for the remote view did not work. Can you point out the right usage with the example I had on my original thread?


Thanks

 

[Mike Lewis]

Swguynos,

as I said, using case..when..end in my query for the remote view did not work.

Sorry, I didn't see you saying that. I thought you were saying that IIF() doesn't work.

Can you point out the right usage with the example I had on my original thread?

What example?

Perhaps if you could just post the code that doesn't work --and also mention what error you were seeing -- we might be able to help.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[swguynos]

Mike,

This is the where condition I am using currently in the VFP Local view

WHERE Iif(?rnApp=1, .T., tblHeader.iappid=?riAppId)

I want to use the same expression in a remote view with SQL backend.

Can you please tell me what is the right syntax using Case...When..End ?


When I tried using it, ( I do not know if I used the case..when..end correctly), the sql says "Incorrect syntax near '='.

Any Ideas?

 

[Mike Lewis]

Swguynos,

First, I think the reason for your error is the use of .T. That's because .T. and .F. is VFP syntax. SQL Server does not have the concept of a logical field. The nearest equivalent is a bit field, which is either 0 or 1.

In general, the syntax of CASE is something like this:

CASE
WHEN x > 100 THEN 1
WHEN x BETWEEN 50 AND 100 THEN 2
WHEN x < 50 THEN 3
END

Remember, all of this is an expression, which means it returns a single value (a numeric value, in this example). If you use it in a WHERE, you will have to test the entire expression against a numeric variable.

Hope this helps.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)