Filer query help

Database
Enthusiast

Filer query help

Hi Guys,

 

Need your urgent help:

 

I have data as below in a tabled called PERSON.

 

PERSON_ID  TYPE   BEST_ADDRESS

121                 PHY          Y

121                 PHY          N

121                 MAIL         N

122                 PHY          Y

123                 SHIP         Y

124                 MAIL         N

125                 PHY          N

 

MY outout should be as follows:

 

PERSON_ID  TYPE   BEST_ADDRESS

121                 PHY          Y

121                 MAIL         N

122                 PHY          Y

123                 SHIP         Y

124                 MAIL         N

 

In the above output result we are elimanating BEST_ADDRESS='N' for type PHY only.

 

So in my query i need to add a filter in such a way, for type PHY, only BEST_ADDRESS 'Ý' should be picked, and for all other types every record should be picked.

 

How can i achieve this in filter as below

 

Select *from

person

where

???

 

Regards

John

5 REPLIES 5
Teradata Employee

Re: Filer query help

Hi John,

 

You have two solutions.

The first one is coding your logic exactly the way you thought it, using the NOT operator :

select PERSON_ID, TYPE, BEST_ADDRESS
  from PERSON
 where not(TYPE = 'PHY' and BEST_ADDRESS = 'N');

The second one is translating the NOT operator like this :

select PERSON_ID, TYPE, BEST_ADDRESS
  from PERSON
 where TYPE         <> 'PHY'
    or BEST_ADDRESS <> 'N';

Keep in mind that I said two solutions but there are mathematicaly the same (in the explain plan, the first one filters as the second one).

Ambassador

Re: Filer query help

I always got problems writing boolean logic like Waldar's :-)

 

There's a 3rd, explicit/long version:

select PERSON_ID, TYPE, BEST_ADDRESS
  from PERSON
 where (TYPE = 'PHY' AND BEST_ADDRESS = 'Y')
    or TYPE <> 'PHY';

 

Highlighted
Teradata Employee

Re: Filer query help

To be honest, I find it easier to write, but harder to re-read (even if I wrote it).

It 100% needs a comment linking to the business rule.

Enthusiast

Re: Filer query help

Many thanks Walder. It worked like a charm.. Appreciate your timely help.

Tags (1)
Enthusiast

Re: Filer query help

Thanks Dieter for your alternative solution. Tried and it worked too.