java - Derby 中的sql错误 - ERROR 42X01 : Syntax error: Encountered “WHERE”

Question

我已经看到了有关此错误的几个问题，但每个解决方案都不同，因为它是所谓的“语法错误”。我在生产中使用 Oracle，在开发中使用 Derby(非常烦人，但我能做什么)。

当我运行在 Oracle 上创建的某个 SQL 命令时，它似乎工作正常并且执行其预期的操作(正在使用 Oracle SQL Developer)。但是当我想在 Derby 中运行相同的命令时，我遇到了这个错误。

无论我做什么，我都会遇到这个错误。

WARN | SQL Error: 20000, SQLState: 42X01

ERROR | Syntax error: Encountered "WHERE" at line 94, column 6.

我一生都无法弄清楚出了什么问题。这是我的 SQL 命令。有点长而且复杂:

CREATE VIEW BDPBCDBView AS SELECT 
        BDP_INSTITUTION_NAME,
        BIC,
        BDP_COUNTRY_NAME,               
        BDP_ISO_COUNTRY_CODE,            
        BDP_CITY,                       
        BDP_NETWORK_CONNECTIVITY,      
        BDP_SERVICE_CODES,               
        BDP_ISTARGET,                        
        BCDB_NAME,                       
        BCDB_LAENDERKENNZEICHEN,  
        BCDB_AKTIVMERKMALBANK,          
        BCDB_AKTIVMERKMALLAND,     
        BCDB_AKTIVMERKMALBANKLAND, 
        BCDB_SWIFTKENNZEICHEN,    
        COUNTRYCODE,     
        ISBDP,    
        ISBCDB,    
        BCDB_ORT,
        s1.BICS_RMA 
       FROM
(SELECT 
        bdp.bic, 
        bdp.institution_name AS bdp_institution_name, 
        bdp.country_name AS bdp_country_name, 
        bdp.iso_country_code AS bdp_iso_country_code,
        bdp.city AS bdp_city, 
        bdp.network_connectivity AS bdp_network_connectivity, 
        bdp.service_codes as bdp_service_codes, 
        bdp.isTarget AS bdp_isTarget, 
        bcdb.name as bcdb_name, 
        bcdb.laenderKennzeichen as bcdb_laenderKennzeichen, 
        bcdb.aktivMerkmalBank AS bcdb_aktivMerkmalBank, 
        bcdb.aktivMerkmalLand AS bcdb_aktivMerkmalLand, 
        bcdb.aktivMerkmalBankLand AS bcdb_aktivMerkmalBankLand, 
        bcdb.swiftKennzeichen AS bcdb_swiftKennzeichen, 
        CASE 
            WHEN bcdb.laenderKennzeichen IS NOT NULL THEN bcdb.laenderKennzeichen
            ELSE bdp.iso_country_code
        END AS countryCode,
        CASE 
            WHEN bdp.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbdp,
        CASE 
            WHEN bcdb.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbcdb,
        bcdb.ort AS bcdb_ort 
        FROM BDP bdp LEFT JOIN BCDB bcdb ON bdp.bic = bcdb.bic WHERE bdp.bic IS NOT NULL 
    UNION ALL SELECT
        bcdb.bic, 
        bdp.institution_name AS bdp_institution_name, 
        bdp.country_name AS bdp_country_name, 
        bdp.iso_country_code AS bdp_iso_country_code, 
        bdp.city AS bdp_city, 
        bdp.network_connectivity AS bdp_network_connectivity, 
        bdp.service_codes as bdp_service_codes,
        bdp.isTarget AS bdp_isTarget, 
        bcdb.name as bcdb_name, 
        bcdb.laenderKennzeichen as bcdb_laenderKennzeichen, 
        bcdb.aktivMerkmalBank AS bcdb_aktivMerkmalBank, 
        bcdb.aktivMerkmalLand AS bcdb_aktivMerkmalLand, 
        bcdb.aktivMerkmalBankLand AS bcdb_aktivMerkmalBankLand, 
        bcdb.swiftKennzeichen AS bcdb_swiftKennzeichen, 
        CASE 
            WHEN bcdb.laenderKennzeichen IS NOT NULL THEN bcdb.laenderKennzeichen
            ELSE bdp.iso_country_code
        END AS countryCode,
        CASE 
            WHEN bdp.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbdp,
        CASE 
            WHEN bcdb.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbcdb,
        bcdb.ort AS bcdb_ort 
    FROM BDP bdp RIGHT JOIN BCDB bcdb ON bdp.bic = bcdb.bic WHERE bdp.bic IS NULL)  
    t1 LEFT JOIN    ( SELECT * FROM
    (
      SELECT s1.BIC_CRSPNDT AS BICS_RMA FROM      
    (SELECT
    rma.crspdt AS BIC_CRSPNDT,
    rma.issr AS BIC_ISSR    
    From RMA    
    WHERE ((RMA.tp= 'Issued' OR RMA.tp = 'Received') AND RMA.RMASTS='Enabled' AND RMA.SVCNM='swift.fin') )s1
    UNION
    SELECT s1.BIC_ISSR AS BIC FROM (SELECT
    rma.crspdt AS BIC_CRSPNDT, 
    rma.issr AS BIC_ISSR     
    FROM RMA 
     WHERE ((RMA.tp= 'Issued' OR RMA.tp = 'Received') AND RMA.RMASTS='Enabled' AND RMA.SVCNM='swift.fin') )s1 ) 
     WHERE BICS_RMA IS NOT NULL      
    ORDER BY BICS_RMA) s1 
    ON (s1.BICS_RMA = substr(t1.BIC, 1,8))

