Solved

Trouble modifying TSQL query

Posted on 2008-10-14
20
198 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
  • 9
  • 8
  • 2
  • +1
20 Comments
 
LVL 9

Expert Comment

by:jamesgu
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 10

Author Comment

by:GeoffSutton
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
in the case statement when CodesToCollectors.Manager  column is null then the table shuld be joined on  Collectors.Manager column.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

by:Chandan_Gowda
Comment Utility
Instead of Collectors.Manager USe C.Manager
0
 
LVL 7

Accepted Solution

by:
Chandan_Gowda earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
use right join instead of Inner join and please let me kno how it behaves
0
 
LVL 10

Author Comment

by:GeoffSutton
Comment Utility
Exactly the same....  :)  Is that helpful?

Geoff
0
 
LVL 7

Expert Comment

by:Chandan_Gowda
Comment Utility
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
Comment Utility
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
Comment Utility
Chandan, I just figured it out:  The NULL values were because those collectors had no corresponding Collector Codes.  Thanks.

Geoff
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now