April 7, 2016 at 8:56 am
Never Used this Command before need to Concat my last two Fields in my select and it is giving me an error.
If removed the query runs with out issue? CONCAT( edit.ruleid, qr.description [, 'Edit Code' ] )
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'.
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( edit.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
April 7, 2016 at 9:05 am
CONCAT( edit.ruleid, qr.description , 'Edit Code' )
You know about the perils of the (NOLOCK) hint, right?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2016 at 9:27 am
Seems to me like you got tripped up because you copy and pasted the code from online documentation. For future reference keep in mind why the square brackets are there. CONCAT at minimum requires two input values where the 3rd (4th, 5th, Nth) was optional. If you're not too sure about syntax or how it's used, it's always a good idea to scroll down to the examples section. https://msdn.microsoft.com/en-ca/library/hh231515.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply