How to add a Queried Valued to a query-CodePudding

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

CodePudding user response：

The syntax error of the last query comes from the fact that you are doing a LEFT JOIN without the ON condition. Infact doing so you are trying to mimic the behave of the CROSS JOIN. Changing it to a CROSS JOIN should work:


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

CodePudding user response：

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