Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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
0
GeoffSutton
Asked:
GeoffSutton
  • 9
  • 8
  • 2
  • +1
1 Solution
 
jamesguCommented:
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

0
 
Chandan_GowdaCommented:
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

0
 
PaulKeatingCommented:
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.
 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GeoffSuttonAuthor Commented:
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.
0
 
GeoffSuttonAuthor Commented:
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.
0
 
GeoffSuttonAuthor Commented:
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.
0
 
Chandan_GowdaCommented:
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

0
 
Chandan_GowdaCommented:
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

0
 
GeoffSuttonAuthor Commented:
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
0
 
Chandan_GowdaCommented:
in the case statement when CodesToCollectors.Manager  column is null then the table shuld be joined on  Collectors.Manager column.
0
 
Chandan_GowdaCommented:
Instead of Collectors.Manager USe C.Manager
0
 
Chandan_GowdaCommented:
USe the below query...It shuld definitely work
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 C.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

0
 
GeoffSuttonAuthor Commented:
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
0
 
GeoffSuttonAuthor Commented:
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
0
 
PaulKeatingCommented:
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.


0
 
Chandan_GowdaCommented:
use right join instead of Inner join and please let me kno how it behaves
0
 
GeoffSuttonAuthor Commented:
Exactly the same....  :)  Is that helpful?

Geoff
0
 
Chandan_GowdaCommented:
there might be some data redundancy....I can figure it out only if i see the data on both tables :(
0
 
GeoffSuttonAuthor Commented:
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
0
 
GeoffSuttonAuthor Commented:
Chandan, I just figured it out:  The NULL values were because those collectors had no corresponding Collector Codes.  Thanks.

Geoff
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 9
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now