Skip to main content
Question

Connecting SQL Select statements

  • September 25, 2024
  • 3 replies
  • 113 views

Forum|alt.badge.img+9
  • Sidekick (Customer)
  • 165 replies

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.

3 replies

Marcel.Ausan
Ultimate Hero (Partner)
Forum|alt.badge.img+22
  • Ultimate Hero (Partner)
  • 1292 replies
  • September 25, 2024

@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.

Forum|alt.badge.img+9
  • Author
  • Sidekick (Customer)
  • 165 replies
  • October 14, 2024

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


Forum|alt.badge.img+9
  • Author
  • Sidekick (Customer)
  • 165 replies
  • October 14, 2024

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings