CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
4nch0r
4nch0r

Reputation: 33

GCP Big Quetry is causing unrecognized name and ambious column

I have a problem with a SQL and would appreciate your help. I have a SQL, this actually works well. Unfortunately, a new column is created when it runs. This is created automatically and is called "Material_1". This leads to the fact that the normal column "Material" can no longer be called ("ambigous name error"). But if I select the Material_1, I get another error: "unrecognized name" do you mean Material? I assume quite strongly that the rank has something to do with it, but my attempts to set it from >1 to >=2 or similar small adjustments have led to no success.

WITH vdl_tracking_daten AS (
  SELECT 
    Trackingnummer,
    Transportdienstleister,
    LogisticsCustomCode,
    ActivityTimestamp,
    Versandtage AS Laufzeit_Arbeitstage,
    CASE WHEN LogisticsCustomCode='C4b' THEN True ELSE NULL END AS Ablieferhindernisse
  FROM 
    `VDL_Tracking_Tab`  
),

sap_sendungsdaten AS (
  SELECT *,
    SAFE_CAST(SUBSTRING(Warenausgangsdatum, 1, 10) as date) as Warenausgangsdatum_dateform,
    belegart.Belegart_Bezeichnung AS Fakturaart_Bez,
    vers.VersandartBezeichnung as Versandart_Bez
  FROM 
    `SP_TrackingKey_Tab` as track
  LEFT JOIN 
    `SP_Belegart` as belegart ON track.Fakturaart = belegart.Belegart_ID
  LEFT JOIN 
    `SP_Versandart` as vers ON vers.VersandartNr = track.Versandart
  where track.Faktura = '700238966'
),


gewicht_vdl_daten AS (
  SELECT DISTINCT 
    Trackingnummer,
    Gewicht AS Gewicht_aus_Rechnung
  FROM 
    `UP_Invoices`
    
  UNION ALL
  SELECT DISTINCT
    Trackingnummer,
    Gewicht AS Gewicht_aus_Rechnung
  FROM 
    `GL_Invoices`
  
  UNION ALL
  SELECT DISTINCT 
    Trackingnummer,
    Gewicht AS Gewicht_aus_Rechnung
  FROM 
    `BS_Invoices`
),

Empfaenger_PLZ AS (
  SELECT DISTINCT 
    f.Faktura AS Faktura_Key,
    f.Material,
    f.Land AS Land_Warenemfaenger,
    f.PLZ AS PLZ_Warenempfaenger,
  FROM 
    `SP_Faktura_Tab` f
  where f.Faktura = '700238966'
)

select * from (
  SELECT 
  sap.Trackingnr as Trackingnummer,
  sap.Faktura,
  sap.Fakturaart_Bez,
  sap.Vertriebsbeleg,
  sap.HandlingUnit,
  sap.Liefernr,
  sap.Material,
  sap.Versandart_Bez,
  sap.VersandartTyp,
  sap.Lagerort_Text as Lagerort,
  CAST(LEFT(sap.Warenausgangsdatum, 10) AS DATE) AS Warenausgangsdatum,
  PARSE_TIME('%H%M%S', REGEXP_REPLACE(sap.Warenausgangszeit, r'[^\d]+', '')) AS Warenausgangszeit,
  tracking.LogisticsCustomCode,
  tracking.ActivityTimestamp,
  CASE
    WHEN tracking.LogisticsCustomCode IN ('C4', 'C4a') THEN tracking.ActivityTimestamp
    ELSE NULL 
  END AS Zeitstempel_Zustellung,
  empf.* EXCEPT (Faktura_Key),

  row_number() OVER(PARTITION BY sap.TrackingNr, sap.Material ORDER BY sap.TrackingNr desc, sap.Material desc) as rank


  FROM 
    sap_sendungsdaten AS sap
  LEFT JOIN 
    vdl_tracking_daten AS tracking ON tracking.Trackingnummer = sap.Trackingnr
  LEFT JOIN 
    gewicht_vdl_daten AS gewicht ON gewicht.Trackingnummer = sap.Trackingnr
  LEFT JOIN 
    Empfaenger_PLZ  AS empf ON sap.Faktura = empf.Faktura_Key

)
where rank >= 2

enter image description here enter image description here

Upvotes: 0

Views: 174

Answers (1)

4nch0r
4nch0r

Reputation: 33

empf.* EXCEPT (Faktura_Key) this caused the error. f.Material AS empf_Material fixed the problem

Upvotes: 1

Related Questions