Versandkostenberechnung nach Regionen Erweiterte SQL Abfrage

Hallo, 

ich versuche eine Versandkostenberechnung nach Regionen (AreaID) durch zuführen. Dafür habe ich in der Versandkostenberechnung folgendes ergänzt:

(SELECT areaID FROM s_core_countries  WHERE id = us.countryID) AS areaId

In der Versandart habe ich unter eigene berechnung folgendes ergänzt:

 

IF(
    areaId = 1,
    4.99,
IF(
    areaId = 2,
    9.99,
IF(
    areaId = 3,
    14.99,
IF(
    areaId = 4,
    29.99,
))))

Leider lädt sich der Offcanvas Warenkorb mit dieser Ergänzung immer tot. Es scheint also was im Versandkostenmodul nicht zu stimmen. Der Vollständigkeit halber der ganze Eintrag in der erweiterten SQL-Abfrage: 

MAX(a.topseller) as has_topseller, MAX(at.attr3) as has_comment, MAX(b.esdarticle) as has_esd, MAX(at.att_sperrgut=„1“) AS sperrgut, MAX(d.shippingfree)  AS allshippingfree, MAX(at.att_spedition=„1“) AS spedition, (SELECT areaID FROM s_core_countries  WHERE id = us.countryID) AS areaId

Wenn ich meine Ergänzung rausnehme funktioniert es wieder Einwandfrei, habt ihr irgendwelche Ideen? Das Thema lässt mich langsam verzweifeln.

Beste Grüße,

Martin

Als Ergänzung hier mal noch die Fehlermeldung:

 

An exception occurred while executing ‘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, MAX(at.att_sperrgut=“1”) AS sperrgut, MAX(d.shippingfree) AS allshippingfree, MAX(at.att_spedition=“1”) AS spedition, (SELECT areaID FROM s_core_countries WHERE id = us.country_id) AS areaId, (IF( areaId = 1, 4.99, IF( areaId = 3, 9.99, IF( areaId = 4, 14.99, )))) as calculation_value_111, SUM(IF(b.modus=0 AND oba.swag_is_free_good_by_promotion_id IS NULL,b.quantity*CAST(b.price as DECIMAL(10,2))/b.currencyFactor,0)) as amount, SUM(IF(b.modus=0 AND oba.swag_is_free_good_by_promotion_id IS NULL,b.quantity*ROUND(CAST(b.price as DECIMAL(10,2))/(100+t.tax)*100,2)/b.currencyFactor,0)) as amount_net 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 = ? AND u.active = 1 LEFT JOIN s_order_basket_attributes oba ON b.id = oba.basketID 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 = ? LEFT JOIN s_user_addresses us ON us.user_id = u.id AND us.id = ? WHERE b.sessionID = ? GROUP BY b.sessionID’ with params [null, 0, 0, “fcaa8c74084bb454a0c99f1494df0a56f44d6267eeb5d5f6b0d34eb0db40eef2”]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘)))) as calculation_value_111, SUM(IF(b.modus=0 AND oba.swag_is_free_good_by_pro’ at line 10 in vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php on line 131

IF( areaId = 4, 14.99, ))))

Fällt dir was auf?

Viele Grüße
https://www.digitvision.de

1 Like

Hi Eike,

ja, hinter 14,99 fehlt natürlich noch was. Beim vielen Testen irgendwie untergegangen, das war aber leider nicht die Ursache des Problems.

Falls die Lösung mal einen interessieren sollte, so läuft es:

IF(
    @areaId = 1,
    4.99,
IF(
    @areaId = 3,
    9.99,
IF(
    @areaId = 4,
    14.99,
    29.99
)))