want a way..

  • 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]

  • 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!

  • 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]

  • 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]

  • 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]

  • 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]

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

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