Common table expression, with a starting with a values statement gives error in java

Question

I've this CTE which I'd implement in Java:
It's running on an IBM Iseries 7.3 DB2 machine.

WITH params (from_date, to_date, outlet, product_number)
AS (
    values(TO_DATE('01.11.2018', 'DD.MM.YYYY'),
        TO_DATE('18.12.2018', 'DD.MM.YYYY'),
        'BLK' ,
        49 )
    ),
product
AS (
    SELECT DISTINCT cpp.competitor_products_id product
    FROM yxdb.competitor_product_prices cpp
    INNER JOIN yxdb.competitor_products_comparisons cpc ON cpc.competitor_products_id = cpp.competitor_products_id
        AND cpc.deleted = 0
    INNER JOIN yxdb.outlets o ON o.outlets_id = cpc.outlets_id
        AND o.deleted = 0
    INNER JOIN params ON cpp.price_date > params.from_date
        AND cpc.product_number = params.product_number
        AND o.short_name = params.outlet
    WHERE cpp.deleted = 0
    )
select * from product;

It's a lot longer, so the params table is used several times.
When implementing it in Java, I replace the hardcoded dates and other parameters in Java as ?1, ?2 etc. I've also tried with named parameters, none works. They all give [SQL0418] Use of parameter marker or NULL not valid.

Java Code snippet:

@RepositoryRestResource
    public interface CompetitorPriceDateRepository extends JpaRepository<CompetitorPriceDateEntity, Long> {
    @Query(value = "WITH params (from_date, to_date, outlet, product_number) "
                + " AS ( "
                + "     values(TO_DATE( :fromDate , 'DD.MM.YYYY'), "
                + "         TO_DATE( :toDate , 'DD.MM.YYYY'), "
                + "         :outlet , "
                + "         :productNumber ) "
                + "     ), "
                + " product "
                + " AS ( "
                + "     SELECT DISTINCT cpp.competitor_products_id product "
                + "     FROM yxdb.competitor_product_prices cpp "
                + "     INNER JOIN yxdb.competitor_products_comparisons cpc ON +" cpc.competitor_products_id = cpp.competitor_products_id "
                + "         AND cpc.deleted = 0 "
                + "     INNER JOIN yxdb.outlets o ON o.outlets_id = cpc.outlets_id "
                + "         AND o.deleted = 0 "
                + "     INNER JOIN params ON cpp.price_date > params.from_date "
                + "         AND cpc.product_number = params.product_number "
                + "         AND o.short_name = params.outlet "
                + "     WHERE cpp.deleted = 0 "
                + "     ) "
                + " select * from product ",nativeQuery = true) 
    List<CompetitorPriceDateEntity> findAllInterpolatedByDates(
                    @Param("productNumber") Integer productNumber,
                    @Param("outlet") String outlet,
                    @Param("fromDate") String fromDate,
                    @Param("toDate") String toDate
            );

The one where you define the SQL statement and try to execute it. Also the stack trace. — Jens Schauder, Dec 19 '18 at 11:53
@JensSchauder or prasannajoshi would you like anything else ? :-) — Anders Metnik, Dec 19 '18 at 12:02
There seems something wrong in this line: `+ " INNER JOIN yxdb.competitor_products_comparisons cpc ON +" cpc.competitor_products_id = cpp.competitor_products_id "`. Also the stack trace, please. — Jens Schauder, Dec 19 '18 at 13:08
Maybe its something with JPA that I don't know about. In general you need to prepare a statement to use a parameter marker. — danny117, Dec 19 '18 at 13:50
@JensSchauder the SQL is solid, might be a C/P issue if anything. The problem is only in Java . — Anders Metnik, Dec 19 '18 at 13:51
@danny117 Lookup JPA repositories, you don't need to make a preparedstatement. — Anders Metnik, Dec 19 '18 at 13:51
I probably wont look up JPA but I did find this really quick https://stackoverflow.com/questions/23008926/how-to-use-prepared-statement-in-jpa — danny117, Dec 19 '18 at 13:53
Possible duplicate of [How to resolve SQL0418N Error](https://stackoverflow.com/questions/13381898/how-to-resolve-sql0418n-error) — Paulo Pedroso, Dec 19 '18 at 15:54

score 0 · Answer 1 · answered Dec 19 '18 at 15:49

Without the stack trace I dont' have any aidea what's wrong with your query. SQL seems solid.

Try with a named native query

@Entity
@NamedNativeQuery(
        name = “competitor.findProducts.byDateOutletProductnumber", 
        query = "WITH params (from_date, to_date, outlet, product_number) "
            + " AS ( "
            + "     values(TO_DATE( :fromDate , 'DD.MM.YYYY'), "
            + "         TO_DATE( :toDate , 'DD.MM.YYYY'), "
            + "         :outlet , "
            + "         :productNumber ) "
            + "     ), "
            + " product "
            + " AS ( "
            + "     SELECT DISTINCT cpp.competitor_products_id product "
            + "     FROM yxdb.competitor_product_prices cpp "
            + "     INNER JOIN yxdb.competitor_products_comparisons cpc ON +" cpc.competitor_products_id = cpp.competitor_products_id "
            + "         AND cpc.deleted = 0 "
            + "     INNER JOIN yxdb.outlets o ON o.outlets_id = cpc.outlets_id "
            + "         AND o.deleted = 0 "
            + "     INNER JOIN params ON cpp.price_date > params.from_date "
            + "         AND cpc.product_number = params.product_number "
            + "         AND o.short_name = params.outlet "
            + "     WHERE cpp.deleted = 0 "
            + "     ) "
            + " select * from product ", 
        hints = { 
            @QueryHint(name = "org.hibernate.cacheable", value = "true") })
public class CompetitorPriceDateEntity { ... }

pro bonus 1: Named queries are sort of precompiled, so jpa doesn't need to compile the query into a executable criteria every time it gets called.

pro bonus 2: Query hint cacheable or eclipselink equivalent

Look forward to Java 12 which will finally allow multi-line strings! — Thorbjørn Ravn Andersen, Dec 19 '18 at 15:51
@ThorbjørnRavnAndersen Then I have bad news for you, it was dropped from Java 12 and is now scheduled for Java 13. — Mark Rotteveel, Dec 20 '18 at 17:20
@MarkRotteveel I found out. I didn’t like the proposal but it was better than none at all. I hope they find a better solution. I found the reason not to use shell here script syntax very unconvincing. Hopefully they will reconsider — Thorbjørn Ravn Andersen, Dec 20 '18 at 19:00

Common table expression, with a starting with a values statement gives error in java

1 Answers1