IIF() in WHERE clause 1

[jluost1]

I have posted a similar question. Here is another one.

My sql statement with ACCESS is like this:

------------------------------------
SELEC email FROM tblA
WHERE IIF(EXISTS(SELECT username FROM tblB WHERE username='ABC'),tblA.username='ABC',true)
-----------------------------------------

Basicly, it says, if username 'ABC' exists, get its email, otherwise, return all emails.

This is a valid statement for ACCESS. I am having a difficult time to convert it to SQL7.

The real sql is much more complicated than this one. What really matters is:
How can I add a condition in the WHERE clause based on another condition.

I cannot find a right way to handle it using CASE..WHEN statement.

Help please?

 

[rajeevnandanmishra]

Hi jluost1,
Here you go,

IF EXISTS(SELECT username FROM tblb WHERE username='ABC')
SELECT email FROM tblA WHERE username='ABC'
ELSE
SELECT email FROM tblA

 

[tlbroadbent]

This is similar to the Access query.

SELECT email FROM tblA
WHERE EXISTS (SELECT username FROM tblB WHERE username = 'ABC') Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[aalmeida]

Also is good to remember that although the fuction IIF() Returns one of two numeric or string values determined by a logical test. It's target is more for DW's with dimentional data, where an example from BOL.
Numeric
The following example returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise:

IIf(IsEmpty(Measures.CurrentMember), 0, 1)

String
The following string returns the string "Yes" if Measures.CurrentMember is an empty cell, the string, "No" otherwise:

IIf(IsEmpty(Measures.CurrentMember), "Yes", "No&quot
Note that the result is not the Measures.CurrentMember actual value, but either the 2nd and 3rd parts of your syntax.

 

[jluost1]

Thank you both for your input. I know you are answering my question posted here. Unfortunately, these are not ideal solution to my problem.

The ideal solution is that I don't have to change the SELECT part of sql statement (my sql statement handles 8 scenarios, all are the same in SELECT part, but different in WHERE). It's like the following:

COMMON for all scenarios:

SELECT tblA.username,A,B,C,D
FROM tblA,tblB,tblC
WHERE tblB.username = tblA.username
AND ... AND ...

Here comes the hard part, for example:

AND IIF(EXISTS(SELECT username FROM tblD WHERE username='ABC'),tblA.username='ABC',true)

I know it is hard to explain, the key point is:

In some case, I want the sql result filtered by "tblA.username='ABC'" (by putting it into WHERE clause), in some case, I don't. Is it possible to do it with CASE..WHEN or other statements?

I tried the following:

AND tblA.username=
CASE (SELECT username FROM tblD WHERE username='ABC')
WHEN NULL THEN tblA.username
ELSE
'ABC'
END

I thought tblA.username = tblA.username is the same as "true". Unfortunately, if tblA.username is null, the whole sql statement still keeps out the record that tblA.username is null.

I understand the difficulty in following my words here. Please advise me as much as you can.

Thank you.

 

[rajeevnandanmishra]

Hi jluost1,
Replace myUserName with your username value.
SELECT email FROM tblA
WHERE 'myUserName' LIKE CASE WHEN EXISTS (SELECT username FROM tblB WHERE username='myUserName')
THEN tblA.username
ELSE '%' END

Hope it will work in your conditions.

 

[jluost1]

Hi, rajeevnandanmishra,

This is a very interesting proposal. Let me try it to see whether this is what I want.

Thanks.

 

[jluost1]

Fantastic! It works for me. I know you guys can help me out.

Thanks a million.