I failed thrice at sending this from the google forum. That editor is
beyond painful.
Using jOOQ 3.14, java 15 and Postgres (12)
Postgres overloads substring to work on bytea data, so against
Column | Type | Collation | Nullable | Default
--------------+-------+-----------+----------+---------
id | uuid | | not null |
person_id | uuid | | not null |
markerset_id | uuid | | not null |
calls | bytea | | not null |
I can
Using jOOQ 3.14, java 15 and Postgres (12)
Postgres overloads substring to work on bytea data, so against
Column | Type | Collation | Nullable | Default
--------------+-------+-----------+----------+---------
id | uuid | | not null |
person_id | uuid | | not null |
markerset_id | uuid | | not null |
calls | bytea | | not null |
I can
select substring(calls,1, 10) from viv.genotype where calls is not
null limit 3;
substring
------------------------
\x12111222212112121121
\x22112211111122221111
\x22112211111122221111
(3 rows)
*** NOTE: 20 characters
But my naive attempt in code
Result<?> peddata = dsl
.select(PEOPLE.NAME.as("peoplename"),
ego.NAME,
(coalesce(ma.NAME,"0")).as("maname"),
(coalesce(pa.NAME, "0")).as("paname"),
choose(ego.GENDER)
.when("m", 1)
.when("f", 2)
.otherwise(0).as("regender"),
substring(GENOTYPE.CALLS, startIndex, markerCount).as("gtdata"))
.from(PEOPLE)
.join(PROBANDSET).on(PEOPLE.ID.equal(PROBANDSET.PEOPLE_ID))
.join(SEGMENT).on(PROBANDSET.ID.equal(SEGMENT.PROBANDSET_ID))
.join(PEOPLE_MEMBER).on(PEOPLE.ID.equal(PEOPLE_MEMBER.PEOPLE_ID))
.join(ego).on(PEOPLE_MEMBER.PERSON_ID.equal(ego.ID))
.join(ascendTrim(PROBANDSET.PROBANDS)).on(ego.ID.equal(ASCEND_TRIM.ID))
.leftJoin(pa).on(ego.PA.equal(pa.ID))
.leftJoin(ma).on(ego.MA.equal(ma.ID))
.leftJoin(GENOTYPE).on(ego.ID.equal(GENOTYPE.PERSON_ID).and(GENOTYPE.MARKERSET_ID.equal(SEGMENT.MARKERSET_ID)))
.where(SEGMENT.ID.equal(segmentId))
.fetch();
generates a compilation error
error: no suitable method found for
substring(TableField<GenotypeRecord,byte[]>,int,int)
substring(GENOTYPE.CALLS, startIndex, markerCount).as("gtdata"))
^
method DSL.substring(Field<String>,Field<? extends Number>,Field<?
extends Number>) is not applicable
(argument mismatch; TableField<GenotypeRecord,byte[]> cannot be
converted to Field<String>)
method DSL.substring(Field<String>,int,int) is not applicable
(argument mismatch; TableField<GenotypeRecord,byte[]> cannot be
converted to Field<String>)
I don't see any reference to this substring(bytea) in the docs. Short
of straight sql am I left with the jOOQ equivalent of
*** NOTE: double the length of the requested string
select substring(calls,1, 20) from viv.genotype where calls is not
null limit 3;
substring
------------------------
\x12111222212112121121
\x22112211111122221111
\x22112211111122221111
(3 rows)
*** NOTE: 20 characters
But my naive attempt in code
Result<?> peddata = dsl
.select(PEOPLE.NAME.as("peoplename"),
ego.NAME,
(coalesce(ma.NAME,"0")).as("maname"),
(coalesce(pa.NAME, "0")).as("paname"),
choose(ego.GENDER)
.when("m", 1)
.when("f", 2)
.otherwise(0).as("regender"),
substring(GENOTYPE.CALLS, startIndex, markerCount).as("gtdata"))
.from(PEOPLE)
.join(PROBANDSET).on(PEOPLE.ID.equal(PROBANDSET.PEOPLE_ID))
.join(SEGMENT).on(PROBANDSET.ID.equal(SEGMENT.PROBANDSET_ID))
.join(PEOPLE_MEMBER).on(PEOPLE.ID.equal(PEOPLE_MEMBER.PEOPLE_ID))
.join(ego).on(PEOPLE_MEMBER.PERSON_ID.equal(ego.ID))
.join(ascendTrim(PROBANDSET.PROBANDS)).on(ego.ID.equal(ASCEND_TRIM.ID))
.leftJoin(pa).on(ego.PA.equal(pa.ID))
.leftJoin(ma).on(ego.MA.equal(ma.ID))
.leftJoin(GENOTYPE).on(ego.ID.equal(GENOTYPE.PERSON_ID).and(GENOTYPE.MARKERSET_ID.equal(SEGMENT.MARKERSET_ID)))
.where(SEGMENT.ID.equal(segmentId))
.fetch();
generates a compilation error
error: no suitable method found for
substring(TableField<GenotypeRecord,byte[]>,int,int)
substring(GENOTYPE.CALLS, startIndex, markerCount).as("gtdata"))
^
method DSL.substring(Field<String>,Field<? extends Number>,Field<?
extends Number>) is not applicable
(argument mismatch; TableField<GenotypeRecord,byte[]> cannot be
converted to Field<String>)
method DSL.substring(Field<String>,int,int) is not applicable
(argument mismatch; TableField<GenotypeRecord,byte[]> cannot be
converted to Field<String>)
I don't see any reference to this substring(bytea) in the docs. Short
of straight sql am I left with the jOOQ equivalent of
*** NOTE: double the length of the requested string
select substring(calls::text,1, 20) from viv.genotype where calls
is not null limit 3;
substring
----------------------
\x121112222121121211
\x221122111111222211
\x221122111111222211
(3 rows)
--
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].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/85f9d384-1d24-09e3-2226-c10ad3b2a140%40gmail.com.
