CODEWITHSUNDEEP
javasqloracle-databasederby
ITguy
ITguy

Reputation: 888

sql Error in derby - ERROR 42X01: Syntax error: Encountered “WHERE”

I have seen several questions regarding this error but each solution is different as it is a so called "Syntax error". I use Oracle in production and Derby in development (extremely annoying but what can I do).

When I run a certain SQL command that I have created on Oracle it seems to work fine and do what it is suppose to (am using Oracle SQL Developer). But when I want to run the same command in Derby I encounter this error.

And I encounter this error no matter what I seem to do.

WARN | SQL Error: 20000, SQLState: 42X01

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

For the life of me I cannot figure out what is wrong. Here is my SQL command. It is a bit long and complicated:

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))

The error occurs at the third to last line.

My read in code in Java is :

@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);
    }
}

Upvotes: 0

Views: 2791

Answers (1)

Boneist
Boneist

Reputation: 23578

As an aside, good formatting is vital to keep track of what is going on in complex queries!

Since my previous answer was incorrect, what about if you were to change the s1 subquery so that you were unpivoting rather than using union? Something like:

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);

Maybe if you were to do that, the Derby database would be able to cope with it?

N.B. I've used a manual UNPIVOT via a conditional cross join rather than the Oracle 11g UNPIVOT function since I don't know anything about Derby and UNPIVOT might not be supported there. Quite why you're being forced to use different database platforms between your live and dev environments is beyond me; sounds bonkers and potentially rather dangerous! I assume you've already tried flagging that!

Upvotes: 2

Related Questions