Hallo *,
ich möchte in eigener Berechnung der Versandkosten das Lieferland verwenden. Laut der Doku 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