sql server - Concatenate fields and join tables using iReports -

i'm trying concatenate 2 fields in 1 table , 2 fields in table , using concatenated value primary key join tables together.

due fact primary key linked example 5 different people need unique value per person concatenating policy number dep code.

the following example of how run query mssql (i did not include full code - example).

alter table [beneficiary] add [unique] varchar(max)  update [beneficiary] set [unique] =concat([t1.ms_fk],[t1.dep_fk])    alter table [tsf_claim] add [unique] varchar(max)  update [tsf_claim] set [unique1] =concat([t5.ms_fk],[t5.dep_fk])    left join [mipbi_dbo.td_beneficiary]  t5 on t1.[unique]  = t5.[unique1]

i need include above in ireports final left join , dont know how create additional field in ireports. note ireports old version (3.7.1)

select t1.scheme_fk,t1.ms_fk,t1.dep_fk,t1.pr_fk,t1.tariff_fk,t1.icd10_fk,t1.claimed_amount,t1.benefit_amount,t1.auth_fk,t1.units,t1.paid_date,         t1.claim_date,t1.claim_code,t1.ref_pr_fk,t1.note,t1.cheque_datetime,t1.suspend_until,t1.dr_mem,t1.suspended,         t2.mem_num,t2.xref_num,         t3.icd10_pk,t3.icd10_descr,         t4.claim_code,t4.description,         t5.initials,t5.surname,    mipst_dbo.tsf_claim t1    left join mipst_dbo.tsf_memxref     t2 on t1. ms_fk 	= t2.mem_num  left join mipbi_dbo.td_icd10        t3 on t1.icd10_fk 	= t3.icd10_pk  left join mipst_dbo.tsd_ccdesc	    t4 on t1.claim_code = t4.claim_code::integer  left join mipbi_dbo.td_beneficiary  t5 on t1.ms_fk      = t5.ms_pk       t1.scheme_fk = '75'  	  group t1.scheme_fk,t1.ms_fk,t1.dep_fk,t1.pr_fk,t1.tariff_fk,t1.icd10_fk,t1.claimed_amount,t1.benefit_amount,t1.auth_fk,t1.units,t1.paid_date,         t1.claim_date,t1.claim_code,t1.ref_pr_fk,t1.note,t1.cheque_datetime,t1.suspend_until,t1.dr_mem,t1.suspended,         t2.mem_num,t2.xref_num,         t3.icd10_pk,t3.icd10_descr,         t4.claim_code,t4.description,         t5.initials,t5.surname

by adding left join 'left join mipbi_dbo.td_beneficiary t5 on t1.ms_fk = t5.ms_pk' not adding persons name according dependant code. merely joining lines.

in short policy number linked different people dependent code, when joining information dep 1 should linked dep 1 second table correct name , surname etc.

in table 1 dep 1 can have 10 lines, current code adding information policy number on 10 lines , not information dep 1.

let me know if above defined or if need more information.

i able resolve query merely

select t1.scheme_fk,t1.ms_fk,t1.dep_fk,t1.pr_fk,t1.tariff_fk,t1.icd10_fk,t1.claimed_amount,t1.benefit_amount,t1.auth_fk,t1.units,t1.paid_date,         t1.claim_date,t1.claim_code,t1.ref_pr_fk,t1.note,t1.cheque_datetime,t1.suspend_until,t1.dr_mem,t1.suspended,         t2.mem_num,t2.xref_num,         t3.icd10_pk,t3.icd10_descr,         t4.claim_code,t4.description,t4.scheme_code,         t5.ms_pk,t5.dep_fk,t5.initials,t5.surname,         t6.narration,t6.key    mipst_dbo.tsf_claim t1    left join mipst_dbo.tsf_memxref     t2 on t1. ms_fk 	= t2.mem_num  left join mipbi_dbo.td_icd10        t3 on t1.icd10_fk 	= t3.icd10_pk  left join mipst_dbo.tsd_ccdesc	    t4 on t1.claim_code = t4.claim_code::integer  left join mipbi_dbo.td_beneficiary  t5 on t1.ms_fk      = t5.ms_pk   left join mipst_dbo.tsf_note	    t6 on t1.note       = t6.key       t1.scheme_fk = '28' ,  	     t4.scheme_code = '28' ,           t1.ms_fk = t5.ms_pk ,            t1.dep_fk =t5.dep_fk        	  group t1.scheme_fk,t1.ms_fk,t1.dep_fk,t1.pr_fk,t1.tariff_fk,t1.icd10_fk,t1.claimed_amount,t1.benefit_amount,t1.auth_fk,t1.units,t1.paid_date,         t1.claim_date,t1.claim_code,t1.ref_pr_fk,t1.note,t1.cheque_datetime,t1.suspend_until,t1.dr_mem,t1.suspended,         t2.mem_num,t2.xref_num,         t3.icd10_pk,t3.icd10_descr,         t4.claim_code,t4.description,t4.scheme_code,         t5.ms_pk,t5.dep_fk,t5.initials,t5.surname,         t6.narration,t6.key

updating clause.