Help with a SQL Join statement

microsoft.public.sqlserver.programming

6 Apr 2006 9:34 PM

James Juno

I have a question with a join. As a new developer, I see that there may be
issues with the join in the query. Can someone please tell me what you
think? Thank you.

SELECT DISTINCT CaseNo, Amount, DTaxID, DZip, DLastName,

DFirstName, DMidName, DSuffix, CoDLastName, CoDFirstName, CoDMidName,

CoDSuffix, CoDTaxID, DStAddress, DCity, DState, PLastName, PFirstName,
PMidName,

PstAddress, PCity, PState, PZip, 'FilingType'=(Case When FilingType='EV'
Then 'CJ' Else FilingType End),

CourtCode, efxCourtCode, ResearcherNo, JudgmentDate, SatisfiedDate,
c.Efx_prt, c.EFX_action_code,

c.Efx_status_code, complaintDate, dismissalDate, dispositionType, dtype,
codType, RRD_sequence_no, '3'

FROM

tblcases, RRD_Client_CourtCodes, EFXRRD_RecordType c

   WHERE PostedDate <= '20060405' and PostedDate >= '20060401'

and c.RRD_rt=rt

AND dispositionType in ('FE', 'FO', 'FW', 'FV', 'SE', 'SV', 'ST', 'JO',
'JE', 'JT', 'JV')

and ( DState IN ( SELECT State FROM US_STATES ) )

and (Amount >= '00000050' or Amount >= 50) and (DType = 'I' or
isnull(coDType,'') = '' or CoDType = 'I')

and (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405' and
satisfieddate > judgmentdate))

and ((filingType in ('FT', 'SL', 'CT') and (isnull(judgmentdate, '') <> ''
or isnull(complaintdate,'') <> '') )

          or isnull(judgmentDate,'') <> '')

and ((isnull(dispositionType, '') = '')

      or (dispositionType in ('PF', 'PL', 'CL', 'FD', 'JD', 'ED', 'EX',
'JE','JO','JV','JT','FE','FO','FV','FW','SE','SV','ST','EH','EI','ET') and
(isnull(dismissalDate,'') <> '' or isnull(satisfieddate,'') <> ''))

      or (dispositionType in ('JS', 'ES') and isnull(satisfieddate,'') <>
''))

and ISNULL(DStAddress,'') <>'' AND ISNULL(DCity,'') <>'' AND
ISNULL(DState,'') <> ''

and LEN( LTRIM(RTRIM(DStAddress)) ) > 1 AND LEN( LTRIM(RTRIM(DCity)) ) > 1

           AND LEN( LTRIM(RTRIM(DState)) ) > 1

and CourtCode = CC AND CourtType = CT

and RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
FilingType End))

and efxCourtCode IS NOT NULL

and ( dlastname IS NOT NULL and LEN(dlastname) > 1 )

and ( CaseNo IS NOT NULL and LEN(CaseNo) > 0 )

and LTRIM(RTRIM(REPLACE(REPLACE(PLastName, ' ', ''), '.', ''))) NOT IN
(SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
REJECT_ALL='Y')

and (filingtype <> 'SL' OR (filingType = 'SL' and substring(courtcode,1,1)
<> 'N') OR (filingType = 'SL' AND substring(courtcode,1,1) = 'N' AND
PLastName NOT IN (SELECT [plaintiff name] FROM NJ_SL_PLAINTIFF_DELETE )))

ORDER BY RRD_sequence_no, courtcode

6 Apr 2006 9:40 PM

Keith Kratochvil

Holy cow. There is a lot going on there.

Can you tell us where the JOINs actually happen?

You are joining three tables, so you need at least two JOIN statements.

Perhaps rewriting the query so that it is more ANSI compliant would help
make the JOIN statement more obvious.

--
Keith Kratochvil

Show quote

