CONCAT Help

Question

I need to Con-cat My Last two fields in the select I have never used this command some guidance would be highly appreciated:

select top 2000 p.description 'Program', bp.description 'Plan',ek.carriermemid 'Member ID',m.fullname 'Member Name',et.county 'Member County',c.controlnmb 'Patient Account',cd.claimid,cd.claimline,cd.status 'Status',cast(cd.dosfrom as date) 'Inc From', Cast(cd.dosto as date) 'Inc TO',
cast(c.paiddate as date) 'Paid Date',cd.location,c.facilitycode + c.billclasscode + c.frequencycode 'BillType',cd.revcode'Revenue',cd.servcode'Procedure'
,cd.modcode 'Modifier',cd.servunits 'Quanity',cd.claimamt 'Charged Amount',cd.copay 'Copay',cd.deductible 'deducible',cd.memamt 'Cost Share'
,cd.claimamt -  cd.contractpaid 'Ineligible',cd.ineligibleamt 'Misc Inelig',crm.msgnumber 'RemitID',mes.description 'Remit Message',(cd.amountpaid  + replace (cd.paydiscount,'-','')) 'Paid Amount',cd.ProvMedicareMandatedAdjust 'Sequestration'
,pay.fedid 'Tax Id', pay.fullname 'Payto Name',rend.fullname 'Service Provider',rend.npi 'Servicing NPI',con.description 'Contract Name',ci.contracted 'Contracted?', pt.description 'Provider Type',
spec.description 'Specialty',cpcp.fullname 'PCP',netw.Network,cpcp.PCPCounty,
q.description 'Fund',pc.checknbr,pc.advanceapplied 'Advance Per Check',c.reimbursemember 'Member Reimbursement Flag', CONCAT( Vedit.ruleid, qr.description [, Edit_Code ] )

from claimdetail cd
left join claimremit crm (nolock) on cd.claimid =crm.claimid and cd.claimline = crm.claimline
left join claimexplain ce (nolock) on cd.claimid = ce.claimid
left join claimedit edit (nolock) on cd.claimid =edit.claimid and cd.claimline = edit.claimline and edit.status in ('okay','deny')
left join qrule qr (nolock) on qr.ruleid = edit.ruleid
left join message mes (nolock) on crm.msgnumber = mes.messageid
left join benefitplan bp (nolock) on bp.planid=cd.planid
left join claim c (nolock) on c.claimid = cd.claimid
left join enrollkeys ek (nolock) on ek.enrollid = c.enrollid
left join member m (nolock) on m.memid  = c.memid
left join entity et on m.entityid = et.entid
left join program p (nolock) on p.programid = ek.programid
left join provider rend (nolock) on rend.provid = c.provid
left join providertype pt (nolock) on rend.provtype = pt.provtype
left join provspecialty ps (nolock) on rend.provid = ps.provid and ps.spectype ='PRIMARY' and cast (ps.termdate as date) >= '2078-12-31'
left join specialty spec (nolock) on spec.specialtycode = ps.specialtycode
left join affiliation a (nolock) on a.affiliationid = c.affiliationid
--left join memberpcp mp (nolock) on ek.enrollid = mp.enrollid
--left join affiliation apcp (nolock) on apcp.affiliationid = mp.aWelcomeffiliationid 
left join (select cpcp.claimid,pcp2.fullname,pcp2.provid,cast (apcp.effdate as date)'pcpeffdte',etp.county 'PCPCounty'
                        from claim cpcp (nolock) 
                        left join enrollkeys ekpcp (nolock) on ekpcp.enrollid = cpcp.enrollid
                        left join memberpcp mp (nolock) on ekpcp.enrollid = mp.enrollid 
                        left join affiliation apcp (nolock) on apcp.affiliationid = mp.affiliationid 
                        left join provider pcp2 (nolock) on apcp.provid = pcp2.provid      
                        left join entity etp on pcp2.entityid = etp.entid                 
                        where CAST(cpcp.startdate as date) between cast(mp.effdate as date) and cast(mp.termdate as date))
                        cpcp on cpcp.claimid = c.claimid
                  
--left join provider pcp (nolock) on apcp.provid = pcp.provid and   CAST(c.startdate as date) between cast(mp.effdate as date) and cast(mp.termdate as date) 
left join (select netw.provid ,network.fullname 'Network',p.fullname'PCP'
              from affiliation netw (nolock)
              left join affiliation apcp (nolock) on apcp.affiliationid = netw.affiliationid 
              left join provider network (nolock) on netw.affiliateid = network.provid
              left join provider p (nolock) on netw.provid = p.provid
              where netw.affiltype ='NETWORK' and cast (apcp.effdate as date)  between cast(netw.effdate as date) and cast(netw.termdate as date)) netw on cpcp.provid = netw.provid 
--left join provider network (nolock) on netw.affiliateid = network.provid
left join provider pay (nolock) on a.affiliateid = pay.provid
left join qfund q (nolock) on cd.fundid = q.fundid
left join payvoucher pv (nolock) on pv.claimid = c.claimid
left join paycheck pc (nolock) on pc.paymentid = pv.paymentid and cd.fundid = pc.fundid
left join contractinfo ci (nolock) on ci.affiliationid=a.affiliationid  and ek.programid = ci.programid and CAST (c.startdate as date) between cast(ci.effdate as date) and cast(ci.termdate as date)
left join contract con (nolock) on ci.contractid = con.contractid
Where cast(c.paiddate as date) between '01-01-2016' and '02-29-2016' 
--pay.fedid = '710892430' and c.paiddate is not null
order by claimid,claimline

Answer 1

What is your question? The code does not work?:

, CONCAT( Vedit.ruleid, ':', qr.description  ) as combinedColumn

Answer 2

Hi Stewart,

You need to covert ruleid from integer to string. See my SQL code Below:

Declare @RuleId Int = 1 , @Description Varchar(10) = 'Description ' ,@EditCode Varchar(10) = 'Edit'
SELECT CONCAT(Cast(@RuleId as varchar(10)) , @Description , @EditCode)

Regards,

Balwant.

---

Below are the Remarks from MSDN:

---

CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions. For more information about data type conversions, see CAST and CONVERT (Transact-SQL).

Failure in Life is failure to try...

PGDCA-98(South Gujarat University),MCSA (SQL 2012) , MCTS (SQL Server 2005)

My Blog

Answer 3

The multi-part identifier "con.description" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "ci.contracted" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "pt.description" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "spec.description" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "cpcp.fullname" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "netw.Network" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "cpcp.PCPCounty" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "q.description" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "pc.checknbr" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "pc.advanceapplied" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "c.reimbursemember" could not be bound.
Msg 209, Level 16, State 1, Line 56
Ambiguous column name 'claimid'.
Msg 209, Level 16, State 1, Line 56
Ambiguous column name 'claimline'.

Hi Stewart,

You need to covert ruleid from integer to string. See my SQL code Below:

Declare @RuleId Int = 1 , @Description Varchar(10) = 'Description ' ,@EditCode Varchar(10) = 'Edit'
SELECT CONCAT(Cast(@RuleId as varchar(10)) , @Description , @EditCode)

Regards,

Balwant.

---

Failure in Life is failure to try...

PGDCA-98(South Gujarat University),MCSA (SQL 2012) , MCTS (SQL Server 2005)

My Blog

Answer 4

What is your question? The code does not work?:

, CONCAT( Vedit.ruleid, ':', qr.description  ) as combinedColumn

Correct this is not working. It was showing the error listed below:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ', 'Edit Code' '.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near 'cpcp'.
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near 'netw'.

Answer 5

You don't need to change int data type with concat ( The function will convert it implicitly):

Declare @RuleId Int = 1 , @Description Varchar(10) = 'Description ' ,@EditCode Varchar(10) = 'Edit'
SELECT CONCAT(Cast(@RuleId as varchar(10)) , @Description , @EditCode)
,CONCAT( @RuleId  , @Description , @EditCode)

Answer 6

You need:

1. Give all columns in your query an alias (e.g. precede with that table's alias)

2. Change your last expression to be

CONCAT( Vedit.ruleid, qr.description , aliasHere.Edit_Code  ) as CombinedColumn

 I also suggest to get rid of '' in the new column names and if you need a space, use [] for the identifiers.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 7

Try to use only these two columns (or without the concat for your query):

select  CONCAT( Vedit.ruleid,' --something here if you want: --', qr.description ) as combinedCol

from claimdetail cd

--....

Answer 8

Hi Steve,

In Order by Clause put tablename/aliasname .columnname to remove below two errors.

Msg 209, Level 16, State 1, Line 56
Ambiguous column name 'claimid'.
Msg 209, Level 16, State 1, Line 56
Ambiguous column name 'claimline'.

order by claimid,claimline

Regards,

Balwant.

---

Failure in Life is failure to try...

PGDCA-98(South Gujarat University),MCSA (SQL 2012) , MCTS (SQL Server 2005)

My Blog

Answer 9

Thanks Jingyang For the correction.

---

Failure in Life is failure to try...

PGDCA-98(South Gujarat University),MCSA (SQL 2012) , MCTS (SQL Server 2005)

My Blog

Answer 10

Hi StewartDFW,

You may take a good look at Transact-SQL Syntax Conventions. Square brackets in CONCAT ( string_value1, string_value2 [, string_valueN ] ) means optional syntax items. It's no need to type the brackets here.

---

Sam Zha
TechNet Community Support