Extra row with null values I need to get rid of

[wheels0323]

SELECT TTBL.RD_DISTRICT, TTBL.LOCATION, JOBTITLE, NAME, Date, SORTORDER
FROM 
  (SELECT RD_DISTRICT, LOCATION, CONVERT(CHAR(26), JOBTITLE) as JOBTITLE, JOBCODE, SORTORDER
   FROM
    (SELECT DISTINCT CONVERT(CHAR(26), JOBTITLE) as JOBTITLE, JOBCODE,
       SORTORDER=CASE WHEN JOBCODE = 1231 THEN 1
                      WHEN JOBCODE = 1405 THEN 2
                      WHEN JOBCODE = 2336 OR JOBCODE = 2337 THEN 3 
                      WHEN JOBCODE = 2335 THEN 4
                      WHEN JOBCODE = 1890 THEN 5
                      WHEN JOBCODE = 1136 THEN 6
                      WHEN JOBCODE = 2169 THEN 7
                      WHEN JOBCODE = 1194 THEN 8
                      WHEN JOBCODE = 1017 THEN 9
                      WHEN JOBCODE = 1045 THEN 10
                      WHEN JOBCODE = 1581 THEN 11
                      WHEN JOBCODE = 2041 THEN 12
                      WHEN JOBCODE = 2339 THEN 13
                      WHEN JOBCODE = 2340 THEN 14
                      WHEN JOBCODE = 2350 THEN 15
                      WHEN JOBCODE = 2342 THEN 16
                      WHEN JOBCODE = 2341 THEN 17
                      WHEN JOBCODE = 1105 THEN 18
                      WHEN JOBCODE = 1260 THEN 19
                      WHEN JOBCODE = 2338 THEN 20 
                 END    
     FROM dbo.PS_RD_EMPLOYEES_VW 
     WHERE JOBCODE IN (1231, 1405, 2336, 2337, 2335, 1890, 1136, 2169, 1194,
                       1017, 1045, 1581, 2041, 2339, 2340, 2350, 2342,
                       2341, 1105, 1260, 2338)) AS JTBL
     CROSS JOIN
    (SELECT DISTINCT RD_DISTRICT, LOCATION
     FROM dbo.PS_RD_EMPLOYEES_VW 
     WHERE RD_DISTRICT = 'D1') AS LTBL) AS TTBL
LEFT OUTER JOIN
  (SELECT RD_DISTRICT, LOCATION, NAME, CONVERT(VARCHAR(8), DEPT_ENTRY_DT, 1) AS Date, JOBCODE
   FROM dbo.PS_RD_EMPLOYEES_VW 
   WHERE 
     RD_DISTRICT = 'D1' AND
     JOBCODE IN (1231, 1405, 2336, 2337 , 2335, 1890, 1136, 2169, 1194,
                 1017, 1045, 1581, 2041, 2339, 2340, 2350, 2342,
                 2341, 1105, 1260, 2338)) AS PTBL
  ON TTBL.LOCATION = PTBL.LOCATION AND
     TTBL.JOBCODE = PTBL.JOBCODE
ORDER BY TTBL.LOCATION, SORTORDER

In my code above, I have two JOBCODE'S for number '3'. I need to do this b/c of work reasons. The problem with this, is that it always brings back two lines of row of data when I only need one, unless two people exist with that JOBCODE.

So right now it comes back like...

D1 06301 GROCERY OPERATIONS MANAGER BOB 11/16/08 2336 3
D1 06301 GROCERY OPERATIONS MANAGER NULL NULL 2337 3

Just for that one job code. I need only that first line to come back, UNLESS another person has that jobcode at the assigned location.

How would I do this?
I know i hafta modify the

                      WHEN JOBCODE = 2336 OR JOBCODE = 2337 THEN 3

section somehow. Any ideas would be great. Thanks. I will give many stars for an answer.

 

[gmmastros]

Is there ever a reason to have a NULL in the name column? If not, you could simply do....

[red]Where  Name is not null[/red]
ORDER BY TTBL.LOCATION, SORTORDER

The red line would go right above the last line (the order by).

It's impossible to know if this is the correct way to solve this problem because you appear to have a relatively complicated table structure and query.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wheels0323]

I can't do that b/c there are certain jobcodes that dont need data to come back for it, but I still need to show null.

Yet these two jobcodes are the same job, and I need only one row to come back for each..

Either one row with data and the person info,
or one row with the jobtitle and all null values,
or two rows with two different people and all info..

i need a way to do a not null for each jobcode if the other jobcode has info in just for those two jobcodes..
'2336' and '2337'