"James Juno" <JamesJ***@discussions.microsoft.com> wrote in message
news:D07C7377-2B29-4017-BDC1-A6E73831FB8D@microsoft.com...
>I have a question with a join. As a new developer, I see that there may be
> issues with the join in the query. Can someone please tell me what you
> think? Thank you.
>
>
>
> SELECT DISTINCT CaseNo, Amount, DTaxID, DZip, DLastName,
>
> DFirstName, DMidName, DSuffix, CoDLastName, CoDFirstName, CoDMidName,
>
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState, PLastName, PFirstName,
> PMidName,
>
> PstAddress, PCity, PState, PZip, 'FilingType'=(Case When FilingType='EV'
> Then 'CJ' Else FilingType End),
>
> CourtCode, efxCourtCode, ResearcherNo, JudgmentDate, SatisfiedDate,
> c.Efx_prt, c.EFX_action_code,
>
> c.Efx_status_code, complaintDate, dismissalDate, dispositionType, dtype,
> codType, RRD_sequence_no, '3'
>
> FROM
>
> tblcases, RRD_Client_CourtCodes, EFXRRD_RecordType c
>
>   WHERE PostedDate <= '20060405' and PostedDate >= '20060401'
>
> and c.RRD_rt=rt
>
> AND dispositionType in ('FE', 'FO', 'FW', 'FV', 'SE', 'SV', 'ST', 'JO',
> 'JE', 'JT', 'JV')
>
>
>
> and ( DState IN ( SELECT State FROM US_STATES ) )
>
> and (Amount >= '00000050' or Amount >= 50) and (DType = 'I' or
> isnull(coDType,'') = '' or CoDType = 'I')
>
> and (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405' and
> satisfieddate > judgmentdate))
>
> and ((filingType in ('FT', 'SL', 'CT') and (isnull(judgmentdate, '') <> ''
> or isnull(complaintdate,'') <> '') )
>
>          or isnull(judgmentDate,'') <> '')
>
> and ((isnull(dispositionType, '') = '')
>
>      or (dispositionType in ('PF', 'PL', 'CL', 'FD', 'JD', 'ED', 'EX',
> 'JE','JO','JV','JT','FE','FO','FV','FW','SE','SV','ST','EH','EI','ET') and
> (isnull(dismissalDate,'') <> '' or isnull(satisfieddate,'') <> ''))
>
>      or (dispositionType in ('JS', 'ES') and isnull(satisfieddate,'') <>
> ''))
>
> and ISNULL(DStAddress,'') <>'' AND ISNULL(DCity,'') <>'' AND
> ISNULL(DState,'') <> ''
>
> and LEN( LTRIM(RTRIM(DStAddress)) ) > 1 AND LEN( LTRIM(RTRIM(DCity)) ) >
> 1
>
>           AND LEN( LTRIM(RTRIM(DState)) ) > 1
>
> and CourtCode = CC AND CourtType = CT
>
> and RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
> DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
> FilingType End))
>
> and efxCourtCode IS NOT NULL
>
> and ( dlastname IS NOT NULL and LEN(dlastname) > 1 )
>
> and ( CaseNo IS NOT NULL and LEN(CaseNo) > 0 )
>
> and LTRIM(RTRIM(REPLACE(REPLACE(PLastName, ' ', ''), '.', ''))) NOT IN
> (SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
> REJECT_ALL='Y')
>
> and (filingtype <> 'SL' OR (filingType = 'SL' and substring(courtcode,1,1)
> <> 'N') OR (filingType = 'SL' AND substring(courtcode,1,1) = 'N' AND
> PLastName NOT IN (SELECT [plaintiff name] FROM NJ_SL_PLAINTIFF_DELETE )))
>
> ORDER BY RRD_sequence_no, courtcode
>
>

6 Apr 2006 9:53 PM

James Juno

Thanks Keith. I re-wrote it to look like this:

Here is my first rewrite â€“
SELECT DISTINCT t.CaseNo, t.Amount, t.DTaxID, t.DZip, t.DLastName,
   t.DFirstName, t.DMidName, t.DSuffix, t.CoDLastName, t.CoDFirstName,
t.CoDMidName,
   t.CoDSuffix, t.CoDTaxID, t.DStAddress, t.DCity, t.DState,
   t.PLastName, t.PFirstName, t.PMidName,
   t.PstAddress, t.PCity, t.PState, t.PZip,
   'FilingType'=(Case When FilingType='EV' Then 'CJ' Else FilingType End),
            t.CourtCode, c.efxCourtCode, t.ResearcherNo, t.JudgmentDate,
t.SatisfiedDate,
        r.Efx_prt, r.EFX_action_code,
        r.Efx_status_code, t.complaintDate, t.dismissalDate,
t.dispositionType, t.dtype,
        t.codType, t.RRD_sequence_no,'3'
   FROM TBLCASES t
     inner join RRD_Client_CourtCodes c
      on (CourtCode = c.CC AND CourtType = c.CT
            and PostedDate >= '20060301'   and PostedDate < '20060331'
              AND ISNULL(t.DStAddress,'') <>''
              AND ISNULL(t.DCity,'') <>''
              AND ISNULL(t.DState,'') <> ''
              AND LEN( LTRIM(RTRIM(t.DStAddress)) ) > 1
              AND LEN( LTRIM(RTRIM(t.DCity)) ) > 1
              AND LEN( LTRIM(RTRIM(t.DState)) ) > 1
              AND ( t.dlastname is not null and len(t.dlastname) > 1 )
              AND ( t.CaseNo is not null and len(t.CaseNo) > 0 )
              AND c.efxCourtCode is not null
     )-- on

    inner join EFXRRD_RecordType R
      on (r.RRD_rt=c.rt

          ) -- on

   WHERE (Amount >= '00000050' or Amount >= 50)
   AND (DType = 'I' or isnull(coDType,'') = '' or CoDType = 'I')
      AND (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405'
and satisfieddate > judgmentdate))
     AND ((filingType in ('FT', 'SL', 'CT') AND (isnull(judgmentdate, '') <>
'' or isnull(complaintdate,'') <> '') ) or isnull(judgmentDate,'') <> '')
   AND ((isnull(dispositionType, '') = '') or (dispositionType in ('PF',
'PL', 'CL', 'FD', 'JD', 'ED', 'EX') and (isnull(dismissalDate,'') <> '' or
isnull(satisfieddate,'') <> '')) or (dispositionType in ('JS', 'ES') and
isnull(satisfieddate,'') <> ''))

   AND RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
FilingType End))
   AND ( DState IN ( SELECT State FROM US_STATES ) )
   AND ltrim(rtrim(replace(replace(PLastName, ' ', ''), '.', ''))) not in
(SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
REJECT_ALL='Y')

   AND (filingtype <> 'SL' or (filingType = 'SL' and
substring(courtcode,1,1) <> 'N') or (filingType = 'SL' and
substring(courtcode,1,1) = 'N' and PLastName not in (SELECT [plaintiff name]
FROM NJ_SL_PLAINTIFF_DELETE )))
   order by RRD_sequence_no, courtcode

Thanks for any help I can get. James.

Show quote

"Keith Kratochvil" wrote:

> Holy cow. There is a lot going on there.
>
> Can you tell us where the JOINs actually happen?
>
> You are joining three tables, so you need at least two JOIN statements.
>
> Perhaps rewriting the query so that it is more ANSI compliant would help
> make the JOIN statement more obvious.
>
> --
> Keith Kratochvil
>
>
> "James Juno" <JamesJ***@discussions.microsoft.com> wrote in message
> news:D07C7377-2B29-4017-BDC1-A6E73831FB8D@microsoft.com...
> >I have a question with a join. As a new developer, I see that there may be
> > issues with the join in the query. Can someone please tell me what you
> > think? Thank you.
> >
> >
> >
> > SELECT DISTINCT CaseNo, Amount, DTaxID, DZip, DLastName,
> >
> > DFirstName, DMidName, DSuffix, CoDLastName, CoDFirstName, CoDMidName,
> >
> > CoDSuffix, CoDTaxID, DStAddress, DCity, DState, PLastName, PFirstName,
> > PMidName,
> >
> > PstAddress, PCity, PState, PZip, 'FilingType'=(Case When FilingType='EV'
> > Then 'CJ' Else FilingType End),
> >
> > CourtCode, efxCourtCode, ResearcherNo, JudgmentDate, SatisfiedDate,
> > c.Efx_prt, c.EFX_action_code,
> >
> > c.Efx_status_code, complaintDate, dismissalDate, dispositionType, dtype,
> > codType, RRD_sequence_no, '3'
> >
> > FROM
> >
> > tblcases, RRD_Client_CourtCodes, EFXRRD_RecordType c
> >
> >   WHERE PostedDate <= '20060405' and PostedDate >= '20060401'
> >
> > and c.RRD_rt=rt
> >
> > AND dispositionType in ('FE', 'FO', 'FW', 'FV', 'SE', 'SV', 'ST', 'JO',
> > 'JE', 'JT', 'JV')
> >
> >
> >
> > and ( DState IN ( SELECT State FROM US_STATES ) )
> >
> > and (Amount >= '00000050' or Amount >= 50) and (DType = 'I' or
> > isnull(coDType,'') = '' or CoDType = 'I')
> >
> > and (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405' and
> > satisfieddate > judgmentdate))
> >
> > and ((filingType in ('FT', 'SL', 'CT') and (isnull(judgmentdate, '') <> ''
> > or isnull(complaintdate,'') <> '') )
> >
> >          or isnull(judgmentDate,'') <> '')
> >
> > and ((isnull(dispositionType, '') = '')
> >
> >      or (dispositionType in ('PF', 'PL', 'CL', 'FD', 'JD', 'ED', 'EX',
> > 'JE','JO','JV','JT','FE','FO','FV','FW','SE','SV','ST','EH','EI','ET') and
> > (isnull(dismissalDate,'') <> '' or isnull(satisfieddate,'') <> ''))
> >
> >      or (dispositionType in ('JS', 'ES') and isnull(satisfieddate,'') <>
> > ''))
> >
> > and ISNULL(DStAddress,'') <>'' AND ISNULL(DCity,'') <>'' AND
> > ISNULL(DState,'') <> ''
> >
> > and LEN( LTRIM(RTRIM(DStAddress)) ) > 1 AND LEN( LTRIM(RTRIM(DCity)) ) >
> > 1
> >
> >           AND LEN( LTRIM(RTRIM(DState)) ) > 1
> >
> > and CourtCode = CC AND CourtType = CT
> >
> > and RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
> > DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
> > FilingType End))
> >
> > and efxCourtCode IS NOT NULL
> >
> > and ( dlastname IS NOT NULL and LEN(dlastname) > 1 )
> >
> > and ( CaseNo IS NOT NULL and LEN(CaseNo) > 0 )
> >
> > and LTRIM(RTRIM(REPLACE(REPLACE(PLastName, ' ', ''), '.', ''))) NOT IN
> > (SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
> > REJECT_ALL='Y')
> >
> > and (filingtype <> 'SL' OR (filingType = 'SL' and substring(courtcode,1,1)
> > <> 'N') OR (filingType = 'SL' AND substring(courtcode,1,1) = 'N' AND
> > PLastName NOT IN (SELECT [plaintiff name] FROM NJ_SL_PLAINTIFF_DELETE )))
> >
> > ORDER BY RRD_sequence_no, courtcode
> >
> >
>
>
>

7 Apr 2006 1:01 AM

Roy Harvey

You described yourself as a new developer, so I am taking you at your
word. Some of this is technique, some philosphy (and probably a bit
of BS too!)   8-) It is not comprehensive, just some ideas.

The first thing that struck me was how much of the complexity is in
trying to deal with crappy data. ISNULLs, LTRIMs, RTRIMs, LEN()>1,
and on and one. Imagine how much simpler it all would be if the data
were CLEAN. Use the pain you are suffering from this to learn not to
ever create such a situation. Then in the future when you build
systems you will save everyone this sort of grief by writing them so
that they always have clean data and keep it that way.

