2

When running the CTE below, it fetchs me the data I want.

WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
          a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

However when I'm trying to create a view on it like below

CREATE VIEW toto AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

It gives me the following error

ERROR: The column « Site » has been specified more than once

********** Error **********

ERROR: The column « Site » has been specified more than once WSQL State :42701

I've tried to put the view name before Site, namely a or d or "Axis_Reference_All" but none of it works.

Questions are the following:

  • Is CTE allowed with view creation on postgres? I guess no but it is unclear.

  • What would be the best way to create a view with my CTE? That CTE has been built that way because it is improving performance.

  • Difficult to say without knowing what the tables look like, what are the column names for your first query? – Lennart Aug 20 '18 at 14:15
1

There are two possible solutions

Do a USING join like below

CREATE VIEW report_view
AS 
WITH d as(
SELECT *
FROM report.get_sa001('2013-01-01'::date", TO_CHAR(NOW()", 'YYYY-MM-DD')::date", 32)
    )",
     a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site"", "Reference_internal"", "Customer_code") IN
       ( SELECT "Site"",
                    "Internal_reference"", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a USING ("Site","Internal_reference","Customer_code")
-- here is the USING. Courtesy of Jack Douglas for the tip

However as I'm retrieving datas from 5 years ago, it takes more than 5 minutes if I'm using a USING.

The best way is to call each columns separately like below and be sure to name the origin of the column, here a for Site and Customer_code.

CREATE VIEW report_view AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2015-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site", "Reference_internal", "Customer_code") IN
       ( SELECT "Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT "Period_date",
       a."Site",
       a."Customer_code",
       "Internal_reference",
       "InvoiceNumber",
       "Value_in_currency",
       "Value_in_EUR",
       "Value_Budget_in_EUR",
       "Selling_price_CUR",
       "Selling_price_EUR",
       "Currency_code",
       "Selling_quantity",
       "Variance_price_CUR",
       "Variance_price_EUR",
       "Variance_value_CUR",
       "Variance_value_EUR",
       "Selling_date",
       "Reference",
       "Reference_internal",
       "Reference_customer",
       "Reference_supplier",
       "Reference_description",
       "Reference_workshop",
       "Reference_line",
       "Reference_segment",
       "Reference_purchasingfamily",
       "Reference_motorapplication",
       "Reference_interco",
       "Reference_trading",
       "Cogs_rm",
       "Cogs_dl",
       "Cogs_voh",
       "Supplier_global",
       "Supplier_code",
       "Supplier_name",
       "Supplier_account_manager",
       "Supplier_interco",
       "Supplier_incoterm",
       "Supplier_incoterm_location",
       "Supplier_incoterm_via",
       "Supplier_continent",
       "Supplier_country",
       "Supplier_city",
       "Supplier_zipcode",
       "Purchasing_unit",
       "Purchasing_price",
       "Purchasing_currency",
       "Purchasing_payment_term_days",
       "Purchasing_payment_term_type",
       "Purchasing_consigned",
       "Purchasing_grossweight",
       "Purchasing_grosscube",
       "Purchasing_eco_order_qty",
       "Purchasing_pack_order_qty",
       "Purchasing_moq",
       "Purchasing_mov",
       "Purchasing_leadtime_days",
       "Customer_global",
       "Customer_name",
       "Customer_account_manager",
       "Customer_interco",
       "Customer_incoterm",
       "Customer_incoterm_location",
       "Customer_incoterm_via",
       "Customer_continent",
       "Customer_country",
       "Customer_city",
       "Customer_zipcode",
       "Selling_unit",
       "Selling_price",
       "Selling_currency",
       "Selling_payment_term_days",
       "Selling_payment_term_type",
       "Selling_consigned",
       "Selling_grossweight",
       "Selling_grosscube",
       "Selling_eco_order_qty",
       "Selling_pack_order_qty",
       "Selling_moq",
       "Selling_mov",
       "Selling_leadtime_days",
       "Reference_netweight",
       "Sc_storage_unit",
       "Sc_production_unit",
       "Sc_inventory_status",
       "Sc_inventory_price",
       "Sc_inventory_type"
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code"

This will give the required speed in term of data retrieval.

Cheers

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.