Solved

Trouble modifying TSQL query

Posted on 2008-10-14
20
225 Views
Last Modified: 2012-05-05
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
Comment
Question by:GeoffSutton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
  • +1
20 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22713385
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
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 22713420
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
 
LVL 5

Expert Comment

by:PaulKeating
ID: 22713418
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 10

Author Comment

by:GeoffSutton
ID: 22713553
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22713618
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22713626
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
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 22713689
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
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 22713703
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22713731
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
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 22713733
in the case statement when CodesToCollectors.Manager  column is null then the table shuld be joined on  Collectors.Manager column.
0
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 22713745
Instead of Collectors.Manager USe C.Manager
0
 
LVL 7

Accepted Solution

by:
Chandan_Gowda earned 500 total points
ID: 22713757
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22713795
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22713827
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
 
LVL 5

Expert Comment

by:PaulKeating
ID: 22713840
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
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 22713857
use right join instead of Inner join and please let me kno how it behaves
0
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22713881
Exactly the same....  :)  Is that helpful?

Geoff
0
 
LVL 7

Expert Comment

by:Chandan_Gowda
ID: 22713912
there might be some data redundancy....I can figure it out only if i see the data on both tables :(
0
 
LVL 10

Author Comment

by:GeoffSutton
ID: 22713936
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
 
LVL 10

Author Comment

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

Geoff
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
IIF in access query 19 39
SQL Rewrite without the NULLIF 4 25
SP result not being displayed 5 37
Applying Roles in Common Scenarios 3 16
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question