Concatenate fields and join tables using iReports
<p>I'm trying to concatenate two fields in one table and another two fields in another table and using the concatenated value as the primary key to join the tables together. </p>

<p>Due to the fact that the primary key is linked to example 5 different people i need a unique value per person concatenating a policy number with a dep code.</p>

<p>The following is an example of how to run the query with MSSQL (I did not include the full code - just an example). </p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>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]</code></pre>
</div>
</div>
</p>

<p>I need to include the above in iReports as the final left join and I dont know how to create additional field in iReports. Also note that my iReports is a very old version (3.7.1)</p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>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,

FROM mipst_dbo.tsf_claim as 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

WHERE    t1.scheme_fk = '75'
 
GROUP BY 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</code></pre>
</div>
</div>
</p>

<p>By adding the left join 'LEFT JOIN mipbi_dbo.td_beneficiary  t5 ON t1.ms_fk      = t5.ms_pk' it is not adding the persons name according to the dependant code. It is merely just joining the lines. </p>

<p>In short a policy number is linked to different people with a dependent code, so when joining the information dep 1 should be linked to dep 1 from the second table with the correct name and surname etc.</p>

<p>In table 1 dep 1 can have say 10 lines, so with the current code it is adding all the information for the policy number on the 10 lines and not just the information for dep 1.</p>

<p>Let me know if the above is clearly defined or if you need more information. </p>