Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is this SQL possible?

Status
Not open for further replies.

JRO061601

MIS
May 30, 2002
92
US
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.
 
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
 
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.
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top