Hi!
I have a QaaWS query that builds the SQL differently depending on if it is run through QaaWS (correct results) or from within Dashboards 4.0 (wrong results). I checked all my mappings and then later had the DBA run a trace… you can see below that the two sql statements are different. I’ve rearranged the filters in my QaaWS query without any affect. Any ideas?
/* GETS BUILT CORRECTLY & RETURNS CORRECT RESULTS (FROM WITHIN QaaWS) */
SELECT DISTINCT
CASE ‘Employee’
WHEN ‘Supervisor’ THEN DIM_H155SupvEmplsMatrix.SupvID
WHEN ‘IT Service/Activity’
THEN DIM_H155SupvEmplsMatrix.ITServiceActivityID
WHEN ‘Employee’ THEN DIM_H155SupvEmplsMatrix.EmployeeID
WHEN ‘Requesting Dept’ THEN DIM_H155SupvEmplsMatrix.RequestingDeptCd
WHEN ‘Assigned Group’ THEN DIM_H155SupvEmplsMatrix.GroupNameCd
WHEN ‘SMART Category’ THEN DIM_H155SupvEmplsMatrix.CategoryStringCd
WHEN ‘Priority’ THEN DIM_H155SupvEmplsMatrix.PriorityCd
END
FROM DIM_H155SupvEmplsMatrix
WHERE (
(
(‘Employee’ = ‘Assigned Group’ AND DIM_H155SupvEmplsMatrix.AssignedGroupDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’)
OR ((‘Employee’ = ‘Supervisor’ OR ‘Employee’ = ‘Employee’) AND DIM_H155SupvEmplsMatrix.AssignedEmplDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’)
OR (( DIM_H155SupvEmplsMatrix.AssignedEmplDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’ OR DIM_H155SupvEmplsMatrix.AssignedGroupDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’)
AND ( ‘Employee’ <> ‘Employee’ AND ‘Employee’ <> ‘Supervisor’ AND ‘Employee’ <> ‘Assigned Group’ ))
OR DIM_H155SupvEmplsMatrix.EnteredDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’
OR DIM_H155SupvEmplsMatrix.OrigCleanedCloseDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’
)
AND ( DIM_H155SupvEmplsMatrix.IncidentType IN (‘IR’,‘SR’,‘PM’,’’))
AND DIM_H155SupvEmplsMatrix.SupvID = ‘0000016351’
)
=============================================
/* GETS BUILT WRONG AND RETURNS WRONG RESULTS (FROM WITHIN DASHBOARDS) */
SELECT DISTINCT
case ‘Employee’
when ‘Supervisor’ then DIM_H155SupvEmplsMatrix.SupvID
when ‘IT Service/Activity’ then DIM_H155SupvEmplsMatrix.ITServiceActivityID
when ‘Employee’ then DIM_H155SupvEmplsMatrix.EmployeeID
when ‘Requesting Dept’ then DIM_H155SupvEmplsMatrix.RequestingDeptCd
when ‘Assigned Group’ then DIM_H155SupvEmplsMatrix.GroupNameCd
when ‘SMART Category’ then DIM_H155SupvEmplsMatrix.CategoryStringCd
when ‘Priority’ then DIM_H155SupvEmplsMatrix.PriorityCd
end
FROM
DIM_H155SupvEmplsMatrix
WHERE
(DIM_H155SupvEmplsMatrix.SupvID = ‘0000016351’
AND ( DIM_H155SupvEmplsMatrix.IncidentType = ‘IR’ OR DIM_H155SupvEmplsMatrix.IncidentType = ‘SR’ OR DIM_H155SupvEmplsMatrix.IncidentType = ‘PM’ OR DIM_H155SupvEmplsMatrix.IncidentType = ‘’ )
AND (DIM_H155SupvEmplsMatrix.EnteredDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’ OR DIM_H155SupvEmplsMatrix.OrigCleanedCloseDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’)
AND (‘Employee’ <> ‘Employee’ AND ‘Employee’ <> ‘Supervisor’ AND ‘Employee’ <> ‘Assigned Group’)
AND (DIM_H155SupvEmplsMatrix.AssignedEmplDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’ OR
DIM_H155SupvEmplsMatrix.AssignedGroupDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’)
AND DIM_H155SupvEmplsMatrix.AssignedEmplDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’
AND (‘Employee’ = ‘Supervisor’ OR ‘Employee’ = ‘Employee’)
AND (‘Employee’ = ‘Assigned Group’ AND DIM_H155SupvEmplsMatrix.AssignedGroupDate BETWEEN ‘09/01/2013 00:0:0’ AND ‘09/14/2015 00:0:0’)
)
Thanks for the help!!!
audpken (BOB member since 2014-11-03)