asked on

Trouble modifying TSQL query

Hey Experts,

Here is the query I currently have:
SELECT
CodestoCollectors.id AS CodeID,
CodestoCollectors.CollectorCode,
Collectors.CollectorLast + ', ' + Collectors.CollectorFirst AS Name,
CodestoCollectors.Manager,
Collectors.CollectorNumber,
Collectors.Branch,
CodestoCollectors.Client AS ClientCode,
Collectors.Position AS PositionCode,
Branches.BranchName,
COA_Positions.Position,
COA_Clients.Client
FROM
COA_Clients
RIGHT OUTER JOIN CodestoCollectors ON COA_Clients.code = CodestoCollectors.Client
FULL OUTER JOIN Collectors
LEFT OUTER JOIN COA_Positions ON Collectors.Position = COA_Positions.code
LEFT OUTER JOIN Branches ON Collectors.Branch = Branches.Branch
ON CodestoCollectors.CollectorNumber = Collectors.CollectorNumber
WHERE
(Collectors.Employed = 'Y') AND
(UPPER(Collectors.CollectorLast) <> 'ZZHOUSE')
ORDER BY
Collectors.CollectorLast,
Collectors.CollectorFirst,
CodestoCollectors.CollectorCode

My problem is that I need to add another field, Manager Name, which would be SELECT C.CollectorLast + ', ' + C.CollectorFirst as ManagerName from Collectors as C INNER JOIN ....
The problem I am having is that I need to combine this query as part of the above query, and the join is on CodesToCollectors.Manager=C.CollectorNumber UNLESS CodesToCollectors.Manager IS NULL, in which case I will join on Collectors.Manager.
I have no idea how to form the CASE WHEN statement, and where to place it (or even if I should use it) in order to get the same results but with the manager name coming back rather than the number (as it is now).

Please help with this.

Thanks

jamesgu

try this:

case when CodestoCollectors.Manager is null then SELECT C.CollectorLast + ', ' + C.CollectorFirst as ManagerName from Collectors as C where CodesToCollectors.Manager=C.CollectorNumber
else SELECT C.CollectorLast + ', ' + C.CollectorFirst as ManagerName from Collectors as C where Collectors.Manager=C.CollectorNumber
end

SELECT 
  CodestoCollectors.id AS CodeID, 
  CodestoCollectors.CollectorCode, 
  Collectors.CollectorLast + ', ' + Collectors.CollectorFirst AS Name, 
  CodestoCollectors.Manager, 
  
  case when CodestoCollectors.Manager is null then SELECT C.CollectorLast + ', ' + C.CollectorFirst as ManagerName from Collectors as C where CodesToCollectors.Manager=C.CollectorNumber
  		else SELECT C.CollectorLast + ', ' + C.CollectorFirst as ManagerName from Collectors as C where Collectors.Manager=C.CollectorNumber
  end
  
  Collectors.CollectorNumber, 
  Collectors.Branch, 
  CodestoCollectors.Client AS ClientCode, 
  Collectors.Position AS PositionCode, 
  Branches.BranchName, 
  COA_Positions.Position, 
  COA_Clients.Client 
FROM 
  COA_Clients 
    RIGHT OUTER JOIN CodestoCollectors ON COA_Clients.code = CodestoCollectors.Client 
    FULL OUTER JOIN Collectors 
      LEFT OUTER JOIN COA_Positions ON Collectors.Position = COA_Positions.code 
      LEFT OUTER JOIN Branches ON Collectors.Branch = Branches.Branch 
    ON CodestoCollectors.CollectorNumber = Collectors.CollectorNumber 
WHERE 
  (Collectors.Employed = 'Y') AND 
  (UPPER(Collectors.CollectorLast) <> 'ZZHOUSE') 
ORDER BY 
  Collectors.CollectorLast, 
  Collectors.CollectorFirst, 
  CodestoCollectors.CollectorCode

Open in new window

Chandan_Gowda

Please try the below query

SELECT 
  CodestoCollectors.id AS CodeID, 
  CodestoCollectors.CollectorCode, 
  Collectors.CollectorLast + ', ' + Collectors.CollectorFirst AS Name, 
 C.CollectorLast + ', ' + C.CollectorFirst as ManagerName ,
  CodestoCollectors.Manager, 
  Collectors.CollectorNumber, 
  Collectors.Branch, 
  CodestoCollectors.Client AS ClientCode, 
  Collectors.Position AS PositionCode, 
  Branches.BranchName, 
  COA_Positions.Position, 
  COA_Clients.Client 
