DISTINCT ON() in jOOQ
This is definitely non-obvious to discover. There's a SelectDistinctOnStep.distinctOn()
method after you've selected your actual columns. The reason why it's non-obvious to discover is that the PostgreSQL syntax itself is a bit hard to model in an internal DSL like jOOQ's.
Think of it this way: You're selecting a set of columns (the correlated subquery) while specifying on which columns a distinctness filter should be applied:
Result<Record> result = create .select(field("(select nr_zew from bo_get_sip_cti_polaczenie_info(uuid))").as("nr_zew")) .distinctOn(field("uuid")) .from("bo_sip_cti_event_day") .where("data_ins::date = current_date") .and("kierunek like 'P'") .and("(hangup_cause like 'NO_ANSWER' or hangup_cause like 'NO_USER_RESPONSE') ") .fetch();
Or, if you were using the code generator:
Result<Record> result = create .select(field( select(BO_GET_SIP_CTI_POLACZENIE_INFO.NR_ZEW) .from(BO_GET_SIP_CTI_POLACZENIE_INFO.call(BO_SIP_CTI_EVENT_DAY.UUID))).as("nr_zew")) .distinctOn(BO_SIP_CTI_EVENT_DAY.UUID) .from(BO_SIP_CTI_EVENT_DAY) .where(BO_SIP_CTI_EVENT_DAY.cast(Date.class).eq(currentDate())) .and(BO_SIP_CTI_EVENT_DAY.KIERUNEK.like("P")) .and(BO_SIP_CTI_EVENT_DAY.HANGUP_CAUSE.like("NO_ANSWER") .or(BO_SIP_CTI_EVENT_DAY.HANGUP_CAUSE.like("NO_USER_RESPONSE"))) .fetch();
Side-note on your usage of LIKE
Do note that the underscore (_
) character is a single-character wild-card in SQL, so your LIKE
predicates might not be entirely correct. Ideally, just use ordinary comparison predicates, such as:
kierunek = 'P'
hangup_cause IN ('NO_ANSWER', 'NO_USER_RESPONSE')
You don't really seem to need LIKE
.