Click here to Skip to main content
15,938,122 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all, I have this Oracle query and need to add a column ( MyValue ) from another table ( Entitlements ) based on the largest value found in a column called ent_refno , this table has a common key in Claim_Periods called cpe_refno, any ideas how to achieve this ?


Current query

SQL
SELECT distinct clms.CLA_REFNO
  FROM CLAIMS clms
  inner join CLAIM_PARTS cpart
  on clms.CLA_REFNO = cpart.CPA_CLA_REFNO
  inner join CLAIM_PERIODS cperiods
  on (cpart.CPA_CLA_REFNO = cperiods.CPE_CPA_CLA_REFNO
  And cpart.CPA_CPY_CODE = cperiods.CPE_CPA_CPY_CODE)
  WHERE ( FQV_CURR_CL_FLAG(clms.CLA_REFNO) = 'Y' )
  AND ( SYSDATE between cperiods.CPE_START_DATE AND cperiods.CPE_END_DATE)
  AND ( s_claim_periods.latest_tenancy_code(clms.CLA_REFNO) IN ( 'PTEN' )
  AND s_claim_periods.latest_tenure_code(clms.CLA_REFNO)    IN ( 'LHA' )
  AND cpart.CPA_CPY_CODE IN ('HB')
  AND cperiods.CPE_CPS_CODE IN ( 'A','R' ) )



The table Entitlements ( cut down for brevity )

SQL
ent_refno cpe_refno MyValue
1            22      100.99
2            22      100.92 
3            22      400.88
4            22      209.76
5            23      209.76
6            23      212.44
7            24      111.22
8            25      256.33
9            25      222.33


In this example the MyValue for cpe_refno = 22 would be 209.76
In this example the MyValue for cpe_refno = 25 would be 222.33

What I have tried:

Googling , the usual heavy drinking and cursing
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900