Skip to main content

Hello,

 

I need help in concatenating SQL queries. My aim is to connect 11 columns together in one view or table. First 6 columns is from one view, column 7, 8, 9, 10 and 11 are from different views. How can I connect this columns together? Should I use JOINs or UNION? Because in both cases I have problem.

 

My columns are: 

SELECT

        PART_PRODUCT_FAMILY "Skupina príbuzných výrobkov", 
        UNIT_MEAS "Merná jednotka", 
        ACCOUNTING_GROUP "Účtovná skupina", 
        PLANNING_METHOD "Metóda plánovania", 
        Purchase_Part_Supplier_Api.Get_Primary_Supplier_No(contract, part_no) AS "Primárny dodávateľ",
 Supplier_Info_Api.Get_Name(IFSAPP.Purchase_Part_Supplier_Api.Get_Primary_Supplier_No(contract, part_no)) "Názov primárneho dodávateľa"
FROM INV_PART_CONFIG_PROJECT_ALT

 

SELECT NVL(SUM(pole.QTY_SUPPLY),0) "Množstvo dodávok NO" FROM PURCHASE_ORDER_LINE_EXT pole WHERE pole.CONTRACT = x.contract AND pole.PART_NO = x.part_no
 

WITH OstDavka1 AS (                                                                                                                   
    SELECT NVL(SUM(osde.QTY_SUPPLY),0) AS total1 
    FROM ORDER_SUPPLY_DEMAND_EXT osde 
    WHERE osde.CONTRACT =  x.contract AND osde.PART_NO = x.part_no
),
OstDavka2 AS (
    SELECT NVL(SUM(lfe.QTY_SUPPLY),0) AS total2 
    FROM PURCHASE_ORDER_LINE_EXT lfe 
    WHERE lfe.CONTRACT = x.contract AND lfe.PART_NO = x.part_no
)
SELECT OstDavka1.total1 - OstDavka2.total2 Difference2 FROM OstDavka1, OstDavka2

 

Thanks.

@Ed22 you cannot use UNION as it will require the same number of columns in the select statements.

What you could do is:

  • create 4 subqueries - 2 you already have in the WITH Clause
  • then do an inner join on contract and part nu on all 4 subqueries.

I created SQL query and implement it into quick report, it has only 14000 rows but in quick report it is very slow, could you please give me advice how can I speed up this query? I created some indexes but it has the same speed

IN Oracle SQL Developer fetching all rows lasts aprox 45 seconds, but in quick report 45 seconds only 60-80 rows and also when i want to filter by for example part no

Reply


Terms & ConditionsCookie settings