Given what we have here, I see two paths to trying to get it all under
control. One is to clean up the data so you can deal with it without
all the ISNULLS and LTRIMS. If you have the choice, cleaning up is
absolutely the way to go. Along the way you can fix things like dates
that are stored as character strings (use datetime datatype).

Of course circumstances may not permit you to fix the data and keep it
clean. Which would be a terrible shame (but as I said, at a minimum
it should teach you what NOT to do in the future.) The rest of my
comments relate to what you can try if you are stuck with the garbage.

Another approach - which may not perform well at all - is to write
views over the tables that deal with all the cleaning issues. Start
with the exact same column names, and column order, but for any column
with issues resolve them to a value that will not give you heartburn.
If you need both an original and a cleaned up version of a column -
say with need PLastName in the original form as well as:
    ltrim(rtrim(replace(replace(PLastName, ' ', ''), '.', '')))
make an additional column in the view with all those changes applied.

Then reference the views in the query in place of the tables. Instead
of having a test:
    isnull(judgmentdate, '') <> ''
which tries to treat blanks and NULL the same, you could set up the
view with:
    judgmentdate = isnull(judgmentdate, '')
and then your test simply becomes:
    judgmentdate <> ' '
Or you could force it to NULL instead of to BLANK:
    judgmentdate = CASE WHEN judgmentdate <> " "
                                 THEN judgmentdate ELSE NULL END
which would make your test:
    judgmentdate IS NOT NULL

Consider the tests:
    (isnull(dismissalDate,'') <> ''   or
    isnull(satisfieddate,'') <> '')
If you knew that these columns were either NULL or non-blank this
could be shortened to:
    COALESCE(dismissalDate, satisfieddate) IS NOT NULL

Likewise, where you now have:
    ( t.CaseNo is not null and len(t.CaseNo) > 0 )
set up the view with
    CaseNo = ISNULL(CaseNo, ' ') --perhaps adding RTRIM, etc
and then the test becomes:
    ( t.CaseNo <> ' ' )

And so forth.

Another bit that worries me is the DISTINCT. Sometimes DISTINCT is
absolutely required. Too often, however, it points to queries that
are not joining properly. I can't judge the reason in this case, but
it would be worth proving to yourself that it can't be written any
other way.

Everyone has their own coding style. I found myself getting (lost (in
the (parentheses))), though I recognize that you have no choice when
mixing ANDs and ORs. I suggest formatting to make the groupings
clearer. In fact, coming up with a standard format for a SELECT, one
that is clear to you, and then following it always is a really good
idea.

I think I've rambled long enough.

Roy Harvey
Beacon Falls, CT

On Thu, 6 Apr 2006 14:53:02 -0700, James Juno
<JamesJ***@discussions.microsoft.com> wrote:

Show quote

>Thanks Keith. I re-wrote it to look like this:
>
>
>Here is my first rewrite –
>SELECT DISTINCT t.CaseNo, t.Amount, t.DTaxID, t.DZip, t.DLastName,
>   t.DFirstName, t.DMidName, t.DSuffix, t.CoDLastName, t.CoDFirstName,
>t.CoDMidName,
>   t.CoDSuffix, t.CoDTaxID, t.DStAddress, t.DCity, t.DState,
>   t.PLastName, t.PFirstName, t.PMidName,
>   t.PstAddress, t.PCity, t.PState, t.PZip,
>   'FilingType'=(Case When FilingType='EV' Then 'CJ' Else FilingType End),
>            t.CourtCode, c.efxCourtCode, t.ResearcherNo, t.JudgmentDate,
>t.SatisfiedDate,
>        r.Efx_prt, r.EFX_action_code,
>        r.Efx_status_code, t.complaintDate, t.dismissalDate,
>t.dispositionType, t.dtype,
>        t.codType, t.RRD_sequence_no,'3'
>   FROM TBLCASES t
>     inner join RRD_Client_CourtCodes c
>      on (CourtCode = c.CC AND CourtType = c.CT
>            and PostedDate >= '20060301'   and PostedDate < '20060331'
>              AND ISNULL(t.DStAddress,'') <>''
>              AND ISNULL(t.DCity,'') <>''
>              AND ISNULL(t.DState,'') <> ''
>              AND LEN( LTRIM(RTRIM(t.DStAddress)) ) > 1
>              AND LEN( LTRIM(RTRIM(t.DCity)) ) > 1
>              AND LEN( LTRIM(RTRIM(t.DState)) ) > 1
>              AND ( t.dlastname is not null and len(t.dlastname) > 1 )
>              AND ( t.CaseNo is not null and len(t.CaseNo) > 0 )
>              AND c.efxCourtCode is not null
>     )-- on
>
>    inner join EFXRRD_RecordType R
>      on (r.RRD_rt=c.rt
>
>          ) -- on
>
>
>   WHERE (Amount >= '00000050' or Amount >= 50)
>   AND (DType = 'I' or isnull(coDType,'') = '' or CoDType = 'I')
>      AND (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405'
>and satisfieddate > judgmentdate))
>     AND ((filingType in ('FT', 'SL', 'CT') AND (isnull(judgmentdate, '') <>
>'' or isnull(complaintdate,'') <> '') ) or isnull(judgmentDate,'') <> '')
>   AND ((isnull(dispositionType, '') = '') or (dispositionType in ('PF',
>'PL', 'CL', 'FD', 'JD', 'ED', 'EX') and (isnull(dismissalDate,'') <> '' or
>isnull(satisfieddate,'') <> '')) or (dispositionType in ('JS', 'ES') and
>isnull(satisfieddate,'') <> ''))
>
>
>   AND RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
>DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
>FilingType End))
>   AND ( DState IN ( SELECT State FROM US_STATES ) )
>   AND ltrim(rtrim(replace(replace(PLastName, ' ', ''), '.', ''))) not in
>(SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
>REJECT_ALL='Y')
>
>   AND (filingtype <> 'SL' or (filingType = 'SL' and
>substring(courtcode,1,1) <> 'N') or (filingType = 'SL' and
>substring(courtcode,1,1) = 'N' and PLastName not in (SELECT [plaintiff name]
>FROM NJ_SL_PLAINTIFF_DELETE )))
>   order by RRD_sequence_no, courtcode
>
>
>Thanks for any help I can get. James.
>
>
>
>"Keith Kratochvil" wrote:
>
>> Holy cow. There is a lot going on there.
>>
>> Can you tell us where the JOINs actually happen?
>>
>> You are joining three tables, so you need at least two JOIN statements.
>>
>> Perhaps rewriting the query so that it is more ANSI compliant would help
>> make the JOIN statement more obvious.
>>
>> --
>> Keith Kratochvil
>>
>>
>> "James Juno" <JamesJ***@discussions.microsoft.com> wrote in message
>> news:D07C7377-2B29-4017-BDC1-A6E73831FB8D@microsoft.com...
>> >I have a question with a join. As a new developer, I see that there may be
>> > issues with the join in the query. Can someone please tell me what you
>> > think? Thank you.
>> >
>> >
>> >
>> > SELECT DISTINCT CaseNo, Amount, DTaxID, DZip, DLastName,
>> >
>> > DFirstName, DMidName, DSuffix, CoDLastName, CoDFirstName, CoDMidName,
>> >
>> > CoDSuffix, CoDTaxID, DStAddress, DCity, DState, PLastName, PFirstName,
>> > PMidName,
>> >
>> > PstAddress, PCity, PState, PZip, 'FilingType'=(Case When FilingType='EV'
>> > Then 'CJ' Else FilingType End),
>> >
>> > CourtCode, efxCourtCode, ResearcherNo, JudgmentDate, SatisfiedDate,
>> > c.Efx_prt, c.EFX_action_code,
>> >
>> > c.Efx_status_code, complaintDate, dismissalDate, dispositionType, dtype,
>> > codType, RRD_sequence_no, '3'
>> >
>> > FROM
>> >
>> > tblcases, RRD_Client_CourtCodes, EFXRRD_RecordType c
>> >
>> >   WHERE PostedDate <= '20060405' and PostedDate >= '20060401'
>> >
>> > and c.RRD_rt=rt
>> >
>> > AND dispositionType in ('FE', 'FO', 'FW', 'FV', 'SE', 'SV', 'ST', 'JO',
>> > 'JE', 'JT', 'JV')
>> >
>> >
>> >
>> > and ( DState IN ( SELECT State FROM US_STATES ) )
>> >
>> > and (Amount >= '00000050' or Amount >= 50) and (DType = 'I' or
>> > isnull(coDType,'') = '' or CoDType = 'I')
>> >
>> > and (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405' and
>> > satisfieddate > judgmentdate))
>> >
>> > and ((filingType in ('FT', 'SL', 'CT') and (isnull(judgmentdate, '') <> ''
>> > or isnull(complaintdate,'') <> '') )
>> >
>> >          or isnull(judgmentDate,'') <> '')
>> >
>> > and ((isnull(dispositionType, '') = '')
>> >
>> >      or (dispositionType in ('PF', 'PL', 'CL', 'FD', 'JD', 'ED', 'EX',
>> > 'JE','JO','JV','JT','FE','FO','FV','FW','SE','SV','ST','EH','EI','ET') and
>> > (isnull(dismissalDate,'') <> '' or isnull(satisfieddate,'') <> ''))
>> >
>> >      or (dispositionType in ('JS', 'ES') and isnull(satisfieddate,'') <>
>> > ''))
>> >
>> > and ISNULL(DStAddress,'') <>'' AND ISNULL(DCity,'') <>'' AND
>> > ISNULL(DState,'') <> ''
>> >
>> > and LEN( LTRIM(RTRIM(DStAddress)) ) > 1 AND LEN( LTRIM(RTRIM(DCity)) ) >
>> > 1
>> >
>> >           AND LEN( LTRIM(RTRIM(DState)) ) > 1
>> >
>> > and CourtCode = CC AND CourtType = CT
>> >
>> > and RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
>> > DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
>> > FilingType End))
>> >
>> > and efxCourtCode IS NOT NULL
>> >
>> > and ( dlastname IS NOT NULL and LEN(dlastname) > 1 )
>> >
>> > and ( CaseNo IS NOT NULL and LEN(CaseNo) > 0 )
>> >
>> > and LTRIM(RTRIM(REPLACE(REPLACE(PLastName, ' ', ''), '.', ''))) NOT IN
>> > (SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
>> > REJECT_ALL='Y')
>> >
>> > and (filingtype <> 'SL' OR (filingType = 'SL' and substring(courtcode,1,1)
>> > <> 'N') OR (filingType = 'SL' AND substring(courtcode,1,1) = 'N' AND
>> > PLastName NOT IN (SELECT [plaintiff name] FROM NJ_SL_PLAINTIFF_DELETE )))
>> >
>> > ORDER BY RRD_sequence_no, courtcode
>> >
>> >
>>
>>
>>

7 Apr 2006 2:13 PM

Jim Underwood

A few suggestions:

1. Qualify all of your columns with the table alias. It makes the code
easier to follow and protects you from ambiguous columns later on. My
personal preference is to use menaingful aliases, with 3 to 6 characters,
that are clearer than single letters.

2. As Roy said, don't use distinct unless you have to. It very often hides
bad joins which should be found and corrected.

3. If this is your database and you are in the process of designing it,
there are several things to change here. First, give your columns better,
more consistent names. t.CourtCode should reference c.CourtCode, not c.CC.
The columns should have the same names in all tables if they are the same
data, it just makes it easier to understand everythign that is going on in
the database. If this is an existing database, or you dont have a say in
the design, then make due.

3a. As Roy said, don't allow bad data to begin with. Put constraints on
your columns so you can take all the cleanup functionality out of this SQL.
Again, this may not be an option for you.

4. get rid of (AMOUNT >= '00000050' OR AMOUNT >= 50)
The data is either numeric or its a string, there should not be a need for
both. if the datatype is numeric (I hope it is) then treat it as such.

5. If your date fields are stored as dates, then treat then as dates. Check
to see if they are null not empty strings.
ISNULL(COMPLAINTDATE,'') <> ''
should be
COMPLAINTDATE is not null

6. Remove redundancy with and/or where you can:
         AND ((FILINGTYPE IN ('FT','SL','CT')
               AND (ISNULL(JUDGMENTDATE,'') <> ''
                     OR ISNULL(COMPLAINTDATE,'') <> ''))
               OR ISNULL(JUDGMENTDATE,'') <> '')
can be done as:
    AND (
     (
     FILINGTYPE IN ('FT','SL','CT')
     AND
     ISNULL(COMPLAINTDATE,'') <> ''
     )
     OR ISNULL(JUDGMENTDATE,'') <> ''
    )
or, using is null:
    (
         (
         FILINGTYPE IN ('FT','SL','CT')
         AND
         COMPLAINTDATE is not null
         )
    OR JUDGMENTDATE is not null
    )

7. Consider moving everything that is not actually related to your join out
of the join and into the where clause. The only part that appears to belong
there is:
COURTCODE = C.CC
AND COURTTYPE = C.CT

Show quote

