Forum

HomeHomeCoreCoreGeneralGeneralAgain Nested IIFAgain Nested IIF
Previous
 
Next
New Post
8/8/2012 10:31 AM
 

Hello,

 

It's not the first time that this problem is discussed but could somebody provide us a code of a nested IIF which is working? After passing a full day to rewrite it with or without \ character, I simply wonder if it's simply possible to use a nested IIF.

 

This is my current code. I tried to escape each specific characters [,],{,}," but the code which works best (but not compeletely) is the following. Nevertheless the condition [chkUserID,Form]'='1' is never fired.

 

SELECT
 CustomerID,
 Username,
 Company,
 FirstName,
 LastName,
 Classification,
 SalesmanName,
 UserId
FROM [SK_CRM_vw_CustomersList]
{IIF,"'[mSearch,Form]'=''","",
{IIF,"'[chkUserID,Form]'='1'",WHERE UserID=\"@mSearch,WHERE (Username LIKE '%@mSearch%'
OR Company LIKE '%@mSearch%'
OR FirstName LIKE '%@mSearch%'
OR LastName LIKE '%@mSearch%'
OR Classification LIKE '%@mSearch%'
OR SalesmanName LIKE '%@mSearch%')\}}

 

Any advice is appreciated. I can find another way to make the work but as I spend so much time to use nested IIF, I wish to know if it's possible :-)

 

Thanks,

 

Dom

New Post
8/8/2012 11:26 AM
 

I couldn't test it, but you can try this:

 

 SELECT
 CustomerID,
 Username,
 Company,
 FirstName,
 LastName,
 Classification,
 SalesmanName,
 UserId
FROM [SK_CRM_vw_CustomersList]


{IIF,"'[mSearch,Form]'=''","","\{IIF,\"'\[chkUserID,Form]'='1'\",\" WHERE UserID= @mSearch\",\" WHERE (Username LIKE '%@mSearch%'
OR Company LIKE '%@mSearch%'
OR FirstName LIKE '%@mSearch%'
OR LastName LIKE '%@mSearch%'
OR Classification LIKE '%@mSearch%'
OR SalesmanName LIKE '%@mSearch%')\}"}
New Post
8/8/2012 11:29 AM
 
I just updated the query as I noticed an error.
New Post
8/8/2012 11:44 AM
 
I can assert that, while confusing, nested IIF statements work fine.  I'm having trouble deciphering the intent of your query so I'll just post the mental steps to construct one:
 

Consider the basic statement: {IIF,"a","b","c"}

1) Start with this basic structure.  All three components MUST exist.  If you don't have an "else" then leave it empty like {IIF,"a","b",""} or you can even omit the quotes like this: {IIF,"a","b",} but the IIF must always contain 3 commas.
2) You will almost always want to escape all {curly braces}, [square brackets] and "double quotes" inside the quotes of the b and c parts.  (Don't escape backslashes just because they are backslashes.)
Note: Don't escape your "a" part or IIF will evaluate its "true/false" based on the un-expanded value.
3) If you have an IIF that contains another IIF (or any other OWS tag for that matter), follow the same rules as you build the outer IIF (see 1-2 above)  This means that, when you place your {IIF...} tag inside the b or c parts, you will need to escape all of those characters mentioned in #2 above - even if they were already escaped - just add another backslash in front of it.

Confused yet?  Here is an example to follow along:

(1)

Start with an IIF tag: {IIF,"","",""}

 

(2)

Let's checks the value of MODULEID and display two different messages:

Condition: [ModuleId,System] > 100
Then Message: [ModuleID,System] greater than "100"
Else Message: [ModuleID,System] is not greater than "100"

The correct syntax is:
{IIF,"[ModuleId,System] > 100","\[ModuleID,System\] greater than \"100\"","\[ModuleID,System\] is not greater than \"100\""}

Note that the condition is NOT escaped but the quotes and brackets in the THEN and ELSE portions are.

(3)

Next, lets say that we want to make the above IIF the "then" portion of another IIF that checks TabID.


The syntax without adding the 2nd IIF (in the THEN) is:
{IIF,"[PortalID,System] = 0","","Not on Portal 0"}

 

Now, take the first IIF and put a backslash in-front of EVERY {, }, [, ] and " character.  This gives us:
\{IIF,\"\[ModuleId,System\] > 100\",\"\\[ModuleID,System\\] greater than \\"100\\"\",\"\\[ModuleID,System\\] is not greater than \\"100\\"\"\}

 

Now paste it into the THEN of the enclosing tag:
{IIF,"[PortalID,System] = 0","\{IIF,\"\[ModuleId,System\] > 100\",\"\\[ModuleID,System\\] greater than \\"100\\"\",\"\\[ModuleID,System\\] is not greater than \\"100\\"\"\}","Not on Portal 0"}

 

 

While hard to read, it should work like a champ.

BTW: if you find escaping these characters by hand tedious, there is a shortcut in OWS: Highlight the code you want to escape and press ctrl-E (think ESCAPE.)  To un-escape, highlight the section and press CTRL-R (think REVERT.)  Some previous version of OWS didn't escape everything with these keys but the later versions work correctly.

Enjoy
New Post
8/8/2012 11:47 AM
 
I hate re-reading my posts after I click SEND. My second example IIF is obviously checking for PortalID rather than TabID. I changed my mind about the example in mid-stream.
Previous
 
Next
HomeHomeCoreCoreGeneralGeneralAgain Nested IIFAgain Nested IIF


 

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA • info@openwebstudio.com

Bookmark & Share Bookmark and Share