How to exclude? or Include based on True/False

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 19 Apr 2006 04:34 GMT

I'm not sure how to go about doing what I need, so I defer to your expertise...

I have a table which I need to query 1) [DestName] or 2) [TransferedTo]
depending on the value of 3) [Transfered]

Can someone help me with the criteria for this. Exactly what I need is this:

If [Transfered] = True then I want [TransferedTo]. There will be a
[DestName] if [Transfered] is True, but I only want the value of
[TransferedTo].

If [Transfered] is False, then I want [DestName].

Any help is appreciated.

Reply to this Message

Paul - 19 Apr 2006 04:43 GMT

Forgot to add that [DesName] and [TransferedTo] are text fields while
[Transfered] is a Y/N field.

Cheers

> I'm not sure how to go about doing what I need, so I defer to your expertise...
>
[quoted text clipped - 10 lines]
>
> Any help is appreciated.

Reply to this Message

Saran - 19 Apr 2006 05:23 GMT

Paul,

Below query might do what you expect.

Select iif(transfered,TransferedTo,DestName) from TableName

Regards,
Saran.

Reply to this Message

Paul B. - 19 Apr 2006 05:46 GMT

Saran,

Thanks, this works except now I have a bunch of blanks. Any idea how to get
rid of the blanks?

Cheers

> Paul,
>
[quoted text clipped - 4 lines]
> Regards,
> Saran.

Reply to this Message

Saran - 19 Apr 2006 06:51 GMT

Paul,

Couple of ways to do this.

1. Select iif(transfered,TransferedTo,DestName) as MyCol from
TableName Where not iif(transfered,TransferedTo,DestName) is null

2. Select * from (Select iif(transfered,TransferedTo,DestName)
as MyCol from TableName) TN Where not TN.Mycol is null

Use whichever you feel perform better.

Want to get rid of any other values? extend the WHERE condition.

Regards,
Saran.

Reply to this Message

Paul B. - 20 Apr 2006 16:13 GMT

Thanks Saran,

I couldn't get example 2 to work, but I did get the first one to work with
the inclusion of DISTINCT.

Thanks again for your help.

> Paul,
>
[quoted text clipped - 12 lines]
> Regards,
> Saran.

Reply to this Message