Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I would really like something to replace all of these REPLACE Statements.

SELECT DISTINCT
CAH.CaseNbr
    , REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            (SELECT table1.Degree 
            FROM SDUJS.dbo.fnWorstDegreeCharge(CB.CaseID) AS table1), 'F1', 'F')
            ,'F2','F'),'F3','F'),'F4','F'),'F5','F'),'F6','F'),'FA','F'),'FB','F')
            ,'FC','F') AS Degree
    , dbo.fncasecounty(CB.CaseID) AS County
FROM
    Justice.dbo.CaseBase AS CB
    INNER JOIN Justice.dbo.xCaseBaseChrg AS xCBC WITH (NOLOCK) ON xCBC.CaseID = CB.CaseID
    INNER JOIN Justice.dbo.Chrg AS Chrg WITH (NOLOCK) ON Chrg.ChargeID = xCBC.ChargeID
    INNER JOIN Justice.dbo.CaseAssignHist AS CAH WITH (NOLOCK) ON CAH.CaseID = CB.CaseID
    INNER JOIN Justice.dbo.ClkCaseHdr AS CCH WITH (NOLOCK) ON CCH.CaseID = CB.CaseID
WHERE 
    CCH.DtFile BETWEEN '07/01/2010' AND '06/30/2011'
    AND 
    CCH.CaseUTypeID IN (858,4330,865,4329,6362,5112)
ORDER BY CaseNbr

All possible entries for that column

M1, M2, MO, PO, CHINS, F1, F2, F3, F4, F5, F6, FA, FB, FC, 00

I want all the Felonies to be represented by F


Code after the review

I decided not to do this as a follow up question because there isn't much that I think can be done to this query now to make it faster, except for get rid of the DISTINCT which I don't feel comfortable doing right now.

SELECT DISTINCT
    CAH.CaseNbr
    , (SELECT table1.Degree FROM SDUJS.dbo.fnWorstDegreeCharge(CB.CaseID) AS table1) AS Degree
    , dbo.fncasecounty(CB.CaseID) AS County
FROM
    Justice.dbo.CaseBase AS CB
    INNER JOIN Justice.dbo.CaseAssignHist AS CAH WITH (NOLOCK) ON CAH.CaseID = CB.CaseID
    INNER JOIN Justice.dbo.ClkCaseHdr AS CCH WITH (NOLOCK) ON CCH.CaseID = CB.CaseID
WHERE 
    CCH.DtFile BETWEEN '07/01/2010' AND '06/30/2011'
    AND 
    CCH.CaseUTypeID IN (858,4330,865,4329,6362,5112)

Missing REPLACE statement

I moved the "REPLACE" to the function that I am calling, but I used @rolfl's wonderful Case Statement idea to get rid of all the REPLACE statements inside that function.

share|improve this question
    
Are there, or can there be, other values starting with 'F' that would be broken if you selected the first leftmost character instead? It might be useful to include a few possible values of that field. –  Mat's Mug Jan 22 at 19:38
    
Warning: with nolock is dangerous: stackoverflow.com/a/5469238 –  Chris Mathews Jan 24 at 17:43
    
@ChrisMathews, this is something that the Vendor told us to do, in order to speed things up. I am pulling data from a report server. if I put a lock on all these tables then I can't run concurrent reports, this can cause issues. that is the purpose of using NOLOCK but thank you for that information. –  Malachi Jan 24 at 17:51
    
@ChrisMathews, in other words there shouldn't be too much going on in terms of things being changed here, so the dangers are less than what you are thinking because of the way that the Databases are set up. –  Malachi Jan 24 at 17:52

1 Answer 1

up vote 6 down vote accepted

This strikes me as best being done by using a temp table, or with clause.

Also, the DISTINCT part worries me.... why is that needed? Are your joins not good?

Still, I think you may have some more contentment with:

SELECT DISTINCT
    CAH.CaseNbr,
    (SELECT CASE WHEN Degree in ('F1','F2','F3','F4','F5','F6','FA','FB','FC')
                 THEN 'F'
                 ELSE Degree
            END
     FROM SDUJS.dbo.fnWorstDegreeCharge(CB.CaseID)) AS Degree,
  dbo.fncasecounty(CB.CaseID) AS County
....

You should still consider a better mechanism for joining. The procedure route seems 'klunky'

share|improve this answer
    
it's a really bad Data Retrieval (PITA Report). I am labeling a case with one Charge's degree, a case can have many charges and they can all be different degrees. the data is good but what they want isn't always good for the data... does that even make sense? –  Malachi Jan 22 at 22:17
    
the database tables are connected all over the place so I could end up with duplicated data. I almost always throw it in there on this Database. I probably don't need it on this query because it isn't bringing back more than a couple of columns –  Malachi Jan 22 at 22:33
    
I like the CASE statement. I will see if I need the `DISTINCT or not while at work today. –  Malachi Jan 23 at 13:47
    
I will wait until the Question is Eligible for a bounty and then accept your answer. I need the DISTINCT I am either not as good of a SQL Dev that I need to be, or the Database is really screwy....or both. I can't believe that I didn't think of a Case Statement...Sigh. –  Malachi Jan 23 at 22:47
2  
That's why CodeReview is a Good Thing™ .... pulls on different people's eyes, experience, and opinions. –  rolfl Jan 23 at 22:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.