Get row from table for last date before date parameter if no row for date parameter or entries null

I have table, which holds the exchange rates for currencies. There is also a Date column, which holds the currency exchange rates acquisition date. For some dates there is a row, but no currency exchange rates and for some dates there is no row in that table. To get the currency exchange rates for a specific date, I need to pass the date as parameter. SELECT documentHead.r_art AS documentHeadDocClass, foreignCurrency.usd_brief AS foreignCurrencyUSDAsk, foreignCurrency.usd_geld AS foreignCurrencyUSDBid, foreignCurrency.chf_brief AS foreignCurrencyCHFAsk, foreignCurrency.chf_geld AS foreignCurrencyCHFBid, foreignCurrency.gbp_brief AS foreignCurrencyGBPAsk, foreignCurrency.gbp_geld AS foreignCurrencyGBPBid, foreignCurrency.jpy_brief AS foreignCurrencyJPYAsk, foreignCurrency.jpy_geld AS foreignCurrencyJPYBid, documentListPeriods.id AS documentPeriodsID, documentListPeriods.von AS documentPeriodsFrom, documentListPeriods.bis AS documentPeriodsTo, documentListPeriods.tage AS documentPeriodsDays, documentListPeriods.rate AS documentPeriodsRate, documentListPeriods.wert AS documentPeriodsWorth FROM ( SELECT documentPeriods.id AS documentPeriodsID, documentPeriods.von AS documentPeriodsFrom, documentPeriods.bis AS documentPeriodsTo, documentPeriods.tage AS documentPeriodsDays, documentPeriods.rate AS documentPeriodsRate, documentPeriods.wert AS documentPeriodsWorth FROM comperiode AS documentPeriods WHERE documentPeriods.id = 1 ) AS documentListPeriods INNER JOIN tckopf AS documentHead ON documentHead.id = 1 LEFT OUTER JOIN devisen AS foreignCurrency ON foreignCurrency.datum = documentHead.datum How can I get a currency exchange rates table row, where the currency exchange rates exists and the date is equal to the passed date parameter or the last date before the passed date parameter? The problem is: LEFT OUTER JOIN devisen AS foreignCurrency ON foreignCurrency.datum = documentHead.datum if the row for foreignCurrency.datum not exists or the currency exchange rates are NULL. If I use LEFT OUTER JOIN devisen AS foreignCurrency ON foreignCurrency.datum <= documentHead.datum I get all less than foreignCurrency.datum, but I only need the newest row. In the following example there are missing rows for a date or a row where are all columns are NULL.: If documentHead.datum is 2009-08-09 and there is no row for that date, I need newest row before. In that case the row for 2009-08-07. I tried to use TOP 1 and a rewrite for the LEFT OUTER JOIN like the following.: SELECT documentHead.r_art AS documentHeadDocClass, foreignCurrency.usd_brief AS foreignCurrencyUSDAsk, foreignCurrency.usd_geld AS foreignCurrencyUSDBid, foreignCurrency.chf_brief AS foreignCurrencyCHFAsk, foreignCurrency.chf_geld AS foreignCurrencyCHFBid, foreignCurrency.gbp_brief AS foreignCurrencyGBPAsk, foreignCurrency.gbp_geld AS foreignCurrencyGBPBid, foreignCurrency.jpy_brief AS foreignCurrencyJPYAsk, foreignCurrency.jpy_geld AS foreignCurrencyJPYBid, documentListPeriods.id AS documentPeriodsID, documentListPeriods.von AS documentPeriodsFrom, documentListPeriods.bis AS documentPeriodsTo, documentListPeriods.tage AS documentPeriodsDays, documentListPeriods.rate AS documentPeriodsRate, documentListPeriods.wert AS documentPeriodsWorth FROM ( SELECT documentPeriods.id AS documentPeriodsID, documentPeriods.von AS documentPeriodsFrom, documentPeriods.bis AS documentPeriodsTo, documentPeriods.tage AS documentPeriodsDays, documentPeriods.rate AS documentPeriodsRate, documentPeriods.wert AS documentPeriodsWorth FROM comperiode AS documentPeriods WHERE documentPeriods.id = $P{document_id} ) AS documentListPeriods INNER JOIN tckopf AS documentHead ON documentHead.id = $P{document_id} LEFT OUTER JOIN ( SELECT TOP 1 foreignCurrency.usd_brief AS foreignCurrencyUSDAsk, foreignCurrency.usd_geld AS foreignCurrencyUSDBid, foreignCurrency.chf_brief AS foreignCurrencyCHFAsk, foreignCurrency.chf_geld AS foreignCurrencyCHFBid, foreignCurrency.gbp_brief AS foreignCurrencyGBPAsk, foreignCurrency.gbp_geld AS foreignCurrencyGBPBid, foreignCurrency.jpy_brief AS foreignCurrencyJPYAsk, foreignCurrency.jpy_geld AS foreignCurrencyJPYBid FROM devisen AS foreignCurrency WHERE foreignCurrency.datum <= documentHead.$P!{exchangeRateTiming} AND foreignCurrency.datum IS NOT NULL AND foreignCurrency.usd_brief IS NOT NULL AND foreignCurrency.usd_geld IS NOT NULL AND foreignCurrency.chf_brief IS NOT NULL AND foreignCurrency.chf_geld IS NOT NULL AND foreignCurrency.gbp_brief IS NOT NULL AND foreignCurrency.gbp_geld IS NOT NULL AND foreignCurrency.jpy_brief IS NOT NULL AND foreignCurrency.jpy_geld IS NOT NULL ORDER BY foreignCurrency.datum DESC ) AS foreignCurrency But then I get a null pointer exception in iReport.: Error filling print... Error executing SQL statement for : boss_charterfaktura_document_list_interval_positions Setting up the file resolver...  net.sf.jasperreports.engine.JRException: Error executing SQL statement for : boss_charterfaktura_document_list_interval_positions      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:246)      at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1086)      at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667)      at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1253)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:822)      at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61)      at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446)      at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:276)      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:745)      at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:891)      at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)      at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)  Caused by: java.sql.SQLException: java.lang.NullPointerException      at com.hxtt.sql.dl.a(Unknown Source)      at com.hxtt.sql.c3.a(Unknown Source)      at com.hxtt.sql.aj.else(Unknown Source)      at com.hxtt.sql.aj.cm(Unknown Source)      at com.hxtt.sql.aj.s(Unknown Source)      at com.hxtt.sql.dl.a(Unknown Source)      at com.hxtt.sql.dl.a(Unknown Source)      at com.hxtt.sql.el.a(Unknown Source)      at com.hxtt.sql.dl.a(Unknown Source)      at com.hxtt.sql.br.a(Unknown Source)      at com.hxtt.sql.ai.a(Unknown Source)      at com.hxtt.sql.dn.executeQuery(Unknown Source)      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:239)      at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1086)      at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667)      at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1253)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:822)      at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61)      at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446)      at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:276)      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:745)      at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:891)      at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)      at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)        at com.hxtt.global.SQLState.SQLException(Unknown Source)      at com.hxtt.sql.br.a(Unknown Source)      at com.hxtt.sql.ai.a(Unknown Source)      at com.hxtt.sql.dn.executeQuery(Unknown Source)      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:239)      ... 12 more  Print not filled. Try to use an EmptyDataSource... How can I achieve that in SQL?