I have a query that needs to calculate a date using the resulting value of another query.
The following code results in 21. When I just input 21 into the main query in place of this, it works.
SELECT value from ir_config_parameter where key = 'xes.mrp.production.default.kit.complete.to.far.offset.days'
I have this query below which results in this error:
ERROR: operator does not exist: text * interval LINE 6: (mo.date_planned_start::date + (leadtime.value) * INTERVAL... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
SELECT
mo.name,
CASE WHEN mo.x_far_confirmed::date is not null then mo.x_far_confirmed::date else
CASE WHEN rtw.produce_delay != 0 THEN (mo.date_planned_start::date + rtw.produce_delay * INTERVAL '1 day') ELSE
(mo.date_planned_start::date+leadtime.value*INTERVAL '1 day') END END as MFAR
FROM
mrp_production AS mo
LEFT JOIN product_product AS pp ON mo.product_id = pp.id
LEFT JOIN mrp_routing AS rt ON mo.routing_id = rt.id
LEFT JOIN mrp_routing_workcenter AS rtw ON rt.id = rtw.routing_id
CROSS JOIN
(SELECT value from ir_config_parameter where key = 'xes.mrp.production.default.kit.complete.to.far.offset.days') as leadtime
WHERE
mo.state IN ('planning','confirmed','draft','ready','in_production','done')
AND (pp.default_code LIKE '900%')
The issue now seems to be the actual math happening within this part of it because when I put leadtime.value as a column, it results in 21 as expected.
(mo.date_planned_start::date + leadtime.value * INTERVAL '1 day')
I added ::numeric after leadtime.value and it has resolved my issue.
SELECT
mo.name,
CASE WHEN mo.x_far_confirmed::date is not null then mo.x_far_confirmed::date else
CASE WHEN rtw.produce_delay != 0 THEN (mo.date_planned_start::date + rtw.produce_delay * INTERVAL '1 day') ELSE
(mo.date_planned_start::date+leadtime.value::numeric *INTERVAL '1 day') END END as MFAR
FROM
mrp_production AS mo
LEFT JOIN product_product AS pp ON mo.product_id = pp.id
LEFT JOIN mrp_routing AS rt ON mo.routing_id = rt.id
LEFT JOIN mrp_routing_workcenter AS rtw ON rt.id = rtw.routing_id
CROSS JOIN
(SELECT value from ir_config_parameter where key = 'xes.mrp.production.default.kit.complete.to.far.offset.days') as leadtime
WHERE
mo.state IN ('planning','confirmed','draft','ready','in_production','done')
AND (pp.default_code LIKE '900%')