March 29, 2016 at 7:34 am
Hi ,
I have two query and the explanation is
we run the query pasted in file OpenRejectedReport.txt. Then the output (let’s call it output 1) of that query is iterated in C# code (For loop) and for individual record, query presented in OpenRejectReportHelper.txt is run. If the result of second query is “1” (which means policy has been corrected), then that policy is removed from output 1. All the remaining policies with their latest status as “0” are displayed in the report.
OpenRejectedReport.txt
CREATE PROCEDURE [dbo].[spOpenRejectedPoliciesReport]
AS
BEGIN
SET NOCOUNT ON;
SELECTSystems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
CASE WHEN Coverstartdate is NULL then '' ELSE
CONVERT(VARCHAR(8), PolicyAudit.Coverstartdate, 112) END as Coverstartdate,
CASE WHEN ErrorCode is NULL then '' ELSE ErrorCode END as ErrorCode,
CASE WHEN ErrorMessage is NULL then '' ELSE ErrorMessage END as ErrorMessage,
CASE WHEN EmployerName is NULL then '' ELSE EmployerName END as EmployerName,
CASE WHEN Branch is NULL then '' ELSE Branch END as Branch,
CASE WHEN Workgroup is NULL then '' ELSE Workgroup END as Workgroup,
CASE WHEN LOB is NULL then '' ELSE LOB END as LOB,
CASE WHEN ProductCode is NULL then '' ELSE ProductCode END as ProductCode,
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateRejected',
CONVERT(VARCHAR(8),DATEDIFF(DAY, PolicyAudit.LastModifiedDate, GETDATE())) AS 'DaysSinceRejected',
(CASE WHEN (DATEDIFF(DAY, PolicyAudit.Coverstartdate, GETDATE())) IS null THEN '' else CONVERT(VARCHAR(8),DATEDIFF(DAY, PolicyAudit.Coverstartdate, GETDATE())) end) AS 'DaysSinceCoverStartDate'
FROM Systems
LEFT JOIN PolicyAudit ON Systems.ID = PolicyAudit.SourceSystemID
left Join RejectReason on RejectReason.PolicyAuditID=PolicyAudit.ID
WHERE PolicyAudit.Status = 2 AND
PolicyAudit.IsCurrent = 1 AND
Systems.FileTypeIndicator in('C','V')
END
Query 2 - OpenRejectReportHelper.txt
CREATE PROCEDURE [spHelperProcedureOpenRejectedPoliciesReport]
@PolicyID varchar (30)
AS
BEGIN
SELECT Top(1) [Status] FROM PolicyAudit
WHERE rtrim(ltrim(PolicyID)) = @PolicyID
And PolicyAudit.IsCurrent = 1
Order By [ID] desc
END
I want to incorporte this query into a single query and give the desired output .
March 29, 2016 at 7:44 am
Try
SELECT Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
CASE WHEN Coverstartdate IS NULL THEN ''
ELSE CONVERT(VARCHAR(8), PolicyAudit.Coverstartdate, 112)
END AS Coverstartdate,
CASE WHEN ErrorCode IS NULL THEN ''
ELSE ErrorCode
END AS ErrorCode,
CASE WHEN ErrorMessage IS NULL THEN ''
ELSE ErrorMessage
END AS ErrorMessage,
CASE WHEN EmployerName IS NULL THEN ''
ELSE EmployerName
END AS EmployerName,
CASE WHEN Branch IS NULL THEN ''
ELSE Branch
END AS Branch,
CASE WHEN Workgroup IS NULL THEN ''
ELSE Workgroup
END AS Workgroup,
CASE WHEN LOB IS NULL THEN ''
ELSE LOB
END AS LOB,
CASE WHEN ProductCode IS NULL THEN ''
ELSE ProductCode
END AS ProductCode,
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateRejected',
CONVERT(VARCHAR(8), DATEDIFF(DAY, PolicyAudit.LastModifiedDate, GETDATE())) AS 'DaysSinceRejected',
(CASE WHEN (DATEDIFF(DAY, PolicyAudit.Coverstartdate, GETDATE())) IS NULL THEN ''
ELSE CONVERT(VARCHAR(8), DATEDIFF(DAY, PolicyAudit.Coverstartdate, GETDATE()))
END) AS 'DaysSinceCoverStartDate'
FROM Systems
LEFT JOIN PolicyAudit ON Systems.ID = PolicyAudit.SourceSystemID
LEFT JOIN RejectReason ON RejectReason.PolicyAuditID = PolicyAudit.ID
OUTER APPLY (SELECT TOP (1)
Status
FROM PolicyAudit pa
WHERE pa.PolicyID = PolicyAudit.PolicyID
AND PolicyAudit.IsCurrent = 1
ORDER BY ID DESC
) QueryPolicyAudit
WHERE PolicyAudit.Status = 2
AND PolicyAudit.IsCurrent = 1
AND Systems.FileTypeIndicator IN ('C', 'V')
AND QueryPolicyAudit.Status = 0;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2016 at 9:00 am
thanks a lot . Instead of outer apply if i try for Left outer or right outer i am getting an error in where .The reason i am saying this is i need to incorporate in bo universe and i found out right outer or left outer . If you could provide me the solution then it would be of great help .
Thanks for your quick response and looking forward your reply .
March 29, 2016 at 10:22 am
With LEFT and RIGHT the two sides represent independent sets, but with APPLY the RIGHT side can be dependent on the LEFT side. Since the right side IS dependent on the left side in this case, you can only use APPLY.
If you put it in your procedure, it shouldn't matter that you are using it in a BO universe.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 30, 2016 at 5:50 am
Since ansi 92 of bo standards does not support outer apply i tried putting an left outer commented in one of the posts and i tried the following way
SELECT Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
CASE WHEN Coverstartdate IS NULL THEN ''
ELSE CONVERT(VARCHAR(8), PolicyAudit.Coverstartdate, 112)
END AS Coverstartdate,
CASE WHEN ErrorCode IS NULL THEN ''
ELSE ErrorCode
END AS ErrorCode,
CASE WHEN ErrorMessage IS NULL THEN ''
ELSE ErrorMessage
END AS ErrorMessage,
CASE WHEN EmployerName IS NULL THEN ''
ELSE EmployerName
END AS EmployerName,
CASE WHEN Branch IS NULL THEN ''
ELSE Branch
END AS Branch,
CASE WHEN Workgroup IS NULL THEN ''
ELSE Workgroup
END AS Workgroup,
CASE WHEN LOB IS NULL THEN ''
ELSE LOB
END AS LOB,
CASE WHEN ProductCode IS NULL THEN ''
ELSE ProductCode
END AS ProductCode,
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateRejected',
CONVERT(VARCHAR(8), DATEDIFF(DAY, PolicyAudit.LastModifiedDate, GETDATE())) AS 'DaysSinceRejected',
(CASE WHEN (DATEDIFF(DAY, PolicyAudit.Coverstartdate, GETDATE())) IS NULL THEN ''
ELSE CONVERT(VARCHAR(8), DATEDIFF(DAY, PolicyAudit.Coverstartdate, GETDATE()))
END) AS 'DaysSinceCoverStartDate'
FROM Systems
LEFT JOIN PolicyAudit ON Systems.ID = PolicyAudit.SourceSystemID
LEFT JOIN RejectReason ON RejectReason.PolicyAuditID = PolicyAudit.ID
LEFT JOIN (SELECT TOP (1)
Status,
pa.PolicyID ,
pa.id
FROM PolicyAudit pa
WHERE pa.PolicyID = PolicyAudit.PolicyID
AND PolicyAudit.IsCurrent = 1
ORDER BY ID DESC
) QueryPolicyAudit on ( QueryPolicyAudit.id =PolicyAudit.id and QueryPolicyAudit.PolicyID=PolicyAudit.PolicyID)
WHERE PolicyAudit.Status = 2
AND PolicyAudit.IsCurrent = 1
AND Systems.FileTypeIndicator IN ('C', 'V')
AND QueryPolicyAudit.Status = 0;
I am getting the following error message .
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "PolicyAudit.PolicyID" could not be bound.
Could you please guide on this why it is throwing this error when we have PolicyAudit.PolicyID in the outer query and how to go ahead with the other joins as it support right ,left outer and theta join (between)
March 30, 2016 at 6:03 am
LEFT JOIN RejectReason ON RejectReason.PolicyAuditID = PolicyAudit.ID
LEFT JOIN (SELECT TOP (1)
Status,
pa.PolicyID ,
pa.id
FROM PolicyAudit pa
WHERE pa.PolicyID = PolicyAudit.PolicyID
AND PolicyAudit.IsCurrent = 1
ORDER BY ID DESC
) QueryPolicyAudit on ( QueryPolicyAudit.id =PolicyAudit.id and QueryPolicyAudit.PolicyID=PolicyAudit.PolicyID)
WHERE PolicyAudit.Status = 2
AND PolicyAudit.IsCurrent = 1
AND Systems.FileTypeIndicator IN ('C', 'V')
AND QueryPolicyAudit.Status = 0;
I am getting the following error message .
Msg 4104, Level 16, State 1, Line 41
The multi-part identifier "PolicyAudit.PolicyID" could not be bound.
Could you please guide on this why it is throwing this error when we have PolicyAudit.PolicyID in the outer query and how to go ahead with the other joins as it support right ,left outer and theta join (between)
You've changed the outer query to a LEFT JOIN above.
Therefore you don't need the
SELECT TOP (1)
Status,
pa.PolicyID ,
pa.id
FROM PolicyAudit pa
WHERE pa.PolicyID = PolicyAudit.PolicyID
AND PolicyAudit.IsCurrent = 1
ORDER BY ID DESC as that's for the APPLY syntax and is what is creating the error message for you.
Plus you already have that covered by your ON statement
QueryPolicyAudit on ( QueryPolicyAudit.id =PolicyAudit.id and QueryPolicyAudit.PolicyID=PolicyAudit.PolicyID)
March 30, 2016 at 6:25 am
Thanks for your response . i tried the way what you said but i dont find any records gettig populated .While earlier things are fine and record counts are perfect .
Thanks once again for all your help . As outer apply is not suppported i am facing this issue else the earlier query could have solve my problem .
March 30, 2016 at 6:39 am
You can't just change an APPLY to a JOIN and expect the same results, you won't get them.
An APPLY runs the query once for each row of the outer query, which is what you want. A join does not do that, therefore it won't give you the results you want.
What's wrong with APPLY? It's fully supported in SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2016 at 7:28 am
Thanks for your response .As said it is fully supported and i am getting the result fine . The problem is i need to incorprate the query in bo universe which it does not support . Hence i thought of doing any left or right outer and getting the desired result . To obtain this i am looking for an alternative thats all .
March 30, 2016 at 7:38 am
You're not going to get the desired output with a join, left or right.
I don't know what bo universe is, If it accesses SQL server, can you put the code into a view or stored proc and query/call that from your app?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2016 at 8:08 am
GilaMonster (3/30/2016)
You're not going to get the desired output with a join, left or right.I don't know what bo universe is, If it accesses SQL server, can you put the code into a view or stored proc and query/call that from your app?
BO stands for Business Objects. I only know enough about BO to recognize that that's what he was talking about.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply