I have a query that I am trying to translate to jooq constructs. I keep
getting a syntax error related to the usage of nullif in a div function.
Here is the query that I am trying to translate:
select * from (
select A.LOCATION, A.PRODUCT, A.METRIC, A.STAT_NAME,
A.SCENARIO_NAME as A_S_NAME,
A.STAT_COUNT as A_COUNT, A.AVERAGE As A_AVG, A.STD_DEV
as A_STD_DEV,
B.SCENARIO_NAME as B_S_NAME,
B.STAT_COUNT as B_COUNT, B.AVERAGE As B_AVG, B.STD_DEV
as B_STD_DEV,
(A.AVERAGE-B.AVERAGE) as A_AVG_MINUS_B_AVG,
(A.AVERAGE-B.AVERAGE)/NULLIF(B.AVERAGE, 0) as RDIFF,
ABS((A.AVERAGE-B.AVERAGE)/NULLIF(B.AVERAGE, 0)) as
ARDIFF
from AR_INV_FILL_RATES as A, AR_INV_FILL_RATES as B
where A.STAT_NAME = B.STAT_NAME
and A.SCENARIO_NAME > B.SCENARIO_NAME) as tmpTbl
where ARDIFF > 0.05;
So far I have code such as:
ArInvOnhand a = AR_INV_ONHAND.as("a");
ArInvOnhand b = AR_INV_ONHAND.as("b");
Field<Double> a_avg_minus_b_avg =
a.AVERAGE.minus(b.AVERAGE).as("A_AVG_MINUS_B_AVG");
//a.AVERAGE.minus(b.AVERAGE).div(nullif(b.AVERAGE,0)).as("RDIFF");
//a_avg_minus_b_avg.div(nullif(b.AVERAGE,0));
//a_avg_minus_b_avg.div(nullif(b.AVERAGE,0));
org.jooq.Table<Record11<String, String, String, String, Double, Double, Double,
Double, Double, Double, Double>>
record11Table = getDSLContext().select(a.LOCATION, a.PRODUCT, a.METRIC,
a.STAT_NAME.as("A_SCEN_NAME"),
a.STAT_COUNT.as("A_COUNT"), a.AVERAGE.as("A_AVG"),
a.STD_DEV.as("A_STD_DEV"),
b.STAT_COUNT.as("B_COUNT"), b.AVERAGE.as("B_AVG"),
b.STD_DEV.as("B_STD_DEV"),
a_avg_minus_b_avg)
.from(a.join(b).on(a.STAT_NAME.eq(b.STAT_NAME)))
.where(a.SCENARIO_NAME.greaterThan(b.SCENARIO_NAME)).asTable();
The commented lines are attempts to build the division part of the query, but
my IDE reports that it
"cannot resolve method div(org.jooq.Field<T>)"
I do not understand why this is the case. I guess that I am misunderstanding
something about the DSL construction.
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
