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.
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.
The following is an example of how to run the query with MSSQL (I did not include the full code - just an 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 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)
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.surnameBy 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.
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.
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.
Let me know if the above is clearly defined or if you need more information.
I was able to resolve the query by 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
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
LEFT JOIN mipst_dbo.tsf_note t6 ON t1.note = t6.key
WHERE t1.scheme_fk = '28' and
t4.scheme_code = '28' and
t1.ms_fk = t5.ms_pk AND
t1.dep_fk =t5.dep_fk
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,t4.scheme_code,
t5.ms_pk,t5.dep_fk,t5.initials,t5.surname,
t6.narration,t6.keyupdating the where clause.