problem modifying a big UNION - 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

General SQL Server Forums

New to SQL Server Programming

problem modifying a big UNION

Author

Topic

MrTexasFreedom
Starting Member

13 Posts

Posted - 2010-10-26 : 20:55:52

I'm trying to take an existing script and add a new select element so that the end result gets the little nugget of info I'm interested in along with all the other junk that comes out of this thing. I've tried to add my select along with an outer join where my table alias is defined, but I get this error:

java.sql.SQLException: The multi-part identifier "revc.description" could not be bound.

revc.description is the thing I added and it's what I care about.

On both sides of the union, I added this outer join as well:

+"left outer join p3_billing_revcode revc on (serv.nubc_revcode=revc.nubc_revcode) "

Any thoughts? Here's the beast:


String x =
"select distinct d.carrier_plan_name,  d.plan_name,  d.plan_id,"
+"d.carrier_id,  d.financial_class,  d.service_id, d.line_number,"
+"d.claim_number, d.code_type, d.procedure_code, d.modifier,"
+"d.addmodifier, d.start_date, d.end_date, d.procedure_desc,   d.fintxid, revc.description,"
+"d.type, d.quantity, d.unit_cost, d.posting_date,  d.amount,"
+"d.payer_code_type, d.payer_code, d.claimRef,"
+"d.visitclaim_id,  d.denial_ID, d.visit_number,d.fClaim_ref,d.nubc_revcode,"
+"d.orig_qty, d.apg_code, d.remark_codetype, d.remark_code, d.reimb_amount, d.claim_id,"
+"d.denial_group_code, d.denial_code, d.adjamount, d.adjquantity, d.proc_description,"
+"d.denial_desc, d.denialgroup_desc, d.DV_code_group, d.denResolved,  d.denRecovered "
+"from"
+"(select ip.plan_long_name as 'carrier_plan_name',  ip.plan_short_name as 'plan_name',  ip.id as 'plan_id',"
+"ip.carrier_id as 'carrier_id',  ip.insurance_type as 'financial_class',"
+"serv.id as 'service_id', isnull(serv.linenum,'0') as 'line_number',"
+"isnull(serv.claim_number, '1') as 'claim_number', serv.code_type as 'code_type',"
+"serv.procedure_code as 'procedure_code', serv.modifier as 'modifier',"
+"serv.modifier_additional as 'addmodifier', serv.start_date as 'start_date', "
+"serv.end_date as 'end_date', isnull(serv.transaction_desc,"
+"isnull(bc.description,'')) as 'procedure_desc',   fin.id as 'fintxid', fin.type as 'type', "
+"isnull(fin.quantity,0) as 'quantity', isnull(fin.unit_cost,0) as 'unit_cost', "
+"isnull(fin.transaction_posting_date,den.denial_date) as 'posting_date',  fin.amount as 'amount', "
+"fin.orig_code_type as 'payer_code_type', fin.orig_proc_code as 'payer_code', "
+"case fin.type when 'CG' then '' else isnull(cl.claim_ref,'') end as 'claimRef', "
+"cl.id as 'visitclaim_id',  den.id as 'denial_ID', cl.visit_number as 'visit_number', "
+"'zzz'+CONVERT(VARCHAR(15),cl.id ) as 'fClaim_ref',    serv.nubc_revcode as 'nubc_revcode', "
+"fin.orig_qty as 'orig_qty', fin.apg_code as 'apg_code', fin.remark_codetype as 'remark_codetype', "
+"fin.remark_code as 'remark_code', fin.reimb_amount as 'reimb_amount', fin.visitclaim_id as 'claim_id', "
+"den.denial_group_code as 'denial_group_code', den.denial_code as 'denial_code', den.amount as 'adjamount', "
+"den.quantity as 'adjquantity', bc.description  as 'proc_description', dc.code_desc  as 'denial_desc', "
+"dg.code_desc as 'denialgroup_desc', dg.DV_code_group  as 'DV_code_group', "
+"isnull(den.resolved,0) as 'denResolved',  isnull(den.recovered,0) as 'denRecovered' "
+"from p3_visitService serv "
+"left outer join p3_visitFin fin ON (fin.visitservice_id = serv.id) "
+"left outer join P3_visitDenial den ON ( den.fintrans_id = fin.id ) "
+"left outer join P3_visitClaim cl ON (  cl.id = fin.visitClaim_id ) "
+"left outer join P3_insurance_plans ip ON (ip.id = fin.insurance_id) "
+"left outer join    P3_billing_codes_CPT bc ON (bc.code = serv.procedure_code) "
+"left outer join     p3_billing_revcode revc on (serv.nubc_revcode=revc.nubc_revcode) "
+"left outer join    P3_denialCodes dc ON (dc.code = den.denial_code) "
+"left outer join    P3_denialGroupCodes dg ON (dg.code_group = den.denial_group_code) "
+"where serv.visitclaim_id ="+bclaimID
+"union "
+"select ip.plan_long_name as 'carrier_plan_name',  ip.plan_short_name as 'plan_name',  ip.id as 'plan_id',"
+"ip.carrier_id as 'carrier_id',  ip.insurance_type as 'financial_class',  "
+"serv.id as 'service_id', isnull(serv.linenum,'0') as 'line_number', "
+"isnull(serv.claim_number, '1') as 'claim_number', serv.code_type as 'code_type', "
+"serv.procedure_code as 'procedure_code', serv.modifier as 'modifier', "
+"serv.modifier_additional as 'addmodifier', serv.start_date as 'start_date', "
+"serv.end_date as 'end_date', isnull(serv.transaction_desc, "
+"isnull(bc.description,'')) as 'procedure_desc',   fin.id as 'fintxid', fin.type as 'type', "
+"isnull(fin.quantity,0) as 'quantity', isnull(fin.unit_cost,0) as 'unit_cost', "
+"isnull(fin.transaction_posting_date,den.denial_date) as 'posting_date',  fin.amount as 'amount', "
+"fin.orig_code_type as 'payer_code_type', fin.orig_proc_code as 'payer_code', "
+"case fin.type when 'CG' then '' else isnull(cl.claim_ref,'') end as 'claimRef', "
+"cl.id as 'visitclaim_id',  den.id as 'denial_ID', cl.visit_number as 'visit_number', "
+"'zzz'+CONVERT(VARCHAR(15),cl.id ) as 'fClaim_ref',    serv.nubc_revcode as 'nubc_revcode', "
//+"case fin.type when 'CG' then '-1' else 'zzz'+CONVERT(VARCHAR(15),cl.id ) end as 'fClaim_ref',    serv.nubc_revcode as 'nubc_revcode', "
+"fin.orig_qty as 'orig_qty', fin.apg_code as 'apg_code', fin.remark_codetype as 'remark_codetype', "
+"fin.remark_code as 'remark_code', fin.reimb_amount as 'reimb_amount', fin.visitclaim_id as 'claim_id', "
+"den.denial_group_code as 'denial_group_code', den.denial_code as 'denial_code', den.amount as 'adjamount', "
+"den.quantity as 'adjquantity', bc.description  as 'proc_description', dc.code_desc  as 'denial_desc', "
+"dg.code_desc as 'denialgroup_desc', dg.DV_code_group  as 'DV_code_group', "
+"isnull(den.resolved,0) as 'denResolved',  isnull(den.recovered,0) as 'denRecovered' "
+"from p3_visitService serv  "
+"left outer join p3_visitFin fin ON (fin.visitservice_id = serv.id) "
+"left outer join P3_visitDenial den ON ( den.fintrans_id = fin.id ) "
+"left outer join P3_visitClaim cl ON (  cl.bclaim_id = fin.visitClaim_id   ) "
+"left outer join P3_insurance_plans ip ON (ip.id = fin.insurance_id) "
+"left outer join    P3_billing_codes_CPT bc ON (bc.code = serv.procedure_code) "
+"left outer join     p3_billing_revcode revc on (serv.nubc_revcode=revc.nubc_revcode) "
+"left outer join    P3_denialCodes dc ON (dc.code = den.denial_code) "
+"left outer join    P3_denialGroupCodes dg ON (dg.code_group = den.denial_group_code) "
+"where serv.visitclaim_id ="+ bclaimID +"and isnull(fin.type,'') = 'CG'"
+") as d "
+"order by d.line_number, d.start_date, d.posting_date " ;

