sql - Crystal Report Select Only First Row -

sql - Crystal Report Select Only First Row -

let have next 2 tables:

table a:

companycode locationcode formnumber employeenumber ...

table b:

companycode locationcode formnumber employeenumber claimdate ...

table , table b joined using 4 parameters (companycode,locationcode,formnumber,employeenumber). these 4 parameters input report. 1 row in table can have multiple rows in table b.

in crystal report, each record in table a, want row earliest claimdate in table b.

using sql, can accomplished using:

select * tablea inner bring together tableb b on a.companycode = b.companycode , a.locationcode = b.locationcode , a.formnumber = b.formnumber , a.employeenumber = b.employeenumber a.companycode = @companycode , a.locationcode = @locationcode , a.formnumber = @formnumber , a.employeenumber = @employeenumber , b.claimdate in (select min(b.claimdate) tableb b b.companycode = @companycode , b.locationcode = @locationcode , b.formnumber = @formnumber , b.employeenumber = @employeenumber )

but i'm not sure how can in crystal report. tried using sql expression, seems sql look cannot pass study input.

you need create 4 parameters (companycode, locationcode , formnumber, employeenumber) in command.

the command's query:

select * tablea inner bring together tableb b on a.companycode = b.companycode , a.locationcode = b.locationcode , a.formnumber = b.formnumber , a.employeenumber = b.employeenumber a.companycode = {?companycode} , a.locationcode = {?locationcode} , a.formnumber = {?formnumber} , a.employeenumber = {?employeenumber} , b.claimdate in (select min(b.claimdate) tableb b b.companycode = a.companycode , b.locationcode = a.locationcode , b.formnumber = a.formnumber , b.employeenumber = a.employeenumber )

sql crystal-reports