Some little complex select

[polderudo]

Does anyone know if it's possible to build such a query with QueryMods?
I'v been trying for hours now but can't get it work and I'm unsure, if it is possible at all :)

It's working nice with generated code but i need to inject 2 parts into the query, based on some situations and therefore would like to have only one query instead of 2 or 3 different in sql files.

here we go

with run as (
    select s.sublot_id, h.hut_id, s.quantity, hf.shelf_equip_id, hs.eqp_id, s.best_before_date_qm,
           sum(s.quantity) over (order by s.sublot_id) as running_total  from v_sublot s
    join v_hut h on h.hut_id=s.hut_id and h.metal_detected=false and h.no_read=false and h.is_in_hbw=true
            and s.reserved_for_batch_id is null and s.reserved_for_id is null
            and h.parent_hut_id is null
    join hbw_slot hs on hs.eqp_id=h.eqp_id and hs.status='free'
    join hbw_shelf hf on hf.shelf_equip_id=hs.shelf and hf.status=any($1 /* shelf_free_states */)
    where s.mat_def_id=$2
      and s.best_before_date_qm>=$3 /* min_best_before_date_qm */ -- needs to be injected
      and s.sublot_status=any($5 /* sublot_status_typ */)

    order by s.best_before_date_qm, hs.distance -- needs to be injected
),
 lowest_over as (
     select min(running_total) AS min_sum
     from run
     where running_total >= $4 /* min_quantity */
 ) 
select run.*
from run
    cross join lowest_over lo
where run.running_total <= lo.min_sum;

[polderudo]

I could use generated code if something like this would be possible (like in sqlc)
and (case when $6 /* :uom_1:string:null */ is not null then s.uom_1=$6 else true end)
but unfortunately bob can not determin the data type:

parse queries: parse query 0: get args and cols: prepare: pq: could not determine data type of parameter $6
exit status 1

[stephenafamo]

I think there's an error with your annotation. Try

and (case when $6 /* uom_1:string:null */ is not null then s.uom_1=$6 else true end)

[polderudo]

Unfortunately the same

[stephenafamo]

I'll take a look later. I'm quite a bit swamped at the moment, so it make take me some time to get to this.