错误发生在倒数第三行。

我在 Java 中读到的代码是:

@PersistenceContext
EntityManager em;

@PostConstruct
public void createViewIfNeeded() {

    if (FidaProfile.isActive(FidaProfile.DEVELOPMENT)) {

        em.createNativeQuery("DROP TABLE BDPBCDBView").executeUpdate();

        String command_1 = loadDevelopmentViewScript("DEV-DB/init_dev_view.sql");//BDPBCDView sql script, this is made from 3 tables namely BCDB, BDP and RMA 

        em.createNativeQuery(command_1).executeUpdate();

    }
}

public void setEm(EntityManager em) {
    this.em = em;
}

private String loadDevelopmentViewScript(String addressOfSQLScript) {
    try {
        InputStream stream = BDPBCDPViewGenerator.class.getClassLoader().getResourceAsStream(addressOfSQLScript);

        ByteArrayOutputStream result = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int length;
        while ((length = stream.read(buffer)) != -1) {
            result.write(buffer, 0, length);
        }
        return result.toString("UTF-8");
    } catch (IOException e) {
        throw new FidaErrorCodeException(FidaErrorCode.UNEXPECTED_EXCEPTION,
                "Could NOT load Development-View-Script", e);
    }
}

Answer 1

顺便说一句，良好的格式对于跟踪复杂查询中发生的情况至关重要!

由于我之前的答案不正确，如果您要更改 s1 子查询以便进行逆透视而不是使用联合，会怎么样？像这样的东西:

SELECT DISTINCT CASE WHEN dummy.id = 1 THEN r.bic_crspndt
                     WHEN dummy.id = 2 THEN r.bic_issr
                END AS bics_rma
FROM   (SELECT rma.crspdt AS bic_crspndt,
               rma.issr   AS bic_issr
        FROM   rma
        WHERE  (rma.tp = 'Issued' OR rma.tp = 'Received')
        AND    rma.rmasts = 'Enabled'
        AND    rma.svcnm = 'swift.fin') r
       INNER JOIN (SELECT 1 ID FROM dual UNION ALL
                   SELECT 2 ID FROM dual) dummy ON (dummy.id = 1 AND r.crspdt IS NOT NULL)
                                                   OR (dummy.id = 2 AND r.issr IS NOT NULL);

如果您这样做，Derby 数据库也许能够应付？

注意我通过条件交叉连接使用手动 UNPIVOT，而不是 Oracle 11g UNPIVOT 函数，因为我不知道有关 Derby 的任何信息，而且那里可能不支持 UNPIVOT。我无法理解为什么您被迫在实时环境和开发环境之间使用不同的数据库平台；听起来很疯狂，而且可能相当危险!我想您已经尝试过标记这一点!