SQL syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL syntax

Author

Topic

Jannette
Starting Member

26 Posts

Posted - 2011-09-01 : 04:58:42

I have the following query that has been working, however I only want to now see records where the Case statement for the Division and Department have results that are '???'. I have amended the where statement to cater for this but I get the error message invalid fields Department and Division, how can I get around this ?

=====================================================================

Select Distinct
'"'+PLACEMENT.Placement_Code+'"',
'"'+Client_Code+'"',
'"'+Candidate_Code+'"',
Case When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id<>2) Then '"OSS"'
When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id=2) Then '"OES"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id=27) Then '"RPO"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (7,15)) Then '"OWB"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (3,4,13,15,20,21,28)) Then '"OWON"'
Else '"???"' End as Division,
Case When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id<>2) Then '"OSS"'
When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id=2) Then '"OES"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id=27) Then '"RPO"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (7,15)
and CANDIDATE.Consultant_Id=29) Then '"OW100"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (7,15)
and CANDIDATE.Consultant_Id=38) Then '"OW150"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (3,4,13,15,20,21,28)
and CANDIDATE.Consultant_Id=31) Then '"OW301"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (3,4,13,15,20,21,28)
and CANDIDATE.Consultant_Id=85) Then '"OW401"'
Else '"???"' End as Department,
'"'+Substring(Placement_Ref,1,30)+'"',
'"'+TOWN.Description +', '+ PostCode+'"',
'"'+CONVERT(VARCHAR(10),Min(Placement_DT),103)+'"',
'"'+CONVERT(VARCHAR(10),Max(Placement_DT),103)+'"',
'""',
'"'+Booking_Ref+'"',
'"'+PERSON.First_Name +' '+ PERSON.Last_Name+'"',
'""', '""','""', '""','""', '""','""', '""', '""','""', '""',
'""', '""','""', '""','""',
Case When (CANDIDATE.Branch_Id=3) Then '"OWWEEK"'
When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id=25) Then '"OMMTH"'
Else '"OMWEEK"' End as Employer,
'""', '""', '""', '"Y"',
'"'+Substring(Placement_Ref,1,8)+'"',
'""', '""', '""',
'"'+CLIENT_CONTACT.Title +' '+CLIENT_CONTACT.First_Name +' '+CLIENT_CONTACT.Last_Name+'"',
'"'+SUBSTRING(PLACEMENT.Notes,1,300)+'"',
'""', '""','""', '""','""', '""','""', '""','""', '""','""', '""','""', '""',
'"'+Client_Code+'"',
'"'+Candidate_Code+'"',
'""', '""','""', '""','""', '""','""', '""','""', '""','""', '""','""', '""',
0.00, 0.00, 0.00

From PLACEMENT Inner Join PLACEMENT_DAY On PLACEMENT.Placement_Id = PLACEMENT_DAY.Placement_Id
Inner Join CLIENT On PLACEMENT.Client_Id = CLIENT.Client_Id
Inner Join CANDIDATE On PLACEMENT.Candidate_Id = CANDIDATE.Candidate_Id
Inner Join CLIENT_ADDRESS On CLIENT.Client_Address_Id = CLIENT_ADDRESS.Client_Address_Id
Inner Join ADDRESS On CLIENT_ADDRESS.Address_Id = ADDRESS.Address_Id
Inner Join TOWN On ADDRESS.Town_Id = TOWN.Town_Id
Inner Join CONSULTANT On PLACEMENT.Consultant_id = CONSULTANT.Consultant_id
Inner Join PERSON On CONSULTANT.Person_Id = PERSON.Person_Id
Inner Join CLIENT_CONTACT On CLIENT.Client_Contact_Id = CLIENT_CONTACT.Client_Contact_Id

Where PLACEMENT.Active_YN = 'Y' and (Division='"???"' or Department='"???"')

Group By PLACEMENT.Placement_id, PLACEMENT.Placement_Code, Client_Code, Placement_Ref, Booking_Ref,
Candidate_Code, Start_TM, End_TM, TOWN.Description, PostCode, PERSON.Last_Name, PERSON.First_Name,
CLIENT_CONTACT.First_Name, CLIENT_CONTACT.Last_Name, CLIENT_CONTACT.Title, PLACEMENT.Notes,
CANDIDATE.Branch_Id, CANDIDATE.Division_Id, CANDIDATE.Consultant_Id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 05:13:57