Thanks in advance for any advice you can provide!

MrTexasFreedom

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-26 : 23:12:48

Can you try the below statement and tell us what it returns:

Select top 1 description from p3_billing_revcode

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

MrTexasFreedom
Starting Member

13 Posts

Posted - 2010-10-27 : 02:11:23

quote:
Originally posted by pk_bohra

Can you try the below statement and tell us what it returns:

Select top 1 description from p3_billing_revcode

I have a strong guess what it will return, but I won't be able to run it until I get to work. Will post back asap. Thanks!!

mrtexasfreedom

MrTexasFreedom
Starting Member

13 Posts

Posted - 2010-10-27 : 10:50:10

quote:
Originally posted by pk_bohra

Can you try the below statement and tell us what it returns:

Select top 1 description from p3_billing_revcode

I just ran the following query:

quote:
select top 1 description from p3_billing_revcode

and the result returned was:

quote:
General Room and Board-Private

Any other ideas?

appreciatively,

MrTexasFreedom

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-27 : 11:01:29

[code]String x =
"select distinct d.carrier_plan_name, d.plan_name, d.plan_id,"
+"d.carrier_id, d.financial_class, d.service_id, d.line_number,"
+"d.claim_number, d.code_type, d.procedure_code, d.modifier,"
+"d.addmodifier, d.start_date, d.end_date, d.procedure_desc, d.fintxid, d.revc_description,"
+"d.type, d.quantity, d.unit_cost, d.posting_date, d.amount,"
+"d.payer_code_type, d.payer_code, d.claimRef,"
+"d.visitclaim_id, d.denial_ID, d.visit_number,d.fClaim_ref,d.nubc_revcode,"
+"d.orig_qty, d.apg_code, d.remark_codetype, d.remark_code, d.reimb_amount, d.claim_id,"
+"d.denial_group_code, d.denial_code, d.adjamount, d.adjquantity, d.proc_description,"
+"d.denial_desc, d.denialgroup_desc, d.DV_code_group, d.denResolved, d.denRecovered "
+"from"
+"(select ip.plan_long_name as 'carrier_plan_name', ip.plan_short_name as 'plan_name', ip.id as 'plan_id',"
+"ip.carrier_id as 'carrier_id', ip.insurance_type as 'financial_class',"
+"serv.id as 'service_id', isnull(serv.linenum,'0') as 'line_number',"
+"isnull(serv.claim_number, '1') as 'claim_number', serv.code_type as 'code_type',"
+"serv.procedure_code as 'procedure_code', serv.modifier as 'modifier',"
+"serv.modifier_additional as 'addmodifier', serv.start_date as 'start_date', "
+"serv.end_date as 'end_date', isnull(serv.transaction_desc,"
+"isnull(bc.description,'')) as 'procedure_desc', fin.id as 'fintxid', fin.type as 'type', "
+"isnull(fin.quantity,0) as 'quantity', isnull(fin.unit_cost,0) as 'unit_cost', "
+"isnull(fin.transaction_posting_date,den.denial_date) as 'posting_date', fin.amount as 'amount', "
+"fin.orig_code_type as 'payer_code_type', fin.orig_proc_code as 'payer_code', "
+"case fin.type when 'CG' then '' else isnull(cl.claim_ref,'') end as 'claimRef', "
+"cl.id as 'visitclaim_id', den.id as 'denial_ID', cl.visit_number as 'visit_number', "
+"'zzz'+CONVERT(VARCHAR(15),cl.id ) as 'fClaim_ref', serv.nubc_revcode as 'nubc_revcode', "
+"fin.orig_qty as 'orig_qty', fin.apg_code as 'apg_code', fin.remark_codetype as 'remark_codetype', "
+"fin.remark_code as 'remark_code', fin.reimb_amount as 'reimb_amount', fin.visitclaim_id as 'claim_id', "
+"den.denial_group_code as 'denial_group_code', den.denial_code as 'denial_code', den.amount as 'adjamount', "
+"den.quantity as 'adjquantity', bc.description as 'proc_description', dc.code_desc as 'denial_desc', "
+"dg.code_desc as 'denialgroup_desc', dg.DV_code_group as 'DV_code_group', "
+"isnull(den.resolved,0) as 'denResolved', isnull(den.recovered,0) as 'denRecovered',revc.description as 'revc_description' "
+"from p3_visitService serv "
+"left outer join p3_visitFin fin ON (fin.visitservice_id = serv.id) "
+"left outer join P3_visitDenial den ON ( den.fintrans_id = fin.id ) "
+"left outer join P3_visitClaim cl ON ( cl.id = fin.visitClaim_id ) "
+"left outer join P3_insurance_plans ip ON (ip.id = fin.insurance_id) "
+"left outer join P3_billing_codes_CPT bc ON (bc.code = serv.procedure_code) "
+"left outer join p3_billing_revcode revc on (serv.nubc_revcode=revc.nubc_revcode) "
+"left outer join P3_denialCodes dc ON (dc.code = den.denial_code) "
+"left outer join P3_denialGroupCodes dg ON (dg.code_group = den.denial_group_code) "
+"where serv.visitclaim_id ="+bclaimID
+"union "
+"select ip.plan_long_name as 'carrier_plan_name', ip.plan_short_name as 'plan_name', ip.id as 'plan_id',"
+"ip.carrier_id as 'carrier_id', ip.insurance_type as 'financial_class', "
+"serv.id as 'service_id', isnull(serv.linenum,'0') as 'line_number', "
+"isnull(serv.claim_number, '1') as 'claim_number', serv.code_type as 'code_type', "
+"serv.procedure_code as 'procedure_code', serv.modifier as 'modifier', "
+"serv.modifier_additional as 'addmodifier', serv.start_date as 'start_date', "
+"serv.end_date as 'end_date', isnull(serv.transaction_desc, "
+"isnull(bc.description,'')) as 'procedure_desc', fin.id as 'fintxid', fin.type as 'type', "
+"isnull(fin.quantity,0) as 'quantity', isnull(fin.unit_cost,0) as 'unit_cost', "
+"isnull(fin.transaction_posting_date,den.denial_date) as 'posting_date', fin.amount as 'amount', "
+"fin.orig_code_type as 'payer_code_type', fin.orig_proc_code as 'payer_code', "
+"case fin.type when 'CG' then '' else isnull(cl.claim_ref,'') end as 'claimRef', "
+"cl.id as 'visitclaim_id', den.id as 'denial_ID', cl.visit_number as 'visit_number', "
+"'zzz'+CONVERT(VARCHAR(15),cl.id ) as 'fClaim_ref', serv.nubc_revcode as 'nubc_revcode', "
//+"case fin.type when 'CG' then '-1' else 'zzz'+CONVERT(VARCHAR(15),cl.id ) end as 'fClaim_ref', serv.nubc_revcode as 'nubc_revcode', "
+"fin.orig_qty as 'orig_qty', fin.apg_code as 'apg_code', fin.remark_codetype as 'remark_codetype', "
+"fin.remark_code as 'remark_code', fin.reimb_amount as 'reimb_amount', fin.visitclaim_id as 'claim_id', "
+"den.denial_group_code as 'denial_group_code', den.denial_code as 'denial_code', den.amount as 'adjamount', "
+"den.quantity as 'adjquantity', bc.description as 'proc_description', dc.code_desc as 'denial_desc', "
+"dg.code_desc as 'denialgroup_desc', dg.DV_code_group as 'DV_code_group', "
+"isnull(den.resolved,0) as 'denResolved', isnull(den.recovered,0) as 'denRecovered',revc.description as 'revc_description' "
+"from p3_visitService serv "
+"left outer join p3_visitFin fin ON (fin.visitservice_id = serv.id) "
+"left outer join P3_visitDenial den ON ( den.fintrans_id = fin.id ) "
+"left outer join P3_visitClaim cl ON ( cl.bclaim_id = fin.visitClaim_id ) "
+"left outer join P3_insurance_plans ip ON (ip.id = fin.insurance_id) "
+"left outer join P3_billing_codes_CPT bc ON (bc.code = serv.procedure_code) "
+"left outer join p3_billing_revcode revc on (serv.nubc_revcode=revc.nubc_revcode) "
+"left outer join P3_denialCodes dc ON (dc.code = den.denial_code) "
+"left outer join P3_denialGroupCodes dg ON (dg.code_group = den.denial_group_code) "
+"where serv.visitclaim_id ="+ bclaimID +"and isnull(fin.type,'') = 'CG'"
+") as d "
+"order by d.line_number, d.start_date, d.posting_date " ;

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-27 : 11:07:50

You are using a dervied table (d).. Replace: revc.description with d.description

Bumma too slow.. :(

MrTexasFreedom
Starting Member

13 Posts

Posted - 2010-10-27 : 13:34:10

Thanks for the suggestions. I'll modify the script and let you know how it turns out.

Appreciatively,

MrTexasFreedom

MrTexasFreedom
Starting Member

13 Posts

Posted - 2010-10-27 : 15:47:42