Need help to identify the issue with result set

Tools & Utilities
Enthusiast

Need help to identify the issue with result set

I have written a query which returns two fields named as MI_NBR, MI_CHANGE after certain joins and filters, (MI_CHANGE field will have only values either YES or NO)
When I run the query to select distinct MI_NBR where MI_CHANGE='YES' it returns 359 rows and When I run the query to select distinct MI_NBR where MI_CHANGE='NO' it returns 413838 rows.

But when I run the query to select distinct MI_NBR only without any MI_CHANGE filter, then it return 414074 rows. Ideally it should return 414197 (413838+359) rows. Can any one tell what the issue can be?

 

FYI, I checked MI_CHANGE contains values as either YES or NO

Regards,
Mayank

Accepted Solutions
Teradata Employee

Re: Need help to identify the issue with result set

It would appear that you have 123 values for MI_NBR where MI_CHANGE has YES for some rows and NO for others.

 

SELECT MI_NBR, SUM(CASE WHEN MI_CHANGE = 'YES' THEN 1 END) NBR_YES, SUM(CASE WHEN MI_CHANGE = 'NO' THEN 1 END) NBR_NO

FROM ...

GROUP BY MI_NBR

HAVING MIN(MI_CHANGE) <> MAX(MI_CHANGE);

 

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Need help to identify the issue with result set

It would appear that you have 123 values for MI_NBR where MI_CHANGE has YES for some rows and NO for others.

 

SELECT MI_NBR, SUM(CASE WHEN MI_CHANGE = 'YES' THEN 1 END) NBR_YES, SUM(CASE WHEN MI_CHANGE = 'NO' THEN 1 END) NBR_NO

FROM ...

GROUP BY MI_NBR

HAVING MIN(MI_CHANGE) <> MAX(MI_CHANGE);

 

Enthusiast

Re: Need help to identify the issue with result set

Thanks Fred.

 

Through the above query you provided I got 123 rows, where MI_NBR has both YES or NO in MI_CHANGE field. I have two questions, can you please answer them?

 

1. HAVING MIN(MI_CHANGE) <> MAX(MI_CHANGE); -- Purpose of this having clause?

 

2. MI_CHANGE is basically a field to determine if the MI_NBR is exisitng or it is the new one by comparing MI_NBR returned by currrent query with MI_NBR stored in history table which maintains the list of MI_NBR, below is the join that I have written in order to implement this.

 

SEL CURR.MI_NBR, 

CASE WHEN HIST.MI_NBR IS NULL THEN 'YES' ELSE 'NO' END AS MI_CHANGE

 

FROM MI_TBL CURR

 

LEFT JOIN MI_NBR_HIST HIST

ON CURR.MI_NBR=HIST.MI_NBR

 

It should return either YES or NO only for a particular MI_NBR, but why it is returning both YES and NO for same MI_NBR?

 

 

Regards,
Mayank
Highlighted
Teradata Employee

Re: Need help to identify the issue with result set

1. The HAVING clause is effectively filtering "where there is more than one (non-NULL) value".

2. If this is the full query, I can't explain. If this is a simplified form, I would suggest that you take one of the MI_NBR with both MI_CHANGE values and relatively few rows, and examine the detail row contents versus the query logic to understand why both values get generated. 

Enthusiast

Re: Need help to identify the issue with result set

Thanks Fred for your help.

 

The issue was with a join, the issue is fixed now. Now I am getting only one MI_CHANGE value for each MI_NBR.

 

 

Regards,
Mayank