如何将查询值添加到查询中

Question

我有一个查询，需要使用另一个查询的结果值来计算日期。

以下代码产生21。当我只是在主查询中输入21来代替它时，它就工作了。

SELECT value from ir_config_parameter where key = 'xes.mrp.production.default.kit.complete.to.far.offset.days'

我在下面有一个查询，它导致了这个错误：

错误：运算符不存在：text*interval第6行：(mo.date_planned_start:：日期+(交付周期值(*间隔。。。^提示：没有与给定名称和参数类型匹配的运算符。您可能需要添加显式类型强制转换。

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%')

现在的问题似乎是它这一部分中发生的实际数学问题，因为当我把leadtime.value作为一列时，它会得到预期的21。

(mo.date_planned_start::date + leadtime.value * INTERVAL '1 day')

Answer 1

上一个查询的语法错误是因为您在执行不带ON条件的LEFT JOIN。实际上，这样做是在试图模仿CROSS JOIN的行为。将其更改为CROSS JOIN应该有效：

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%')

Answer 2

我在交付周期.value之后添加了：numeric，它解决了我的问题。

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%')