如何在查询中添加价值的查询

Question

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

以下代码的结果是 21。当我在主查询中输入 21 来代替它时，它就起作用了。

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

我有下面的查询会导致此错误：

错误：运算符不存在：文本 * 间隔 第 6 行：(mo.date_planned_start::date + (leadtime.value) * INTERVAL... ^ 提示：没有运算符与给定名称和参数类型匹配。您可能需要添加显式类型转换。

    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

我在 Leadtime.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%')