"James Juno" <JamesJ***@discussions.microsoft.com> wrote in message
news:2D8344E7-D04D-4D02-A814-BDE8904EE9AF@microsoft.com...
> Thanks Keith. I re-wrote it to look like this:
>
>
> Here is my first rewrite -
> SELECT DISTINCT t.CaseNo, t.Amount, t.DTaxID, t.DZip, t.DLastName,
>    t.DFirstName, t.DMidName, t.DSuffix, t.CoDLastName, t.CoDFirstName,
> t.CoDMidName,
>    t.CoDSuffix, t.CoDTaxID, t.DStAddress, t.DCity, t.DState,
>    t.PLastName, t.PFirstName, t.PMidName,
>    t.PstAddress, t.PCity, t.PState, t.PZip,
>    'FilingType'=(Case When FilingType='EV' Then 'CJ' Else FilingType End),
>             t.CourtCode, c.efxCourtCode, t.ResearcherNo, t.JudgmentDate,
> t.SatisfiedDate,
>         r.Efx_prt, r.EFX_action_code,
>         r.Efx_status_code, t.complaintDate, t.dismissalDate,
> t.dispositionType, t.dtype,
>         t.codType, t.RRD_sequence_no,'3'
>    FROM TBLCASES t
>      inner join RRD_Client_CourtCodes c
>       on (CourtCode = c.CC AND CourtType = c.CT
>             and PostedDate >= '20060301'   and PostedDate < '20060331'
>               AND ISNULL(t.DStAddress,'') <>''
>               AND ISNULL(t.DCity,'') <>''
>               AND ISNULL(t.DState,'') <> ''
>               AND LEN( LTRIM(RTRIM(t.DStAddress)) ) > 1
>               AND LEN( LTRIM(RTRIM(t.DCity)) ) > 1
>               AND LEN( LTRIM(RTRIM(t.DState)) ) > 1
>               AND ( t.dlastname is not null and len(t.dlastname) > 1 )
>               AND ( t.CaseNo is not null and len(t.CaseNo) > 0 )
>               AND c.efxCourtCode is not null
>      )-- on
>
>     inner join EFXRRD_RecordType R
>       on (r.RRD_rt=c.rt
>
>           ) -- on
>
>
>    WHERE (Amount >= '00000050' or Amount >= 50)
>    AND (DType = 'I' or isnull(coDType,'') = '' or CoDType = 'I')
>       AND (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405'
> and satisfieddate > judgmentdate))
>      AND ((filingType in ('FT', 'SL', 'CT') AND (isnull(judgmentdate, '')
<>
> '' or isnull(complaintdate,'') <> '') ) or isnull(judgmentDate,'') <> '')
>    AND ((isnull(dispositionType, '') = '') or (dispositionType in ('PF',
> 'PL', 'CL', 'FD', 'JD', 'ED', 'EX') and (isnull(dismissalDate,'') <> '' or
> isnull(satisfieddate,'') <> '')) or (dispositionType in ('JS', 'ES') and
> isnull(satisfieddate,'') <> ''))
>
>
>    AND RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
> DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
> FilingType End))
>    AND ( DState IN ( SELECT State FROM US_STATES ) )
>    AND ltrim(rtrim(replace(replace(PLastName, ' ', ''), '.', ''))) not in
> (SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
> REJECT_ALL='Y')
>
>    AND (filingtype <> 'SL' or (filingType = 'SL' and
> substring(courtcode,1,1) <> 'N') or (filingType = 'SL' and
> substring(courtcode,1,1) = 'N' and PLastName not in (SELECT [plaintiff
name]
> FROM NJ_SL_PLAINTIFF_DELETE )))
>    order by RRD_sequence_no, courtcode
>
>
> Thanks for any help I can get. James.
>
>
>
> "Keith Kratochvil" wrote:
>
> > Holy cow. There is a lot going on there.
> >
> > Can you tell us where the JOINs actually happen?
> >
> > You are joining three tables, so you need at least two JOIN statements.
> >
> > Perhaps rewriting the query so that it is more ANSI compliant would help
> > make the JOIN statement more obvious.
> >
> > --
> > Keith Kratochvil
> >
> >
> > "James Juno" <JamesJ***@discussions.microsoft.com> wrote in message
> > news:D07C7377-2B29-4017-BDC1-A6E73831FB8D@microsoft.com...
> > >I have a question with a join. As a new developer, I see that there
may be
> > > issues with the join in the query. Can someone please tell me what
you
> > > think? Thank you.
> > >
> > >
> > >
> > > SELECT DISTINCT CaseNo, Amount, DTaxID, DZip, DLastName,
> > >
> > > DFirstName, DMidName, DSuffix, CoDLastName, CoDFirstName, CoDMidName,
> > >
> > > CoDSuffix, CoDTaxID, DStAddress, DCity, DState, PLastName, PFirstName,
> > > PMidName,
> > >
> > > PstAddress, PCity, PState, PZip, 'FilingType'=(Case When
FilingType='EV'
> > > Then 'CJ' Else FilingType End),
> > >
> > > CourtCode, efxCourtCode, ResearcherNo, JudgmentDate, SatisfiedDate,
> > > c.Efx_prt, c.EFX_action_code,
> > >
> > > c.Efx_status_code, complaintDate, dismissalDate, dispositionType,
dtype,
> > > codType, RRD_sequence_no, '3'
> > >
> > > FROM
> > >
> > > tblcases, RRD_Client_CourtCodes, EFXRRD_RecordType c
> > >
> > >   WHERE PostedDate <= '20060405' and PostedDate >= '20060401'
> > >
> > > and c.RRD_rt=rt
> > >
> > > AND dispositionType in ('FE', 'FO', 'FW', 'FV', 'SE', 'SV', 'ST',
'JO',
> > > 'JE', 'JT', 'JV')
> > >
> > >
> > >
> > > and ( DState IN ( SELECT State FROM US_STATES ) )
> > >
> > > and (Amount >= '00000050' or Amount >= 50) and (DType = 'I' or
> > > isnull(coDType,'') = '' or CoDType = 'I')
> > >
> > > and (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405'
and
> > > satisfieddate > judgmentdate))
> > >
> > > and ((filingType in ('FT', 'SL', 'CT') and (isnull(judgmentdate, '')
<> ''
> > > or isnull(complaintdate,'') <> '') )
> > >
> > >          or isnull(judgmentDate,'') <> '')
> > >
> > > and ((isnull(dispositionType, '') = '')
> > >
> > >      or (dispositionType in ('PF', 'PL', 'CL', 'FD', 'JD', 'ED', 'EX',
> > > 'JE','JO','JV','JT','FE','FO','FV','FW','SE','SV','ST','EH','EI','ET')
and
> > > (isnull(dismissalDate,'') <> '' or isnull(satisfieddate,'') <> ''))
> > >
> > >      or (dispositionType in ('JS', 'ES') and isnull(satisfieddate,'')
<>
> > > ''))
> > >
> > > and ISNULL(DStAddress,'') <>'' AND ISNULL(DCity,'') <>'' AND
> > > ISNULL(DState,'') <> ''
> > >
> > > and LEN( LTRIM(RTRIM(DStAddress)) ) > 1 AND LEN(

LTRIM(RTRIM(DCity)) ) >
Show quote

> > > 1
> > >
> > > AND LEN( LTRIM(RTRIM(DState)) ) > 1
> > >
> > > and CourtCode = CC AND CourtType = CT
> > >
> > > and RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
> > > DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
> > > FilingType End))
> > >
> > > and efxCourtCode IS NOT NULL
> > >
> > > and ( dlastname IS NOT NULL and LEN(dlastname) > 1 )
> > >
> > > and ( CaseNo IS NOT NULL and LEN(CaseNo) > 0 )
> > >
> > > and LTRIM(RTRIM(REPLACE(REPLACE(PLastName, ' ', ''), '.', ''))) NOT
IN
> > > (SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
> > > REJECT_ALL='Y')
> > >
> > > and (filingtype <> 'SL' OR (filingType = 'SL' and

substring(courtcode,1,1)
Show quote

> > > <> 'N') OR (filingType = 'SL' AND substring(courtcode,1,1) = 'N' AND
> > > PLastName NOT IN (SELECT [plaintiff name] FROM
NJ_SL_PLAINTIFF_DELETE )))
> > >
> > > ORDER BY RRD_sequence_no, courtcode
> > >
> > >
> >
> >
> >

