To check ALL condition in Teradata

Database

To check ALL condition in Teradata

Hi All,

I have a below requirement:-

Table1:-

GROUP_ID - MEMBER_ID

A - A

A - B

A - C

M - M

M - N

M - O

X - X

X - Y

X - Z

Table2:-

MEMBER_ID - MEMBER_FLAG

A - Y

B - Y

C - Y

M - Y

N - ?

O - Y

X - N

Y - N

Z - Y

I want to select groups from Table1 whose all members have MEMBER_FLAG as "Y" in Table2.

The expected output is:-

GROUP_ID - MEMBER_ID

A - A

A - B

A - C

The groups M and X should not come in the output as members have different flags in Table2.

Could you please advise.

Thanks a lot in advance.

Regards,

Sagar

4 REPLIES
N/A

Re: To check ALL condition in Teradata

Hi Sagar,

simply add:

SELECT
....
QUALIFY
MIN(MEMBER_FLAG) OVER (PARTITION BY GROUP_ID) = 'Y'
AND MIN(MEMBER_FLAG) OVER (PARTITION BY GROUP_ID) =
MAX(MEMBER_FLAG) OVER (PARTITION BY GROUP_ID)

Dieter

Re: To check ALL condition in Teradata

Hi Dieter,

Many thanks for your inputs and it worked well for the said scenario.

I got the data like below:-

GROUP_ID - MEMBER_ID - MEMBER_FLAG

M - M - N

M - N - N

M - O - N

X - X - Y

X - Y - N

X - Z - N

All members of group M has same value i.e. N.

Members of group X has mixture of values i.e. N and Y.

Here, I want to extract group having flag as N for all members of the group

When I tried to apply the same logic by changing the flag to N in given QUALIFY as below. I got both the groups.

SELECT....

QUALIFY     

MIN(MEMBER_FLAG) OVER (PARTITION BY GROUP_ID) = 'N'

AND MIN(MEMBER_FLAG) OVER (PARTITION BY GROUP_ID) =     MAX(MEMBER_FLAG) OVER (PARTITION BY GROUP_ID)

Please advise any changes required in QUALIFY for this scenario.

Thanks a lot in advance.

Sagar. 

N/A

Re: To check ALL condition in Teradata

Hi Sagar,

this QUALIFY will return only group M.

If not, you did something wrong, so better show your actual query.

Dieter

Re: To check ALL condition in Teradata

Hi Dieter,

I got it what I expected. Many Thanks.

Sagar.