logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Padma  
#1 Posted : Wednesday, October 23, 2019 5:33:56 PM(UTC)
Padma

Rank: Member

Groups: Registered
Joined: 10/21/2019(UTC)
Posts: 16
United States

Below is the SQl I am using - How do i use restrictions in the main and sub query
SELECT
OMG_Outage_Report_APP.DataCenter,
OMG_Outage_Report_APP.AppName,
Format(OMG_Outage_Report_APP.OutageDate,'yyyy') as [Year],
Month(OMG_Outage_Report_APP.OutageDate)as [Month],
OMG_Outage_Report_APP.TestDomain,
SUM(Distinct OMG_Outage_Report_APP.OutageDurationInMins) AS [Sum_OutageDurationInMins]
FROM
OMG_Outage_Report_APP with (nolock)
WHERE
OMG_Outage_Report_APP.DataCenter = 'Staging'
and OMG_Outage_Report_APP.AppName = 'EN'
and Convert(VARCHAR(10),OMG_Outage_Report_APP.OutageDate,111) not in (Select CONVERT(VARCHAR(10), StartTime , 111) from OMG_DCMaintenance_Data where DataCenter = 'staging')
and Convert(VARCHAR(10),OMG_Outage_Report_APP.OutageDate,111) not in (Select CONVERT(VARCHAR(10), EndTime , 111) from OMG_DCMaintenance_Data where DataCenter = 'staging')
GROUP BY
OMG_Outage_Report_APP.DataCenter,
OMG_Outage_Report_APP.AppName,
Format(OMG_Outage_Report_APP.OutageDate,'yyyy'),
Month(OMG_Outage_Report_APP.OutageDate),
OMG_Outage_Report_APP.TestDomain

Please help
epf  
#2 Posted : Thursday, October 24, 2019 7:51:21 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
You can use common restrictions to do that: https://sealreport.org/Sources#lineTables_3

and check also report input values: https://sealreport.org/Reports#lineInputs
Padma  
#3 Posted : Thursday, October 24, 2019 4:18:40 PM(UTC)
Padma

Rank: Member

Groups: Registered
Joined: 10/21/2019(UTC)
Posts: 16
United States

Thank you.
I tried the report input values and tasks and the output was not quiet right

So, I have actually tried by dropping restrictions in the Restrictions Text as explained in the documentation and substituting those restrictions in my SQL statement.
This works, but somehow the restriction used in sub query does not seem to honor the value as I see that the output numbers are not right.
but if I substitute the actual value, it gets it right

My restriction names are:
Data Center and App Name
Both are enumerated lists

Then in my SQL i have substituted the restriction names as follows

The highlighted restrictions in the Where clause works, but the highlighted and underlined restrictions in Subquery does nto seem to work - Any suggestions??

SELECT
OMG_Outage_Report_APP.DataCenter,
OMG_Outage_Report_APP.AppName,
Format(OMG_Outage_Report_APP.OutageDate,'yyyy') as [Year],
Month(OMG_Outage_Report_APP.OutageDate)as [Month],
OMG_Outage_Report_APP.TestDomain,
SUM(Distinct OMG_Outage_Report_APP.OutageDurationInMins) AS [Sum_OutageDurationInMins]
FROM
OMG_Outage_Report_APP with (nolock)
WHERE
OMG_Outage_Report_APP.DataCenter = [DataCenter]
and OMG_Outage_Report_APP.AppName = [AppName]
and Convert(VARCHAR(10),OMG_Outage_Report_APP.OutageDate,111) not in (Select CONVERT(VARCHAR(10), StartTime , 111) from OMG_DCMaintenance_Data with(nolock) where OMG_DCMaintenance_Data.DataCenter = [DataCenter])
and Convert(VARCHAR(10),OMG_Outage_Report_APP.OutageDate,111) not in (Select CONVERT(VARCHAR(10), EndTime , 111) from OMG_DCMaintenance_Data with(nolock) where OMG_DCMaintenance_Data.DataCenter = [DataCenter])
GROUP BY
OMG_Outage_Report_APP.DataCenter,
OMG_Outage_Report_APP.AppName,
Format(OMG_Outage_Report_APP.OutageDate,'yyyy'),
Month(OMG_Outage_Report_APP.OutageDate),
OMG_Outage_Report_APP.TestDomain




epf  
#4 Posted : Friday, October 25, 2019 7:58:47 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Is it an Input Values or a Common Restriction ?
Can you reproduce your problem using Northwind and post the report so we can check the problem ?
Padma  
#5 Posted : Friday, October 25, 2019 4:38:34 PM(UTC)
Padma

Rank: Member

Groups: Registered
Joined: 10/21/2019(UTC)
Posts: 16
United States

Finally figured it out ..
I removed the restriction from subquery and replaced with
"where OMG_DCMaintenance_Data.DataCenter = OMG_Outage_Report_APP.DataCenter"


So
"where OMG_DCMaintenance_Data.DataCenter = [DataCenter])" replaced with
"where OMG_DCMaintenance_Data.DataCenter = OMG_Outage_Report_APP.DataCenter"

This gives the correct results.


Thank you!

Edited by user Friday, October 25, 2019 4:40:29 PM(UTC)  | Reason: Not specified

Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.