Settings and activity
3 results found
-
10 votes
Syltrem
shared this idea
·
-
43 votesSyltrem
supported this idea
·
An error occurred while saving the comment -
3 votesSyltrem
shared this idea
·
This would be very handy as I sometimes have UNION in a subquery so the whole query cannot be easily split into blocks that will be editable in Query builder.
Example :
Create or Replace View SIQ_V_FINISHED_GOODS_SUM
as Select nvl(ALLOCATED.PFC_PRODUCT, INVENTORY.PFC_PRODUCT) PFC_PRODUCT,
ALLOCATED.WEIGHT ALLOCATED_WEIGHT, INVENTORY.WEIGHT INVENTORY_WEIGHT
From (Select PFCF101.PFC_PRODUCT, sum(PFCF101.PFC_QTY_TO_SHIP * PFCF101.PFC_WEIGHT) WEIGHT
From SIQRMS_PFCF100 PFCF100
INNER JOIN SIQRMS_PFCF101 PFCF101 ON PFCF100.PFC_BO_SEQUENCE = PFCF101.PFC_BO_SEQUENCE
AND PFCF100.PFC_ORDER_NO = PFCF101.PFC_ORDER_NO
Where PFCF100.PFC_ORDER_STATUS in ('1','2')
and PFCF100.PFC_ORDER_TYPE = '1'
Group By PFCF101.PFC_PRODUCT) ALLOCATED
Full Outer Join ( -- Inventaire Fil
Select PFCF001_FIL.PFC_PRODUCT,
sum(PROF413SIQ.PRO_ETIQUET_POIDS * PFCF001_FIL.PFC_WEIGHT) WEIGHT
From SIQRMS_PROF413SIQ PROF413SIQ
Inner Join SIQRMS_PROF400SIQ PROF400SIQ
On PROF400SIQ.PFC_ITEM_PROD_NO = PROF413SIQ.PFC_ITEM_PROD_NO
Inner Join SIQRMS_PFCF001 PFCF001_FIL
On PFCF001_FIL.PFC_PRODUCT = PROF400SIQ.PFC_PRODUCT
Where ( PRO_ETIQ_DISPONIBILITE = ' '
or PRO_ETIQ_DISPONIBILITE = 'T')
And substr(PFCF001_FIL.PFC_PRODUCT,1,1) <> '1' -- Juste le fil
and PRO_ETIQ_ITEM_PERDU <> 'O'
Group By PFCF001_FIL.PFC_PRODUCT
Union -- Inventaire Clous
Select INVF001.PFC_PRODUCT, SUM(INV_IN_STOCK * PFCF001_CLOUS.PFC_WEIGHT) WEIGHT
From SIQRMS_INVF001 INVF001
Inner Join SIQRMS_PFCF001 PFCF001_CLOUS
On PFCF001_CLOUS.PFC_PRODUCT = INVF001.PFC_PRODUCT
Where substr(INVF001.PFC_PRODUCT,1,1) = '1'
Group By INVF001.PFC_PRODUCT
) INVENTORY
On ALLOCATED.PFC_PRODUCT = INVENTORY.PFC_PRODUCT
/