FROM 
  COA_Clients 
    RIGHT OUTER JOIN CodestoCollectors 
		INNER JOIN Collectors  C
				ON CASE WHEN CodesToCollectors.Manager IS NULL THEN CodesToCollectors.Manager  ELSE CodesToCollectors.Manager END =C.CollectorNumber 
	ON COA_Clients.code = CodestoCollectors.Client 
    FULL OUTER JOIN Collectors 
      LEFT OUTER JOIN COA_Positions ON Collectors.Position = COA_Positions.code 
      LEFT OUTER JOIN Branches ON Collectors.Branch = Branches.Branch 
    ON CodestoCollectors.CollectorNumber = Collectors.CollectorNumber 
WHERE 
  (Collectors.Employed = 'Y') AND 
  (UPPER(Collectors.CollectorLast) <> 'ZZHOUSE') 
ORDER BY 
  Collectors.CollectorLast, 
  Collectors.CollectorFirst, 
  CodestoCollectors.CollectorCode

Open in new window

PaulKeating

You can't use a CASE statement to switch between different tables to join to, even though you clearly wish you could. CASE allows you to conditionally determine a column value, either in a SELECT or a WHERE. You can't use it to conditionally determine the tables making up the query, or the nature of the joins, or the operator in the join (=, > etc).

The simplest way to do what you want is to have two queries, one that says
WHERE CodesToCollectors.Manager IS NULL
and joins to Collectors.Manager

and a second nearly identical one that says
WHERE CodesToCollectors.Manager IS NOT NULL
and joins to CodesToCollectors.Manager

and then UNION the two SELECTs together.

Geoff Sutton

ASKER

Jamesqu: I tried your query and received a number of errors back:
Error in SELECT clause: expression near 'SELECT'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Unable to parse query text.

They look minor, and if none of the other queries work I will come back to this. Thanks.

Geoff Sutton

ASKER

Chandan, your query is giving me null for CodesToCollectors when CodesToCollectors.Manager is null. Seems to work when there is a manager value in CodesToCollectors.

Thanks.

Geoff Sutton

ASKER

Paul, unfortunately you comment is next to useless to me. If you could give me a code sample I may be able to use that, but can a union be used outside of a stored procedure?

Thanks.

Chandan_Gowda

Sorry, i made a small mistake in the case statement...PLease use the below query

SELECT 
  CodestoCollectors.id AS CodeID, 
  CodestoCollectors.CollectorCode, 
  Collectors.CollectorLast + ', ' + Collectors.CollectorFirst AS Name, 
  CodestoCollectors.Manager, 
  Collectors.CollectorNumber, 
  Collectors.Branch, 
  CodestoCollectors.Client AS ClientCode, 
  Collectors.Position AS PositionCode, 
  Branches.BranchName, 
  COA_Positions.Position, 
  COA_Clients.Client 
FROM 
  COA_Clients 
    RIGHT OUTER JOIN CodestoCollectors ON COA_Clients.code = CodestoCollectors.Client 
		INNER JOIN Collectors  C                               
		 ON CASE WHEN CodesToCollectors.Manager IS NULL THEN CodesToCollectors.Manager  ELSE Collectors.Manager END =C.CollectorNumber 
    FULL OUTER JOIN Collectors 
      LEFT OUTER JOIN COA_Positions ON Collectors.Position = COA_Positions.code 
      LEFT OUTER JOIN Branches ON Collectors.Branch = Branches.Branch 
    ON CodestoCollectors.CollectorNumber = Collectors.CollectorNumber 
WHERE 
  (Collectors.Employed = 'Y') AND 
  (UPPER(Collectors.CollectorLast) <> 'ZZHOUSE') 
ORDER BY 
  Collectors.CollectorLast, 
  Collectors.CollectorFirst, 
  CodestoCollectors.CollectorCode

Open in new window

Chandan_Gowda

I copied the wrong one....Please ignore the previous 1...Use this

SELECT 
  CodestoCollectors.id AS CodeID, 
  CodestoCollectors.CollectorCode, 
  Collectors.CollectorLast + ', ' + Collectors.CollectorFirst AS Name, 
  CodestoCollectors.Manager, 
  Collectors.CollectorNumber, 
  Collectors.Branch, 
  CodestoCollectors.Client AS ClientCode, 
  Collectors.Position AS PositionCode, 
  Branches.BranchName, 
  COA_Positions.Position, 
  COA_Clients.Client 
FROM 
  COA_Clients 
    RIGHT OUTER JOIN CodestoCollectors ON COA_Clients.code = CodestoCollectors.Client 
		INNER JOIN Collectors  C                               
		 ON CASE WHEN CodesToCollectors.Manager IS NULL THEN Collectors.Manager  ELSE CodesToCollectors.Manager  END =C.CollectorNumber 
    FULL OUTER JOIN Collectors 
      LEFT OUTER JOIN COA_Positions ON Collectors.Position = COA_Positions.code 
      LEFT OUTER JOIN Branches ON Collectors.Branch = Branches.Branch 
    ON CodestoCollectors.CollectorNumber = Collectors.CollectorNumber 
WHERE 
  (Collectors.Employed = 'Y') AND 
  (UPPER(Collectors.CollectorLast) <> 'ZZHOUSE') 
ORDER BY 
  Collectors.CollectorLast, 
  Collectors.CollectorFirst, 
  CodestoCollectors.CollectorCode

Open in new window

Geoff Sutton

ASKER

Chandan, I had tried that myself. Getting Collectors.Manager could not be found. If we put the case after Collectors would that help? Or do we need to join Collectors a third time to make this work?

Thanks,

Geoff

Chandan_Gowda

in the case statement when CodesToCollectors.Manager column is null then the table shuld be joined on Collectors.Manager column.

Chandan_Gowda

Instead of Collectors.Manager USe C.Manager

ASKER CERTIFIED SOLUTION

Chandan_Gowda

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geoff Sutton

ASKER

Chandan, using C.Manager worked - Going to try copying in this latest to see if I get an improvement. I am still getting a few null values but I will blame that on Data, not on query since the bulk seems to be giving me good values. Much thanks.
Geoff

Geoff Sutton

ASKER

Still getting about half nulls, but that may be data lining up incorrectly. I am getting correct values from both sides, so this query is working correctly. Although I believe I should be getting a definite answer from Collectors (where I set the manager value by default) all the time when CodesToCollectors.Manager is null (which they all are except 1 as a test). As a follow up, can you see why they woudl be coming up null without having sample data?

Thanks,
Geoff

PaulKeating

I'm not going to write out the entire query for you. Simply because I don't have your table definitions or your data, so there is no way for me to check a 100-line query. If I put one comma wrong, all I will get back is "that doesn't work".

Of course you can use a union close outside a stored proc. Whatever gave you that idea?

Start with your existing query. To the WHERE clause add:
AND CodesToCollectors.Manager IS NULL

Put UNION below the query and copy the entire query after it. Change the WHERE clause in the copy to
AND CodesToCollectors.Manager IS NOT NULL

Remove the ORDER BY clause from the first part of the union. You can only have one.

Now, that should give you exactly the same output as you had before. (Check before proceeding.)

Finally, add the extra field to both SELECT clauses. Add the appropriate join to each query.
What is the "appropriate join"? Well, I can't work that out because you didn't explain it clearly enough. (Remember, I don't have your schema). But you said:

the join is on CodesToCollectors.Manager=C.CollectorNumber UNLESS CodesToCollectors.Manager IS NULL, in which case I will join on Collectors.Manager.

so CodesToCollectors.Manager=C.CollectorNumber goes in the second part of the union, and whatever you meant by "in which case I will join on Collectors.Manager" goes in the first part.

Chandan_Gowda

use right join instead of Inner join and please let me kno how it behaves

Geoff Sutton

ASKER

Exactly the same.... :) Is that helpful?

Geoff

Chandan_Gowda

there might be some data redundancy....I can figure it out only if i see the data on both tables :(

Geoff Sutton

ASKER

I also see the in the select portion, Where I replaced CodesToCollectors.Manager with (C.CollectorNumber as Manager, C.CollectorLast + ', ' + C.CollectorLast as ManagerName) I am getting Null for CodeID, CollectorCode, Manager and ManagerName. I am going to go back and look at the raw data - This feels like a data problem.

Geoff

Geoff Sutton

ASKER

Chandan, I just figured it out: The NULL values were because those collectors had no corresponding Collector Codes. Thanks.

Geoff