July 11, 2008 at 4:43 am
HI All,
We have a Report which calls the below Query. I just checked and found that there are calls to two views more than once, but there is nothing like a self join. But I could not say that the query is wrong right away. So, I want help in checking this. Please have a look at this and let me know if I can remove the RED colored lines and it would make no difference. (and ofcourse change to CRSC2 to CRSC1 and CC2 to CC1 in the SELECT list; and also change CRSC2.TargetClassInstanceID to CRSC1.TargetClassInstanceID in the last of WHERE ).
SELECT DISTINCT
CRSC1.SourceClassInstanceKeyValue AS [TargetSite],
CC1.FQDN AS [TargetDomainController],
CRSC2.SourceClassInstanceKeyValue AS [SourceSite],
CC2.FQDN AS [SourceDomainController],
CRCO.[Transport Type] AS [TransportType],
CRCO.[Manual Connection] AS ManualConnection
FROM [SC_Class_Rel_Connection Object_View] CRCO,
SC_ComputerDimension_View CD,
[SC_Class_Rel_Site-Computer_View] CRSC1,
[SC_Class_Rel_Site-Computer_View] CRSC2,
SC_Class_Computer_View CC1,
SC_Class_Computer_View CC2
WHERE ((CRCO.SourceClassInstanceID = CD.ComputerID) OR (CRCO.TargetClassInstanceID = CD.ComputerID))
AND CRCO.TargetClassInstanceID = CRSC1.TargetClassInstanceID
AND CC1.ClassInstanceID = CRSC1.TargetClassInstanceID
AND CRCO.SourceClassInstanceID = CRSC2.TargetClassInstanceID
AND CC2.ClassInstanceID = CRSC2.TargetClassInstanceID
And, I am not able to run both the queries and compare output, because if I keep these second references, a error is being thrown that the allowed number of table usage (260) is exceeded.
Thanks a lot,
Manoj Deshpande.
July 11, 2008 at 9:34 am
I cannot see any code in red. Could you use italics or bold?
When you mentioned that your view is called more than once, could you include this part of script?
July 11, 2008 at 9:51 am
This cleans up your script. Not sure if it'll produce what you're looking for. Can you provide the views, tables, sample data and expected output? And look at the bold code, you're using the same source fields for different "AS' names. That can't be right, can it?
SELECT DISTINCT
CRSC1.SourceClassInstanceKeyValue AS [TargetSite],
CC1.FQDN AS [TargetDomainController],
CRSC1.SourceClassInstanceKeyValue AS [SourceSite],
CC1.FQDN AS [SourceDomainController],
CRCO.[Transport Type] AS [TransportType],
CRCO.[Manual Connection] AS ManualConnection
FROM [SC_Class_Rel_Connection Object_View] CRCO,
SC_ComputerDimension_View CD,
[SC_Class_Rel_Site-Computer_View] CRSC1,
SC_Class_Computer_View CC1
WHERE ((CRCO.SourceClassInstanceID = CD.ComputerID) OR (CRCO.TargetClassInstanceID = CD.ComputerID))
AND CRCO.TargetClassInstanceID = CRSC1.TargetClassInstanceID
AND CC1.ClassInstanceID = CRSC1.TargetClassInstanceID
-- You can't be late until you show up.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply