want a way..

Question

Post reply

want a way..

Vishal Singh

SSCertifiable

Points: 5830
More actions
May 27, 2010 at 6:57 am

#235560

Hi all,
By Writing the below queries I am getting the result set like this:
129bdf6b1c7d4fe9b05b1dcd51a19190K1C1A4X9H9SERVICETestIssueFilter
129bdf6b1c7d4fe9b05b1dcd51a19190K7C4K2X9K7PETERManual Works Issue01
ac78f6d80fc040b99ab266193a71ad25X6A0H0C6X0PETERMyNewIssue
SELECT cdi.xidxrecid,CFA.ORGID,
CI.ISSUEID AS [Issue],
CI.PATH AS DISPLAYVALUE
FROM CASE_DOC_ISSUE CDI WITH (NOLOCK)
INNER JOIN CASE_RESULT_HITS CRH WITH (NOLOCK)
ON CDI.XIDXRECID = CRH.XIDXRECID
INNER JOIN CASE_ISSUES CI WITH (NOLOCK)
ON CI.ISSUEID=CDI.ISSUEID
INNER JOIN CASE_FCI_ACL CFA
ON CI.ISSUEID = CFA.ItemId
WHERE CRH.SESSIONID = '12919' AND
CRH.IsDisplayed = 1 AND
CFA.ItemType = 'I' AND
CFA.ORGID IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')
UNION ALL
SELECTcdi.xidxrecid,cdi.issueid AS [Issue], CFA.ORGID,
ci.path AS DISPLAYVALUE
FROM CASE_DOC_ISSUE CDI WITH (NOLOCK)
INNER JOIN CASE_RESULT_HITS CRH WITH (NOLOCK)
ON CDI.XIDXRECID = CRH.XIDXRECID
INNER JOIN CASE_ISSUES CI WITH (NOLOCK)
ON CI.ISSUEID=CDI.ISSUEID
INNER JOIN CASE_FCI_ACL CFA
ON CI.ISSUEID = CFA.ItemId
WHERECRH.SESSIONID = '12919' AND
CRH.IsDisplayed = 1 AND
CFA.ItemType = 'I' AND
CFA.ORGID NOT IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')
GROUP BY CDI.XIDXRECID, CDI.IssueId, CI.Path,CFA.ORGID
The expected result set is this:
129bdf6b1c7d4fe9b05b1dcd51a19190K7C4K2X9K7PETERManual Works Issue01
ac78f6d80fc040b99ab266193a71ad25X6A0H0C6X0PETERMyNewIssue
Any help will be much more appreciated.
-Forum Etiquette: How to post Performance Problems[/url]
-Forum Etiquette: How to post data/code to get the best help [/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

V in your first query of the UNION ALL you have this as part of the WHERE arguments:

CFA.ORGID IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')

but in the second part of the union all, you have the opposite condition:

CFA.ORGID NOT IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')

could it be just a matter of adding the NOT condition to the first half of the union?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 2

Lowell (5/27/2010)
V in your first query of the UNION ALL you have this as part of the WHERE arguments:
CFA.ORGID IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')
but in the second part of the union all, you have the opposite condition:
CFA.ORGID NOT IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')
could it be just a matter of adding the NOT condition to the first half of the union?

Nope, still I am getting the result set like what I have with UNION ALL.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 3

sorry I mean I am not getting the desired output.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 4

Try using RowNumber(). Something Like;

Select * from (

Select *, RowNumber() over(order by [Issue] partition by xidxrecid) Rno

From (

SELECT cdi.xidxrecid,CFA.ORGID,

CI.ISSUEID AS [Issue],

CI.PATH AS DISPLAYVALUE

FROM CASE_DOC_ISSUE CDI WITH (NOLOCK)

INNER JOIN CASE_RESULT_HITS CRH WITH (NOLOCK)

ON CDI.XIDXRECID = CRH.XIDXRECID

INNER JOIN CASE_ISSUES CI WITH (NOLOCK)

ON CI.ISSUEID=CDI.ISSUEID

INNER JOIN CASE_FCI_ACL CFA

ON CI.ISSUEID = CFA.ItemId

WHERE CRH.SESSIONID = '12919' AND

CRH.IsDisplayed = 1 AND

CFA.ItemType = 'I' AND

CFA.ORGID IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')

UNION ALL

SELECT cdi.xidxrecid,cdi.issueid AS [Issue], CFA.ORGID,

ci.path AS DISPLAYVALUE

FROM CASE_DOC_ISSUE CDI WITH (NOLOCK)

INNER JOIN CASE_RESULT_HITS CRH WITH (NOLOCK)

ON CDI.XIDXRECID = CRH.XIDXRECID

INNER JOIN CASE_ISSUES CI WITH (NOLOCK)

ON CI.ISSUEID=CDI.ISSUEID

INNER JOIN CASE_FCI_ACL CFA

ON CI.ISSUEID = CFA.ItemId

WHERE CRH.SESSIONID = '12919' AND

CRH.IsDisplayed = 1 AND

CFA.ItemType = 'I' AND

CFA.ORGID NOT IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')

GROUP BY CDI.XIDXRECID, CDI.IssueId, CI.Path,CFA.ORGID

) Innerdata

) Data where Rno = 1

I dont have test data, so I postyed the query without check.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 5

Below query is giving me desired output, but can we merge both these query to have just one single query:

SELECT cdi.xidxrecid,CFA.ORGID,

CDI.ISSUEID AS [Issue],

CI.PATH AS DISPLAYVALUE

FROM CASE_DOC_ISSUE CDI WITH (NOLOCK)

INNER JOIN CASE_RESULT_HITS CRH WITH (NOLOCK)

ON CDI.XIDXRECID = CRH.XIDXRECID

INNER JOIN CASE_ISSUES CI WITH (NOLOCK)

ON CI.ISSUEID=CDI.ISSUEID

INNER JOIN CASE_FCI_ACL CFA

ON CI.ISSUEID = CFA.ItemId

WHERE CRH.SESSIONID = '12919' AND

CRH.IsDisplayed = 1 AND

CFA.ItemType = 'I' AND

CFA.ORGID IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')

UNION

SELECT cdi.xidxrecid,CFA.ORGID,

CDI.ISSUEID AS [Issue],

CI.PATH AS DISPLAYVALUE

FROM CASE_DOC_ISSUE CDI WITH (NOLOCK)

INNER JOIN CASE_RESULT_HITS CRH WITH (NOLOCK)

ON CDI.XIDXRECID = CRH.XIDXRECID

INNER JOIN CASE_ISSUES CI WITH (NOLOCK)

ON CI.ISSUEID=CDI.ISSUEID

INNER JOIN CASE_FCI_ACL CFA

ON CI.ISSUEID = CFA.ItemId

WHERE CRH.SESSIONID = '12919' AND

CRH.IsDisplayed = 1 AND

CFA.ItemType = 'I' AND

CFA.ORGID NOT IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A')

and cdi.xidxrecid not in

( SELECT cdi.xidxrecid

FROM CASE_DOC_ISSUE CDI WITH (NOLOCK)

INNER JOIN CASE_RESULT_HITS CRH WITH (NOLOCK)

ON CDI.XIDXRECID = CRH.XIDXRECID

INNER JOIN CASE_ISSUES CI WITH (NOLOCK)

ON CI.ISSUEID=CDI.ISSUEID

INNER JOIN CASE_FCI_ACL CFA

ON CI.ISSUEID = CFA.ItemId

WHERE CRH.SESSIONID = '12919' AND

CRH.IsDisplayed = 1 AND

CFA.ItemType = 'I' AND

CFA.ORGID IN ('*','SERVICE','4C0C6A2U7P','4X9A8F2U5A') )

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]