JasperServer Domain Design: Derived Table or Calculated Field?

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's picture
Joined: Nov 24 2014 - 7:49am
Last seen: 8 years 2 weeks ago

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.

 

shane_anderson - 8 years 1 month ago

0 Answers:

No answers yet
Feedback
randomness