There is a basic query that does a specific selection. And you need to attach another field to it. Here is what my query looks like now:

select m.PIPE_NUMBER, m.PIPE_YEAR, s.WC_INSTANCE, r.WORKCENTER_ID, s.RETURN_REASON, m.DIAMETER, m.THICKNESS, m.MELT_NUMBER, m.PART_WELD, m.STEELMARK, m.VENDER, s.WAREHOUSE_BEFORE_MOVING, s.ORDER_NUM_RES || '-' || s.ORDER_ITEM_RES as res_und_order, s.CREATE_DATE, s.INSERT_OPERATOR, (select min(p.CREATE_DATE) keep(dense_rank first order by s.create_date desc) from WC_7_3_SGP s left join WC_4_8_PRIEMKA p on s.SHOP_ID = p.SHOP_ID and s.PIPE_YEAR = p.PIPE_YEAR and s.PIPE_NUMBER = p.PIPE_NUMBER and p.EDIT_STATE = 0 and p.WC_INSTANCE = s.WC_INSTANCE where s.ORDER_NUM_RES is null and s.ORDER_ITEM_RES is null and s.EDIT_STATE = 0 and s.PIPE_NUMBER = m.PIPE_NUMBER) as DATE_CANCEL_RESERV, (select min(p.INSERT_OPERATOR) keep(dense_rank first order by s.create_date desc) from WC_7_3_SGP s left join WC_4_8_PRIEMKA p on s.SHOP_ID = p.SHOP_ID and s.PIPE_YEAR = p.PIPE_YEAR and s.PIPE_NUMBER = p.PIPE_NUMBER and p.EDIT_STATE = 0 and p.WC_INSTANCE = s.WC_INSTANCE where s.ORDER_NUM_RES is null and s.ORDER_ITEM_RES is null and s.EDIT_STATE = 0 and s.PIPE_NUMBER = m.PIPE_NUMBER) as OPERATOR_PRM from WC_MAIN m left join WC_1_2_LIST l on m.SHOP_ID = l.SHOP_ID and m.PIPE_YEAR = l.PIPE_YEAR and m.PIPE_NUMBER = l.PIPE_NUMBER left join WC_4_8_PRIEMKA p on m.SHOP_ID = p.SHOP_ID and m.PIPE_YEAR = p.PIPE_YEAR and m.PIPE_NUMBER = p.PIPE_NUMBER left join WC_7_3_SGP s on m.SHOP_ID = s.SHOP_ID and m.PIPE_YEAR = s.PIPE_YEAR and m.PIPE_NUMBER = s.PIPE_NUMBER left join ROUTE_PIPE_MOVES r on m.SHOP_ID = r.SHOP_ID and m.PIPE_YEAR = r.PIPE_YEAR and m.PIPE_NUMBER = r.PIPE_NUMBER where m.EDIT_STATE = 0 and p.EDIT_STATE = 0 and l.EDIT_STATE = 0 and s.EDIT_STATE = 0 and r.EDIT_STATE = 0 and r.ACTION = 'MOVE'

To the main part of the request you need to attach a field on the following conditions using case:

  • If a

    1. the minimum date of the receiving point is greater than the maximum date of the shr point,
    2. Return address is null and
    3. s.ORDER_NUM_RES||'-'|| s.ORDER_ITEM_RES = p.ORDER_NUMBER

    then the field I need is created, say with the name status, and it is written “Confirmed”,

  • otherwise: the same conditions only s.ORDER_NUM_RES||'-'|| s.ORDER_ITEM_RES <> p.ORDER_NUMBER s.ORDER_NUM_RES||'-'|| s.ORDER_ITEM_RES <> p.ORDER_NUMBER no longer equal, then the field is “Not confirmed”,
  • otherwise, the field is spelled "Active."

I can not understand how to stick it among the fields. Either very complex nesting with cases, or I can not imagine.

 select p.CREATE_DATE, s.CREATE_DATE, s.RETURN_ADDRESS, s.ORDER_NUM_RES || '-' || s.ORDER_ITEM_RES as res_und_order, p.ORDER_NUMBER from WC_MAIN m left join WC_4_8_PRIEMKA p on m.SHOP_ID = p.SHOP_ID and m.PIPE_YEAR = p.PIPE_YEAR and m.PIPE_NUMBER = p.PIPE_NUMBER left join WC_7_3_SGP s on m.SHOP_ID = s.SHOP_ID and m.PIPE_YEAR = s.PIPE_YEAR and m.PIPE_NUMBER = s.PIPE_NUMBER

Here is a request for an example. Conditions remain the same as I described above. Dates are just p.CREATE_DATE and s.CREATE_DATE . s is the date of the first item, and p is the date of the second.

  • @Mike help please with this question. - Andrey
  • 2
    90% of this request (if not 100) is irrelevant. Try to simplify the request, create a reproducible example in which there will be nothing superfluous and clearly articulate the requirements. Questions like: “How to make everything work?” Are too wide for this site - default locale
  • one
    @defaultlocale I think I understand you, approximately it should look different, I added another request to the question for simplification. It contains a couple of extra fields, I added them there because they are needed in the conditions for adding a new field with the help of cases. In general, for clarity. - Andrey
  • one
    @ Andrei, you yourself understand that in your first script? format it normally so that it is immediately understandable. With this script, as in the first part, there is only a desire to close the tab with the question. - Denis
  • one
    @defaultlocale I don’t think it’s okay to get an option, there’s the whole problem with the dates. These dates are very difficult to find. In the first sample query, if you read it, there are 2 fields that are formed under the queries. And there is a twitching date. And for her + one more I need to make a condition in the case for the new field. I do not understand how to issue the same nesting here in the case. - Andrey

1 answer 1

In the first sample query, if you read it, there are 2 fields that are formed under the queries. And there is a twitching date. And for her + one more I need to make a condition in the case for the new field.

If the only hitch is to use the fields that are selected by subqueries several times, then try to select all the necessary fields and refer to the result as a whole:

 select * from ( select m.PIPE_NUMBER, ... здесь весь первый запрос ) res

Thus, the fields received in the first request will be available for operation and can be further processed; add a field for example:

 select res.*, (case when res.DATE_CANCEL_RESERV > res.CREATE_DATE and ... еще условия then 'Подтверждено' when ... then 'Не подтверждено' else 'Активно' end) status from ( select m.PIPE_NUMBER, ... здесь весь первый запрос ) res