[code]
SELECT *
FROM
(
Select Distinct
'"'+PLACEMENT.Placement_Code+'"',
'"'+Client_Code+'"',
'"'+Candidate_Code+'"',
Case When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id<>2) Then '"OSS"'
When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id=2) Then '"OES"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id=27) Then '"RPO"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (7,15)) Then '"OWB"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (3,4,13,15,20,21,28)) Then '"OWON"'
Else '"???"' End as Division,
Case When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id<>2) Then '"OSS"'
When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id=2) Then '"OES"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id=27) Then '"RPO"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (7,15)
and CANDIDATE.Consultant_Id=29) Then '"OW100"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (7,15)
and CANDIDATE.Consultant_Id=38) Then '"OW150"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (3,4,13,15,20,21,28)
and CANDIDATE.Consultant_Id=31) Then '"OW301"'
When (CANDIDATE.Branch_Id=3 and CANDIDATE.Division_Id In (3,4,13,15,20,21,28)
and CANDIDATE.Consultant_Id=85) Then '"OW401"'
Else '"???"' End as Department,
'"'+Substring(Placement_Ref,1,30)+'"',
'"'+TOWN.Description +', '+ PostCode+'"',
'"'+CONVERT(VARCHAR(10),Min(Placement_DT),103)+'"',
'"'+CONVERT(VARCHAR(10),Max(Placement_DT),103)+'"',
'""',
'"'+Booking_Ref+'"',
'"'+PERSON.First_Name +' '+ PERSON.Last_Name+'"',
'""', '""','""', '""','""', '""','""', '""', '""','""', '""',
'""', '""','""', '""','""',
Case When (CANDIDATE.Branch_Id=3) Then '"OWWEEK"'
When (CANDIDATE.Branch_Id=2 and CANDIDATE.Division_Id=25) Then '"OMMTH"'
Else '"OMWEEK"' End as Employer,
'""', '""', '""', '"Y"',
'"'+Substring(Placement_Ref,1,8)+'"',
'""', '""', '""',
'"'+CLIENT_CONTACT.Title +' '+CLIENT_CONTACT.First_Name +' '+CLIENT_CONTACT.Last_Name+'"',
'"'+SUBSTRING(PLACEMENT.Notes,1,300)+'"',
'""', '""','""', '""','""', '""','""', '""','""', '""','""', '""','""', '""',
'"'+Client_Code+'"',
'"'+Candidate_Code+'"',
'""', '""','""', '""','""', '""','""', '""','""', '""','""', '""','""', '""',
0.00, 0.00, 0.00

From PLACEMENT Inner Join PLACEMENT_DAY On PLACEMENT.Placement_Id = PLACEMENT_DAY.Placement_Id
Inner Join CLIENT On PLACEMENT.Client_Id = CLIENT.Client_Id
Inner Join CANDIDATE On PLACEMENT.Candidate_Id = CANDIDATE.Candidate_Id
Inner Join CLIENT_ADDRESS On CLIENT.Client_Address_Id = CLIENT_ADDRESS.Client_Address_Id
Inner Join ADDRESS On CLIENT_ADDRESS.Address_Id = ADDRESS.Address_Id
Inner Join TOWN On ADDRESS.Town_Id = TOWN.Town_Id
Inner Join CONSULTANT On PLACEMENT.Consultant_id = CONSULTANT.Consultant_id
Inner Join PERSON On CONSULTANT.Person_Id = PERSON.Person_Id
Inner Join CLIENT_CONTACT On CLIENT.Client_Contact_Id = CLIENT_CONTACT.Client_Contact_Id

Group By PLACEMENT.Placement_id, PLACEMENT.Placement_Code, Client_Code, Placement_Ref, Booking_Ref,
Candidate_Code, Start_TM, End_TM, TOWN.Description, PostCode, PERSON.Last_Name, PERSON.First_Name,
CLIENT_CONTACT.First_Name, CLIENT_CONTACT.Last_Name, CLIENT_CONTACT.Title, PLACEMENT.Notes,
CANDIDATE.Branch_Id, CANDIDATE.Division_Id, CANDIDATE.Consultant_Id
)t

Where PLACEMENT.Active_YN = 'Y' and (Division='"???"' or Department='"???"')
[/code]

replace * with your actual column names
Also ahy id distinct required if you're already grouping by same set of columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2011-09-01 : 07:06:04

I have amended the code and now get the following error:
================================================================
Msg 8156, Level 16, State 1, Line 2
The column 'Description' was specified multiple times for 'T'.
================================================================

How can I sort this please ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 07:34:12

i can see only one description column in your posted query that too as a part of concatenation. So I dont think above query will cause this error. please post the exact query that you're using.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2011-09-01 : 11:19:13

All sorted now thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 00:27:06

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources