April 13, 2012 at 9:41 pm
Please see the query below .if I include the end_date in my select statement, my records counts are more. But I have to include them to qc the data as we have to show that when end date is removed from the where filter the row numbers are same as table which is joined.What could be the reason when we include one extra column it increases number of rows.
select a.oid,a.mid,b.end_date
from apple a join ball b
on a.oid =b.oid
where b.state ='TX'--- **and b.end_date is not null** If I remove this filter(end_date_ my rows are same as joined table , and at the same time I have to include end date in select statemnt to qc the data .and also keep the filter where end_date is null.
So how do I do this where I show the end_date as column and at the same time also show my filter of end_date and still not affect the rows count.
April 14, 2012 at 3:58 am
Please give us DDL of your tables and some sample data. I couldn't understand your problem by just looking at your query.
For better and quick solutions please check out the link in my signature to read/know how to post DDL and sample data.
April 14, 2012 at 1:00 pm
if I have follwing code:
select I.OID, I.segment,,
MAX(CASE WHEN S.SUBSCRIPTION_TYPE = '5' THEN 'Y' ELSE 'N' END ) PB_SUBS,
max(case when S.SUBSCRIPTION_TYPE ='12' then 'Y' else 'N' end) DAILY_TASTE,
MAX(CASE WHEN S.SUBSCRIPTION_TYPE ='8' THEN 'Y' ELSE 'N' END) COOKING_FOR_TWO
FROM MAIL_ID i JOIN SUBSCRIPTION_FCT S
ON I.IDENTITY_ID = S.IDENTITY_ID
WHERE S.SITE_CODE ='PB'and S.SUBSCRIPTION_END_DATE is not null
group by I.oid_customer_dim, I.segment
My rows counts are different, not same as my joined table:mail_id . SO in reality they want to know what happened to all those customers who don't show up in this query.
If I remove my filter S.SUBSCRIPTION_END_DATE is not null then I get almost same numbers as my joined table: mail_id.
But the confusion is that I have to have that filter of end_date and they want me to put end_date in select statement as well to qc the data if all the customers who were missing in my previous query show up as null. but if I keep that end_date in select statement my rows are much more higher.
Please help!!Hope this is less confusing!! just want to know that why select statements differ my row numbers.
April 15, 2012 at 1:15 pm
Looks like you have duplicated this thread
***The first step is always the hardest *******
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply