Is this SQL possible?

[JRO061601]

Can MSTR generate this?

SELECT A.ATTRIBUTE, B.COUNT(PK)
FROM LOOKUP_TBL A, FACT_TBL B
WHERE A.PK=B.PK (+)
GROUP BY A.ATTRIBUTE

So I want to count rows in the fact table that aren't in the lookup table, group by a higher level attribute, and have the extra show up as a row with a NULL as the attribute element.

Thanks.

 

[sunnyphoenixier]

It is hard, or impossible from my experience, to get this SQL from MSTR. Why don't you query the database directly? BTW, this SQL doesn't answer the question you are asking. You probably want

Select SUM(1)
from B
where PK not in (Select distinct PK from A);

Phoenixier

 

[JRO061601]

Thanks for the reply. I meant to say "rows in the fact table that are AND aren't in the lookup table" - hence the outer join. Didn't think it was possible, but wanted to check.

 

[Z3]

Yes it is. Create a report filter with And Not operator. The SQL will be more like this:
SELECT COUNT(B.PK)
FROM FACT_TBL B
WHERE not exists
(select * from LOOKUP_TBL A where A.PK=B.PK)

Have fun!