SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 The CONCAT function requires 2 arguements
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

forrestgump
Starting Member

19 Posts

Posted - 02/07/2013 :  04:33:30  Show Profile  Reply with Quote
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
52325 Posts

Posted - 02/07/2013 :  04:49:01  Show Profile  Reply with Quote
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/

Go to Top of Page

forrestgump
Starting Member

19 Posts

Posted - 02/07/2013 :  05:08:02  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/07/2013 :  05:15:46  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/07/2013 :  05:39:41  Show Profile  Reply with Quote
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/

Go to Top of Page

forrestgump
Starting Member

19 Posts

Posted - 02/07/2013 :  05:42:04  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/07/2013 :  05:45:15  Show Profile  Reply with Quote
are you doing it in view editor or in sql management studio?

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

Go to Top of Page

forrestgump
Starting Member

19 Posts

Posted - 02/07/2013 :  06:04:02  Show Profile  Reply with Quote
View design in sql server
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/07/2013 :  06:10:53  Show Profile  Reply with Quote
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/

Go to Top of Page

forrestgump
Starting Member

19 Posts

Posted - 02/07/2013 :  06:15:12  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/07/2013 :  06:16:23  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000