7 Apr 2006 2:53 PM

Keith Kratochvil

You have received lots of good advice from Roy, Jim, and sloan. Hopefully
you find this information helpful.

--
Keith Kratochvi

6 Apr 2006 9:44 PM

sloan

Unless your table has a very small number of items, you want to avoid

ISNULL like a black plague, because it always forces a table scan.

ex
and isnull(satisfieddate,'') <>

> ''))

Don't fall into this bad habit..

Look at the feedback for this blog:
http://weblogs.asp.net/eporter/archive/2005/01/21/358217.aspx

ISNULL are kinda ok in the SELECT statement, but really bad in the join or
where clauses.

Show quote

"James Juno" <JamesJ***@discussions.microsoft.com> wrote in message
news:D07C7377-2B29-4017-BDC1-A6E73831FB8D@microsoft.com...
> I have a question with a join. As a new developer, I see that there may
be
> issues with the join in the query. Can someone please tell me what you
> think? Thank you.
>
>
>
> SELECT DISTINCT CaseNo, Amount, DTaxID, DZip, DLastName,
>
> DFirstName, DMidName, DSuffix, CoDLastName, CoDFirstName, CoDMidName,
>
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState, PLastName, PFirstName,
> PMidName,
>
> PstAddress, PCity, PState, PZip, 'FilingType'=(Case When FilingType='EV'
> Then 'CJ' Else FilingType End),
>
> CourtCode, efxCourtCode, ResearcherNo, JudgmentDate, SatisfiedDate,
> c.Efx_prt, c.EFX_action_code,
>
> c.Efx_status_code, complaintDate, dismissalDate, dispositionType, dtype,
> codType, RRD_sequence_no, '3'
>
> FROM
>
> tblcases, RRD_Client_CourtCodes, EFXRRD_RecordType c
>
>    WHERE PostedDate <= '20060405' and PostedDate >= '20060401'
>
> and c.RRD_rt=rt
>
> AND dispositionType in ('FE', 'FO', 'FW', 'FV', 'SE', 'SV', 'ST', 'JO',
> 'JE', 'JT', 'JV')
>
>
>
> and ( DState IN ( SELECT State FROM US_STATES ) )
>
> and (Amount >= '00000050' or Amount >= 50) and (DType = 'I' or
> isnull(coDType,'') = '' or CoDType = 'I')
>
> and (isnull(satisfieddate, '') = '' or (satisfieddate <= '20060405' and
> satisfieddate > judgmentdate))
>
> and ((filingType in ('FT', 'SL', 'CT') and (isnull(judgmentdate, '') <>
''
> or isnull(complaintdate,'') <> '') )
>
>           or isnull(judgmentDate,'') <> '')
>
> and ((isnull(dispositionType, '') = '')
>
>       or (dispositionType in ('PF', 'PL', 'CL', 'FD', 'JD', 'ED', 'EX',
> 'JE','JO','JV','JT','FE','FO','FV','FW','SE','SV','ST','EH','EI','ET') and
> (isnull(dismissalDate,'') <> '' or isnull(satisfieddate,'') <> ''))
>
>       or (dispositionType in ('JS', 'ES') and isnull(satisfieddate,'') <>
> ''))
>
> and ISNULL(DStAddress,'') <>'' AND ISNULL(DCity,'') <>'' AND
> ISNULL(DState,'') <> ''
>
> and LEN( LTRIM(RTRIM(DStAddress)) ) > 1 AND LEN( LTRIM(RTRIM(DCity)) ) >
1
>
>            AND LEN( LTRIM(RTRIM(DState)) ) > 1
>
> and CourtCode = CC AND CourtType = CT
>
> and RT = isnull(nullif((Case When DispositionType='ES' Then 'JS' Else
> DispositionType End),''), (Case When FilingType='EV' Then 'CJ' Else
> FilingType End))
>
> and efxCourtCode IS NOT NULL
>
> and ( dlastname IS NOT NULL and LEN(dlastname) > 1 )
>
> and ( CaseNo IS NOT NULL and LEN(CaseNo) > 0 )
>
> and LTRIM(RTRIM(REPLACE(REPLACE(PLastName, ' ', ''), '.', ''))) NOT IN
> (SELECT ptype FROM PLAINTIFF_TYPES_FILTER WHERE EFX_REJECT='Y' OR
> REJECT_ALL='Y')
>
> and (filingtype <> 'SL' OR (filingType = 'SL' and substring(courtcode,1,1)
> <> 'N') OR (filingType = 'SL' AND substring(courtcode,1,1) = 'N' AND
> PLastName NOT IN (SELECT [plaintiff name] FROM NJ_SL_PLAINTIFF_DELETE )))
>
> ORDER BY RRD_sequence_no, courtcode
>
>