July 26, 2012 at 10:26 am
I am having issues joining 3 tables. My problem is that the (COUNT(dbo.IIRPS_HPOO_IHS.LPAR) is displaying twice the actual amount of rows. I think that the reason is because there are 2 JVMs within the LPAR. Does anyone have any advice on how to pull the correct values? Code is:
SELECT dbo.IIRPS_HPOO_JVM.LPAR,SUM(distinct OnlineMemory) AS OLMactual,((SUM(distinct MaxHeapSize) * 1.6) + (COUNT(dbo.IIRPS_HPOO_IHS.LPAR) * 1024) + 1024) AS OLMneed
FROM dbo.IIRPS_HPOO_JVM, dbo.IIRPS_HPOO_IHS, dbo.IIRPS_HPOO_LPAR
WHERE dbo.IIRPS_HPOO_JVM.LPAR = dbo.IIRPS_HPOO_IHS.LPAR AND dbo.IIRPS_HPOO_IHS.LPAR = dbo.IIRPS_HPOO_LPAR.LPAR
GROUP BY dbo.IIRPS_HPOO_JVM.LPAR
The structure of the tables is:
dbo.IIRPS_HPOO_JVM: LPAR, JVM, MaxHeapSize
dbo.IIRPS_HPOO_LPAR: LPAR, OnlineMemory
dbo.IIRPS_HPOO_IHS: LPAR
Thank you for your help.
July 26, 2012 at 10:29 am
As stated count is correct but not the one you are looking for, in that case fix would bt to filter out the unwanted JVM or just divide the result by two 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 26, 2012 at 10:31 am
The query needs to be dynamic though...so I tried dividing by Count(dbo.IIRPS_HPOO_JVM.JVM) it resulted in 1, which is definitely not correct.
July 26, 2012 at 10:40 am
Is LPAR unique in the IHS table? If so, select from there, and use Inline Select statements in your Select clause, instead of joins. That usually fixes this kind of thing.
Something like:
SELECT LPAR,
(select sum(onlinememory)
from dbo.IIRPS_HPOO_LPAR
where IIRPS_HPOO_LPAR.LPAR = IIRPS_HPOO_IHS.LPAR) as OnlineMemory,
(select count(*)
from dbo.IIRPS_HPOO_JVM
where IIRPS_HPOO_JVM.LPAR = IIRPS_HPOO_IHS.LPAR) as MaxHeapSize
FROM dbo.IIRPS_HPOO_IHS;
That's just an outline of it, you'll need to modify it to fit your actual business needs.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply