It is necessary to display the following query:

enter image description here

That is, if there is a product in reserve, you need to display the document number on it.

Request text for outputting pallets:

SELECT pal_num, pal_prod, prd_desc, (CASE loc_blocked WHEN 0 THEN pal_loc ELSE pal_loc ||' блок' END) pal_loc, rst_desc, pal_batch, pal_vin, pal_cert, awacs_core.to_oradate(pal_bbdate), loc_name, loc_zone, pal_owner, pal_qty cases, pal_sqty singles, decode(pal_alloc, 1, pal_qty, pal_pickqty) pcases, decode(pal_alloc, 1, pal_sqty, pal_saqty) psingles FROM AWACS_PALVIEW, AWACS_PRD, awacs_loc, awacs_rst WHERE PAL_CUST = 'DIAGEO' AND PRD_PROD = PAL_PROD AND loc_name = pal_loc AND RST_CODE = PAL_RSTCODE AND PAL_CUST = PRD_CUST ORDER BY pal_num

How to make so that in the presence of packings in reserve not equal to zero, the substring with document number and quantity is displayed?

Request for bottom line:

 SELECT wrk_docref, wrk_qty, wrk_sqty FROM AWACS_WRK

Here are sample tables for a query.

  create table AWACS_PALVIEW (pal_num INTEGER,pal_prod INTEGER,pal_qty INTEGER, PAL_SQTY INTEGER); INSERT INTO AWACS_PALVIEW (pal_num,pal_prod,pal_qty,pal_sqty) VALUES (712005,124353,0,0); INSERT INTO AWACS_PALVIEW (pal_num,pal_prod,pal_qty,pal_sqty) VALUES (712015,1242343,1,0); INSERT INTO AWACS_PALVIEW (pal_num,pal_prod,pal_qty,pal_sqty) VALUES (789445,104399,0,0); create table AWACS_WRK (wrk_docref INTEGER, WRK_PALNUM INTEGER,WRK_QTY INTEGER, WRK_SQTY INTEGER); INSERT INTO AWACS_WRK (WRK_DOCREF,WRK_PALNUM,WRK_QTY,WRK_SQTY) VALUES (99999,712015,1,0); --- **Query #1** SELECT PAL_NUM,PAL_PROD,PAL_QTY,PAL_SQTY FROM AWACS_PALVIEW; | PAL_NUM | PAL_PROD | PAL_QTY | PAL_SQTY | | ------- | -------- | ------- | -------- | | 712005 | 124353 | 0 | 0 | | 712015 | 1242343 | 1 | 0 | | 789445 | 104399 | 0 | 0 | ---

View on DB Fiddle

It is necessary to output between the second and third line: From them on the invoice, WRK_DOCREF, WRK_QTY, WRK_SQTY

And at me it turns out that in the end the union

 SELECT PAL_NUM,PAL_PROD,PAL_QTY,PAL_SQTY FROM AWACS_PALVIEW UNION SELECT 'Из них по накладной',WRK_DOCREF,WRK_QTY,WRK_SQTY FROM AWACS_WRK

enter image description here

  • Are you comfortable working with a query on almost one line? PS your question is completely incomprehensible. - Denis
  • Thanks, I usually do that - Mishustiq
  • I need the bottom line to appear only when there are packages in reserve. That is, usually the request displays pallets to me, but when the pallet has a reserve, it should output the document number below, etc. - Mishustiq
  • And what is the actual problem then? - Viktorov
  • I do not know how to do it ((I think that through the union, but how incomprehensible is Mishustiq

1 answer 1

For a simplified example:

 select pal_num, PAL_PROD, PAL_QTY, PAL_SQTY from ( SELECT to_char(PAL_NUM) as pal_num, PAL_PROD, PAL_QTY, PAL_SQTY, 1 as rn, pal_num as pal_num_ordered FROM AWACS_PALVIEW union all SELECT 'Из них по накладной', wrk_docref, wrk_qty, wrk_sqty, 2 as rn, pal_num as pal_num_ordered FROM AWACS_WRK t1 inner join AWACS_PALVIEW t2 on t1.wrk_palnum=t2.pal_num ) order by pal_num_ordered, rn;