• dwain.c (4/21/2015)


    Wayne West (4/21/2015)


    Dwain, that first code worked perfect, the second one only has D/N/P/Y appearing for district 000, which may or may not work live. Since the first one does work the way that I need, that's the one that I'll use. I'd never used that format of a Select, most of my time has previously been spent in a DBA role. This greatly improves the fairly ugly code that I was dealing with. Much thanks!

    Strange that the second solution didn't work. Thought I'd nailed it. But then I didn't have access to the underlying data to be sure.

    In any event, glad I provided something that could help.

    Edit: I realized that it is probably the second LEFT OUTER JOIN in my second solution that is the reason it doesn't work for you. Because there's no district 000 in that table.

    I went back and forth on providing tables and some sample data and ended up not doing it. It's development code, I'm still working with it because I'm getting a scan instead of a seek against the districts table, which isn't a huge deal because it's pretty static and non-growing, but just the same I'd like it to work better. The left join isn't needed as it's a not null field and related to the district table and is now removed, like I said, it's development code. The scan is only 8% of the query cost, so I'm leaning towards not worrying too much about the scan since this is one of those reports that's needed irregularly for 2-3 months at the start of the year then it won't be run until the following year.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]