Foxpro

如果沒有日期參數或條目的行，則從表中獲取日期參數之前的最後一個日期的行

  • February 26, 2014

我有一張桌子，裡面有貨幣的匯率。還有一個Date列，保存貨幣匯率的獲取日期。

對於某些日期，有一行，但沒有貨幣匯率，對於某些日期，該表中沒有行。

要獲取特定日期的貨幣匯率，我需要將日期作為參數傳遞。

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

如何獲取貨幣匯率表行，其中貨幣匯率存在並且日期等於傳遞的日期參數或傳遞的日期參數之前的最後一個日期？

問題是：

LEFT OUTER JOIN devisen AS foreignCurrency ON foreignCurrency.datum = documentHead.datum

如果foreignCurrency.datum不存在的行或貨幣匯率為NULL。如果我使用

LEFT OUTER JOIN devisen AS foreignCurrency ON foreignCurrency.datum <= documentHead.datum

我得到的都小於foreignCurrency.datum，但我只需要最新的行。

在以下範例中，缺少日期行或所有列均為NULL. 的行：

日期缺失值的貨幣匯率

如果documentHead.datum是2009-08-09並且該日期沒有行，我需要最新的行。在這種情況下，為2009-08-07.

我嘗試使用TOP 1並重寫LEFT OUTER JOIN以下內容：

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

但後來我在 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...

如何在 SQL 中實現這一點？

這種類型的查詢可以使用派生表或相關子查詢或在具有視窗函式的現代 DBMS 中完成。一些 DBMS 對標準 SQL 語法有特殊的專有擴展，如 SQL-ServerOUTER APPLY和 PostgreSQL 的DISTINCT ON，也可以用來解決這類問題。

由於 FoxPro 沒有這些，您可以使用派生表方法或我稱之為*“窮人OUTER APPLY”*的方法：

SELECT 
   dh.r_art  AS documentHeadDocClass,
   fc.usd_brief AS foreignCurrencyUSDAsk,
   fc.usd_geld  AS foreignCurrencyUSDBid,
   fc.chf_brief AS foreignCurrencyCHFAsk,
   fc.chf_geld  AS foreignCurrencyCHFBid,
   fc.gbp_brief AS foreignCurrencyGBPAsk,
   fc.gbp_geld  AS foreignCurrencyGBPBid,
   fc.jpy_brief AS foreignCurrencyJPYAsk,
   fc.jpy_geld  AS foreignCurrencyJPYBid,
   dp.id   AS documentPeriodsID,
   dp.von  AS documentPeriodsFrom,
   dp.bis  AS documentPeriodsTo,
   dp.tage AS documentPeriodsDays,
   dp.rate AS documentPeriodsRate,
   dp.wert AS documentPeriodsWorth
FROM
       comperiode AS dp                 -- documentPeriods
   INNER JOIN 
       tckopf AS dh                     -- documentHead 
           ON dh.id = dp.id 
   LEFT OUTER JOIN
       devisen AS fc                    -- foreignCurrency
           ON fc.datum =
              ( SELECT TOP (1) fci.datum
                FROM devisen AS fci
                WHERE fci.datum <= dh.datum 
                ORDER BY fci.datum DESC 
              )
WHERE 
       dh.id = $P{document_id} 
 AND   dp.id = $P{document_id} ;

我認為您忘記ON在末尾添加子句：

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
       fc.usd_brief AS foreignCurrencyUSDAsk,
       fc.usd_geld AS foreignCurrencyUSDBid,
       fc.chf_brief AS foreignCurrencyCHFAsk,
       fc.chf_geld AS foreignCurrencyCHFBid,
       fc.gbp_brief AS foreignCurrencyGBPAsk,
       fc.gbp_geld AS foreignCurrencyGBPBid,
       fc.jpy_brief AS foreignCurrencyJPYAsk,
       fc.jpy_geld AS foreignCurrencyJPYBidб
       fc.datum
   FROM
       devisen AS fс
   WHERE
       fc.datum <= $P!{exchangeRateTiming}
       AND fc.datum IS NOT NULL
       AND fc.usd_brief IS NOT NULL
       AND fc.usd_geld IS NOT NULL
       AND fc.chf_brief IS NOT NULL
       AND fc.chf_geld IS NOT NULL
       AND fc.gbp_brief IS NOT NULL
       AND fc.gbp_geld IS NOT NULL
       AND fc.jpy_brief IS NOT NULL
       AND fc.jpy_geld IS NOT NULL
   ORDER BY fc.datum DESC  ) AS foreignCurrency
ON foreignCurrency.datum = documentHead.datum

順便說一句，我不喜歡在子查詢內部和外部重複使用別名。因此我引入了一個fc別名

引用自：https://dba.stackexchange.com/questions/59621

相關問答

Files

.dbf 文件中顯示的表的記錄數與實際記錄數不匹配

  • February 7, 2015
檢視問答
Foxpro

我可以安全地從 FoxPro 2.5 中刪除 .tmp 文件嗎？

  • May 5, 2014
檢視問答
Jdbc

如何在 SQL 查詢中重寫 IFNULL 或 COALESCE？

  • February 13, 2014
檢視問答
Foxpro

從 SQL 中的 SELECT 中減去值

  • February 12, 2014
檢視問答
Index

FoxPro DBF 索引損壞

  • June 16, 2012
檢視問答