| Author |
 Topic  |
|
|
forrestgump
Starting Member
18 Posts |
 Posted - 02/07/2013 : 04:33:30
|
I have created a 'UniqueID' field using the CONCAT function. This field creates fine when I use it in a new query window as:
SELECT
CONCAT(dbo.Data.[Req ID], '-', dbo.Data.[Candidate ref num]) AS UniqueID
FROM dbo.Data
But when I try and place the code in a view with the rest of my view code as:
CONCAT(dbo.Data.[Req ID], '-', dbo.Data.[Candidate ref num])AS UniqueID
I get the error message The CONCAT function requires 2 arguments?
Any ideas why?
Regards,
Forrest |
Edited by - forrestgump on 02/07/2013 05:02:19
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48092 Posts |
Posted - 02/07/2013 : 04:49:01
|
can you show your full view query?
how did the table name change to Data in the view code?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
forrestgump
Starting Member
18 Posts |
Posted - 02/07/2013 : 05:08:02
|
Sorry visakh ignore that. I have amended= the code in the original post.
SELECT dbo.Data.[Req ID], dbo.Data.[Current Req Status], dbo.Data.[Job title], dbo.Data.[Geographic location], dbo.Data.SPU,
dbo.Data.[Business unit], dbo.Data.Department, dbo.Data.[Job category], dbo.Data.[Level], dbo.Data.Recruiter,
dbo.Data.[Hiring Manager], dbo.Data.[First name], dbo.Data.[Last name], dbo.Data.[E-mail], dbo.Data.[Candidate ref num],
dbo.Data.[Current HR status], dbo.Data.[Current HR Status Updated on], dbo.Data.[Candidate Type], dbo.Data.[0-Filed],
dbo.Data.[Met Minimum Requirements], dbo.Data.[Recruiter Approved], dbo.Data.[Technical Screen], dbo.Data.[Hiring Manager Approved],
dbo.Data.Interview, dbo.Data.[Offer-UKUS], dbo.Data.[Offer-MoW], dbo.Data.[Verbal Offer Accepted - UK/US],
dbo.Data.[Verbal Offer Accepted - MoW], dbo.Data.[Verbal Offer Accepted - US/UK - EXTERNAL], dbo.Data.Hired, dbo.HRStatusMap.TAS,
dbo.HRStatusMap.REPORT, dbo.HRStatusMap.REPORT2, CASE WHEN [Offer-UKUS] IS NOT NULL
THEN [Offer-UKUS] ELSE [Offer-MoW] END AS Offer_Made_Date, CASE WHEN [Verbal Offer Accepted - UK/US] IS NOT NULL
THEN [Verbal Offer Accepted - UK/US] ELSE [Verbal Offer Accepted - MoW] END AS VOF_Accept_Date, DATEDIFF(day,
dbo.Data.[Met Minimum Requirements], dbo.Data.[Recruiter Approved]) AS [No Of Days in Met Min], DATEDIFF(day,
dbo.Data.[Recruiter Approved], dbo.Data.[Hiring Manager Approved]) AS [No Of Days in Rec Appro], DATEDIFF(day,
dbo.Data.[Hiring Manager Approved], dbo.Data.Interview) AS [No Of Days in HM Approved], DATEDIFF(day, dbo.Data.Interview,
CASE WHEN dbo.Data.[Offer-UKUS] IS NOT NULL THEN dbo.Data.[Offer-UKUS] ELSE dbo.Data.[Offer-MoW] END) AS [No Of Days in Interview],
DATEDIFF(day, (CASE WHEN dbo.Data.[Offer-UKUS] IS NOT NULL THEN dbo.Data.[Offer-UKUS] ELSE dbo.Data.[Offer-MoW] END),
(CASE WHEN dbo.Data.[Verbal Offer Accepted - UK/US] IS NOT NULL
THEN dbo.Data.[Verbal Offer Accepted - UK/US] ELSE dbo.Data.[Verbal Offer Accepted - MoW] END)) AS [No of Days at Offer],
CONCAT(dbo.Data.[Req ID], '-', dbo.Data.[Candidate ref num]) } AS UniqueID
FROM dbo.Data LEFT OUTER JOIN
dbo.HRStatusMap ON dbo.Data.[Current HR status] = dbo.HRStatusMap.MAS |
Edited by - forrestgump on 02/07/2013 05:09:39 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1744 Posts |
Posted - 02/07/2013 : 05:15:46
|
The view and the CONCAT function looks correct, except that you have an extra curly brace, which probably is a typo.CONCAT(dbo.Data.[Req ID], '-', dbo.Data.[Candidate ref num]) } AS UniqueID -- remove the curly brace If you already did not do it this way, can you right-click on the view name in object explorer, script view as -> create to -> New query editor window to generate the script and post that? |
Edited by - James K on 02/07/2013 05:17:27 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48092 Posts |
Posted - 02/07/2013 : 05:39:41
|
i would suggest using aliases to avoid repeating fully qualified names everywhere
SELECT d.[Req ID], d.[Current Req Status], d.[Job title], d.[Geographic location], d.SPU,
d.[Business unit], d.Department, d.[Job category], d.[Level], d.Recruiter,
d.[Hiring Manager], d.[First name], d.[Last name], d.[E-mail], d.[Candidate ref num],
d.[Current HR status], d.[Current HR Status Updated on], d.[Candidate Type], d.[0-Filed],
d.[Met Minimum Requirements], d.[Recruiter Approved], d.[Technical Screen], d.[Hiring Manager Approved],
d.Interview, d.[Offer-UKUS], d.[Offer-MoW], d.[Verbal Offer Accepted - UK/US],
d.[Verbal Offer Accepted - MoW], d.[Verbal Offer Accepted - US/UK - EXTERNAL], d.Hired, hsm.TAS,
hsm.REPORT, hsm.REPORT2, CASE WHEN [Offer-UKUS] IS NOT NULL
THEN [Offer-UKUS] ELSE [Offer-MoW] END AS Offer_Made_Date, CASE WHEN [Verbal Offer Accepted - UK/US] IS NOT NULL
THEN [Verbal Offer Accepted - UK/US] ELSE [Verbal Offer Accepted - MoW] END AS VOF_Accept_Date, DATEDIFF(day,
d.[Met Minimum Requirements], d.[Recruiter Approved]) AS [No Of Days in Met Min], DATEDIFF(day,
d.[Recruiter Approved], d.[Hiring Manager Approved]) AS [No Of Days in Rec Appro], DATEDIFF(day,
d.[Hiring Manager Approved], d.Interview) AS [No Of Days in HM Approved], DATEDIFF(day, d.Interview,
CASE WHEN d.[Offer-UKUS] IS NOT NULL THEN d.[Offer-UKUS] ELSE d.[Offer-MoW] END) AS [No Of Days in Interview],
DATEDIFF(day, (CASE WHEN d.[Offer-UKUS] IS NOT NULL THEN d.[Offer-UKUS] ELSE d.[Offer-MoW] END),
(CASE WHEN d.[Verbal Offer Accepted - UK/US] IS NOT NULL
THEN d.[Verbal Offer Accepted - UK/US] ELSE d.[Verbal Offer Accepted - MoW] END)) AS [No of Days at Offer],
CONCAT(d.[Req ID], '-', d.[Candidate ref num]) } AS UniqueID
FROM dbo.Data d
LEFT OUTER JOIN dbo.HRStatusMap hsm
ON d.[Current HR status] = hsm.MAS
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
forrestgump
Starting Member
18 Posts |
Posted - 02/07/2013 : 05:42:04
|
Hi James K, This is what it would be and then I just added on the final part: CONCAT(dbo.Data.[Req ID], '-', dbo.Data.[Candidate ref num]) AS UniqueID
SELECT dbo.Data.[Req ID], dbo.Data.[Current Req Status], dbo.Data.[Job title], dbo.Data.[Geographic location], dbo.Data.SPU,
dbo.Data.[Business unit], dbo.Data.Department, dbo.Data.[Job category], dbo.Data.[Level], dbo.Data.Recruiter,
dbo.Data.[Hiring Manager], dbo.Data.[First name], dbo.Data.[Last name], dbo.Data.[E-mail], dbo.Data.[Candidate ref num],
dbo.Data.[Current HR status], dbo.Data.[Current HR Status Updated on], dbo.Data.[Candidate Type], dbo.Data.[0-Filed],
dbo.Data.[Met Minimum Requirements], dbo.Data.[Recruiter Approved], dbo.Data.[Technical Screen], dbo.Data.[Hiring Manager Approved],
dbo.Data.Interview, dbo.Data.[Offer-UKUS], dbo.Data.[Offer-MoW], dbo.Data.[Verbal Offer Accepted - UK/US],
dbo.Data.[Verbal Offer Accepted - MoW], dbo.Data.[Verbal Offer Accepted - US/UK - EXTERNAL], dbo.Data.Hired, dbo.HRStatusMap.TAS,
dbo.HRStatusMap.REPORT, dbo.HRStatusMap.REPORT2, CASE WHEN [Offer-UKUS] IS NOT NULL
THEN [Offer-UKUS] ELSE [Offer-MoW] END AS Offer_Made_Date, CASE WHEN [Verbal Offer Accepted - UK/US] IS NOT NULL
THEN [Verbal Offer Accepted - UK/US] ELSE [Verbal Offer Accepted - MoW] END AS VOF_Accept_Date, DATEDIFF(day,
dbo.Data.[Met Minimum Requirements], dbo.Data.[Recruiter Approved]) AS [No Of Days in Met Min], DATEDIFF(day,
dbo.Data.[Recruiter Approved], dbo.Data.[Hiring Manager Approved]) AS [No Of Days in Rec Appro], DATEDIFF(day,
dbo.Data.[Hiring Manager Approved], dbo.Data.Interview) AS [No Of Days in HM Approved], DATEDIFF(day, dbo.Data.Interview,
CASE WHEN dbo.Data.[Offer-UKUS] IS NOT NULL THEN dbo.Data.[Offer-UKUS] ELSE dbo.Data.[Offer-MoW] END) AS [No Of Days in Interview],
DATEDIFF(day, (CASE WHEN dbo.Data.[Offer-UKUS] IS NOT NULL THEN dbo.Data.[Offer-UKUS] ELSE dbo.Data.[Offer-MoW] END),
(CASE WHEN dbo.Data.[Verbal Offer Accepted - UK/US] IS NOT NULL
THEN dbo.Data.[Verbal Offer Accepted - UK/US] ELSE dbo.Data.[Verbal Offer Accepted - MoW] END)) AS [No of Days at Offer]
FROM dbo.Data LEFT OUTER JOIN
dbo.HRStatusMap ON dbo.Data.[Current HR status] = dbo.HRStatusMap.MAS |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48092 Posts |
Posted - 02/07/2013 : 05:45:15
|
are you doing it in view editor or in sql management studio?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
forrestgump
Starting Member
18 Posts |
Posted - 02/07/2013 : 06:04:02
|
| View design in sql server |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48092 Posts |
Posted - 02/07/2013 : 06:10:53
|
ok...I know View designer had few issues in past when you try to use new features of SQL server like PIVOT etc
So I guess this may be a related issue as CONCAT is newly added feature.
Why not use query editor in SSMS to modify the view (using ALTER VIEW statement)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
forrestgump
Starting Member
18 Posts |
Posted - 02/07/2013 : 06:15:12
|
| Thanks visakh16 and James K for your help. It seems you might be right it works fine when I write a select statement to view the query results but does not work when I execute from the design of the view. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48092 Posts |
Posted - 02/07/2013 : 06:16:23
|
Ok..So I've tried this in my 2012 box and can reproduce this issue.
So this seems to be an existing issue with view editor.
I was able to get this working using ALTER VIEW syntax in SSMS though!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
The CONCAT function requires 2 arguements
