JasperServer Domain Design: Derived Table or Calculated Field?

[shane_anderson]

Please excuse me if this is a simple question but I am brand new to this product and have looked for a while and can't seem to find an answer.  I am attempting to design a domain on JS that is connected to an Oracle database and am having issues.

Consider the case where I have three tables as follows:

Dates_Table
JDate	GDate	StringDate
115001	01/01/2015	January 1, 2015
115002	01/02/2015	January 2, 2015
115003	01/03/2015	January 3, 2015
115004	01/04/2015	January 4, 2015
115005	01/05/2015	January 5, 2015
…	…	…

Price_Heading_Table
PriceID	PriceDesc	CatCode1
100	Price Description 1	AAA
200	Price Description 2	AAA
300	Price Description 3	BBB
400	Price Description 4	BBB
500	Price Description 5	CCC
…	…	…

Price_Detail_Table
Price_ID	Effective Date	Amount
100	01/01/2015	1.25
200	01/01/2015	2.50
200	01/03/2015	2.75
300	01/01/2015	1.15
400	01/02/2015	1.75
…	…	…

 

I wish to create a domain that will allow the creation of an ad-hoc view to return of the following information when a user enters a paramater containing a date value of January 3:

Results for user entering a date of January 3
PriceID	PriceDesc	Gdate	Amount
100	Price Description 1	01/03/2015	1.25
200	Price Description 2	01/03/2015	2.75
300	Price Description 3	01/03/2015	1.15
400	Price Description 4	01/03/2015	1.75
500	Price Description 5	01/03/2015

My Issue is being able to return only the current effective price for each Price_ID by date.  I can do this using SQL by returning a single row from the Price_Detail_Table when joining by Price_ID where Effectie_Date >= GDate and sorting decending by effective date but I have no idea how to accomplish this in the Domain designer.  I have reviewed the DomEL information (for as much as I am able to find) but have had no luck.

Any idea's would be greatly appreciated!

[shane_anderson]

I'm not sure if there is a more elegant solution and I would certainly be interested in hearing from anyone who miight have one but I ended up creating a stored procedure to retreive the values I need based on input parameters of PriceID and Date.  I am then able to execute the stored procedure using DomEL in a calculated field.