Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Unanswered: 'OR' broke my query

    The following query, minus the 'OR', was working. When I added the 'OR' now I get a time out before the query returns results. I don't get any errors. I know I could use some help optimizing this thing as well. Any ideas?

    SELECT C.CAS_ID AS [CASE ID], H.HIS_DT_CREATE AS TIMESTAMP, U.USR_FNAME + ' ' + U.USR_LNAME AS Responsible
    FROM dbo.S_CASE AS C INNER JOIN
    dbo.B_HISTORY AS H ON C.ID = H.HIS_ROO_PK INNER JOIN
    dbo.D_USER AS U ON C.CAS_USR_PK_RESP = U.ID
    WHERE (C.CAS_STATUS < '3') AND (H.HIS_TYPE = '1111') AND (H.HIS_DT_CREATE IN
    (SELECT MAX(HIS_DT_CREATE) AS MAXDATE
    FROM dbo.B_HISTORY AS HIS1
    GROUP BY HIS_ROO_PK)) OR
    (H.HIS_TYPE = '1136')
    ORDER BY [CASE ID]


    I was getting the follwing without the " OR
    (H.HIS_TYPE = '1136')"

    11955 2011-02-11 09:12:04.393 Andrew Sims
    15995 2011-02-07 10:21:09.983 Eric Reed
    16037 2011-02-11 07:58:15.497 Brian Morishita
    16978 2011-01-31 09:52:20.370 Tagg McDonald
    17500 2011-02-10 09:37:45.960 Christian Armstrong

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As written, the query looks like it will return anything with a H.HIS_TYPE of 1136, and the 5 records you listed. Is this what you wanted it to do?

  3. #3
    Join Date
    Feb 2011
    Posts
    3

    What I am looking for:

    So I am looking for all records from B_HISTORY of HIS_TYPE = 1111 or 1136

    and the Maximum date for each group of case id's

    and that the case status is < 3

    When I do just one his_type I get results relatively fast that meet my criteria.

    When I add the second OR the query times out.

    If I reverse the order so that 1136 is first it disregards my MAX Date.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As I suspected, your parenthesis are screwed up. You are using them when you don't need to, and you aren't using them where you do need to.

    Code:
    SELECT	C.CAS_ID AS [CASE ID],
    	H.HIS_DT_CREATE AS TIMESTAMP,
    	U.USR_FNAME + ' ' + U.USR_LNAME AS Responsible
    FROM	dbo.S_CASE AS C
    	INNER JOIN dbo.B_HISTORY AS H ON C.ID = H.HIS_ROO_PK
    	INNER JOIN dbo.D_USER AS U ON C.CAS_USR_PK_RESP = U.ID
    WHERE	(C.CAS_STATUS < '3'
    	AND H.HIS_TYPE = '1111'
    	AND H.HIS_DT_CREATE IN
    		(SELECT	MAX(HIS_DT_CREATE) AS MAXDATE
    		FROM dbo.B_HISTORY AS HIS1
    		GROUP BY HIS_ROO_PK))
    	OR H.HIS_TYPE = '1136'
    ORDER BY [CASE ID]
    Parenthesis are superfluous for simple AND criteria, and using them in such cases only obfuscates their correct use when it is vital that an OR criteria be applied in the correct order.
    Bottom line: format your code with white spcae and indencts, and use parenthesis only when necessary.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think this might be what he wants:
    Code:
    SELECT	C.CAS_ID AS [CASE ID],
    	H.HIS_DT_CREATE AS TIMESTAMP,
    	U.USR_FNAME + ' ' + U.USR_LNAME AS Responsible
    FROM	dbo.S_CASE AS C
    	INNER JOIN dbo.B_HISTORY AS H ON C.ID = H.HIS_ROO_PK
    	INNER JOIN dbo.D_USER AS U ON C.CAS_USR_PK_RESP = U.ID
    WHERE	C.CAS_STATUS < '3'
    	AND (H.HIS_TYPE = '1111' OR H.HIS_TYPE = '1136')
    	AND H.HIS_DT_CREATE IN
    		(SELECT	MAX(HIS_DT_CREATE) AS MAXDATE
    		FROM dbo.B_HISTORY AS HIS1
    		GROUP BY HIS_ROO_PK)
    	
    ORDER BY [CASE ID]

  6. #6
    Join Date
    Feb 2011
    Posts
    3

    Thank you

    That was it Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •