# Erweiterung der SQL-Anfrage um Lieferland in Versandmodul nach Doku:  Unknown column

**URL:** <https://forum.shopware.com/t/erweiterung-der-sql-anfrage-um-lieferland-in-versandmodul-nach-doku-unknown-column/65075>\
**Category:** Programmierung\
**Created:** [28. Februar 2020 um 12:50 UTC](https://forum.shopware.com/t/erweiterung-der-sql-anfrage-um-lieferland-in-versandmodul-nach-doku-unknown-column/65075 "2020-02-28T12:50:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![rreimche](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@rreimche](https://forum.shopware.com/u/rreimche)\
**Post date:** [28. Februar 2020 um 12:50 UTC](https://forum.shopware.com/t/erweiterung-der-sql-anfrage-um-lieferland-in-versandmodul-nach-doku-unknown-column/65075/1 "2020-02-28T12:50:03Z")

</div>

Hallo \*,

ich möchte in eigener Berechnung der Versandkosten das Lieferland verwenden. [Laut der Doku](https://docs.shopware.com/de/shopware-5-de/versand-und-zahlungsarten/individuelle-versandkosten#laender) habe ich (das ist praktisch Copy-Paste aus der Doku):

```
(
      SELECT
        countryiso
      FROM
        s_core_countries
      WHERE
        id = us.country_id
) 
AS lieferland

```

Leider verursacht das die MySQL-Exception:

```
... SQLSTATE(42S22): Column not found: 1054 Unknown column 'lieferland' in 'field list' in ...

```

Die ganze problematische Query:

```
SELECT
  MIN(d.instock >= b.quantity) as instock,
  MIN(d.instock >=(b.quantity + d.stockmin)) as stockmin,
  MIN(a.laststock) as laststock,
  SUM(d.weight * b.quantity) as weight,
  SUM(IF(a.id, b.quantity, 0)) as count_article,
  MAX(b.shippingfree) as shippingfree,
  SUM(
    IF(
      b.modus = 0,
      b.quantity * CAST(b.price as DECIMAL(10, 2)) / b.currencyFactor,
      0
    )
  ) as amount,
  SUM(
    IF(
      b.modus = 0,
      b.quantity * ROUND(CAST(b.price as DECIMAL(10, 2)) /(100 + t.tax) * 100, 2) / b.currencyFactor,
      0
    )
  ) as amount_net,
  SUM(CAST(b.price as DECIMAL(10, 2)) * b.quantity) as amount_display,
  MAX(d.length) as `length`,
  MAX(d.height) as height,
  MAX(d.width) as width,
  u.id as userID,
  MAX(a.topseller) as has_topseller,
  MAX(at.attr3) as has_comment,
  MAX(b.esdarticle) as has_esd,
  
  (
      SELECT
        countryiso
      FROM
        s_core_countries
      WHERE
        id = us.country_id
  )
  AS lieferland,
  MIN(
    (
      SELECT
        1
      FROM
        s_articles_categories
      WHERE
        articleID = a.id
        AND categoryID = 7
    )
  ) AS contains_moebel,
  COUNT(
    (
      SELECT
        1
      FROM
        s_articles_categories
      WHERE
        articleID = a.id
        AND categoryID = 146
    )
  ) AS anzahl_tassenkomplekte,
  COUNT(
    (
      SELECT
        1
      FROM
        s_articles_categories
      WHERE
        articleID = a.id
        AND categoryID = 10
    )
  ) AS anzahl_gutscheine,
  COUNT(a.id) as anzahl_produkte,
  (
    CASE WHEN lieferland = 'DE' THEN 4.95 * (1 + anzahl_tassenkomplekte) WHEN lieferland = 'AT' THEN 7.95 * (1 + anzahl_tassenkomplekte) WHEN anzahl_tassenkomplekte = 0 THEN CASE WHEN lieferland = 'NL' THEN 7.95 WHEN lieferland = 'DK' || lieferland = 'FR' || lieferland = 'IT' || lieferland = 'IE' THEN 9.95 WHEN lieferland = 'ES' || lieferland = 'GB' || lieferland = 'LU' THEN 10.95 WHEN lieferland = 'CH' THEN 19.95 else 999 END else 999 END
  ) as calculation_value_10,
  (
    6 + (
      CASE WHEN lieferland = 'DE' THEN 4.95 * (1 + anzahl_tassenkomplekte) WHEN lieferland = 'AT' THEN 7.95 * (1 + anzahl_tassenkomplekte) WHEN anzahl_tassenkomplekte = 0 THEN CASE WHEN lieferland = 'NL' THEN 7.95 WHEN lieferland = 'DK' || lieferland = 'FR' || lieferland = 'IT' || lieferland = 'IE' THEN 9.95 WHEN lieferland = 'ES' || lieferland = 'GB' || lieferland = 'LU' THEN 10.95 WHEN lieferland = 'CH' THEN 19.95 else 999 END else 999 END
    )
  ) as calculation_value_11,
  ('') as calculation_value_12,
  ('') as calculation_value_13,
  ('') as calculation_value_14,
  ('') as calculation_value_15
FROM
  s_order_basket b
  LEFT JOIN s_articles a ON b.articleID = a.id
  AND b.modus = 0
  AND b.esdarticle = 0
  LEFT JOIN s_user u ON u.id = null
  AND u.active = 1
  LEFT JOIN s_articles_details d ON (d.ordernumber = b.ordernumber)
  AND d.articleID = a.id
  LEFT JOIN s_core_tax t ON t.id = a.taxID
  LEFT JOIN s_articles_attributes at ON at.articledetailsID = d.id
  LEFT JOIN s_user_addresses ub ON ub.user_id = u.id
  AND ub.id = 0
  LEFT JOIN s_user_addresses us ON us.user_id = u.id
  AND us.id = 0
WHERE
  b.sessionID = "blabla"
GROUP BY
  b.sessionID

```

Vielen Dank im Vorab

MfG  
Roman

---

<div class="post-metadata">

**Author:** ![rreimche](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@rreimche](https://forum.shopware.com/u/rreimche)\
**Post date:** [28. Februar 2020 um 12:58 UTC](https://forum.shopware.com/t/erweiterung-der-sql-anfrage-um-lieferland-in-versandmodul-nach-doku-unknown-column/65075/2 "2020-02-28T12:58:59Z")

</div>

Kann es sein, dass der Fehler sich nicht auf die Erweiterung bezieht, sonder auf die Eigene Berechnungen, die in der komplette SQL-Anfrage so aussehen (das ist ein Auszug aus der kompletten Anfrage oben)?

```
(
    CASE WHEN lieferland = 'DE' THEN 4.95 * (1 + anzahl_tassenkomplekte) WHEN lieferland = 'AT' THEN 7.95 * (1 + anzahl_tassenkomplekte) WHEN anzahl_tassenkomplekte = 0 THEN CASE WHEN lieferland = 'NL' THEN 7.95 WHEN lieferland = 'DK' || lieferland = 'FR' || lieferland = 'IT' || lieferland = 'IE' THEN 9.95 WHEN lieferland = 'ES' || lieferland = 'GB' || lieferland = 'LU' THEN 10.95 WHEN lieferland = 'CH' THEN 19.95 else 999 END else 999 END
  ) as calculation_value_10,
  (
    6 + (
      CASE WHEN lieferland = 'DE' THEN 4.95 * (1 + anzahl_tassenkomplekte) WHEN lieferland = 'AT' THEN 7.95 * (1 + anzahl_tassenkomplekte) WHEN anzahl_tassenkomplekte = 0 THEN CASE WHEN lieferland = 'NL' THEN 7.95 WHEN lieferland = 'DK' || lieferland = 'FR' || lieferland = 'IT' || lieferland = 'IE' THEN 9.95 WHEN lieferland = 'ES' || lieferland = 'GB' || lieferland = 'LU' THEN 10.95 WHEN lieferland = 'CH' THEN 19.95 else 999 END else 999 END
    )as calculation_value_11,

```

&nbsp;

---

<div class="post-metadata">

**Author:** ![sacrofano](https://avatars.discourse-cdn.com/v4/letter/s/65b543/32.png) [@sacrofano](https://forum.shopware.com/u/sacrofano)\
**Post date:** [3. März 2020 um 16:16 UTC](https://forum.shopware.com/t/erweiterung-der-sql-anfrage-um-lieferland-in-versandmodul-nach-doku-unknown-column/65075/3 "2020-03-03T16:16:57Z")

</div>

Genau, der Fehler bezieht sich auf die Verwendung in der case Anweisung.

---

<div class="post-metadata">

**Author:** ![rreimche](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@rreimche](https://forum.shopware.com/u/rreimche)\
**Post date:** [5. März 2020 um 13:32 UTC](https://forum.shopware.com/t/erweiterung-der-sql-anfrage-um-lieferland-in-versandmodul-nach-doku-unknown-column/65075/4 "2020-03-05T13:32:24Z")

</div>

Wie Shopware Support erklärt hat, darf man keine Aliase in Eigenen Berechnungen verwenden, sondern muss dort die SQL-Anfragen komplett schreiben.