1

I try to get shipping postcode, shipping city, billing postcode, billing city of order, join to table sales_order_address, but i can't use "CASE WHEN" on Join. Can someone help me, please.

This is my code,

$a = new \Zend_Db_Expr('CASE WHEN sales_order_address.address_type <> "shipping" THEN street END');
$b = 'CASE WHEN sales_order_address.address_type <> "shipping" THEN postcode END';
$c = 'CASE WHEN sales_order_address.address_type <> "shipping" THEN city END';
$d = 'CASE WHEN sales_order_address.address_type <> "billing" THEN street END';
$e = 'CASE WHEN sales_order_address.address_type <> "billing" THEN postcode END';
$f = 'CASE WHEN sales_order_address.address_type <> "billing" THEN city END';

$this->getSelect()
        ->joinLeft(
            $joinTable . ' as cgf',
            'main_table.entity_id = cgf.order_id',
            ['invoices' => 'cgf.increment_id']
        )
        ->joinLeft(
            'sales_order_address', 
            'main_table.entity_id = sales_order_address.parent_id',
            array(
                'country_id' => 'country_id', 
                'shipping_street' => $a,  
                'shipping_postcode' => $b, 
                'shipping_city' => $c, 
                'billing_street' => $d, 
                'billing_postcode' => $e, 
                'billing_city' => $f
            )
        )
->where('main_table.store_id = ' . $storeId);

Echo select, it show

SELECT `main_table`.*, `cgf`.`increment_id` AS `invoices`, `sales_order_address`.`country_id`, `CASE WHEN sales_order_address`.`address_type <> "shipping" THEN street END`, `CASE WHEN sales_order_address`.`address_type <> "shipping" THEN postcode END` AS `shipping_postcode`, `CASE WHEN sales_order_address`.`address_type <> "shipping" THEN city END` AS `shipping_city`, `CASE WHEN sales_order_address`.`address_type <> "billing" THEN street END` AS `billing_street`, `CASE WHEN sales_order_address`.`address_type <> "billing" THEN postcode END` AS `billing_postcode`, `CASE WHEN sales_order_address`.`address_type <> "billing" THEN city END` AS `billing_city` FROM `sales_order_grid` AS `main_table` LEFT JOIN `sales_invoice_grid` AS `cgf` ON main_table.entity_id = cgf.order_id LEFT JOIN `sales_order_address` ON main_table.entity_id = sales_order_address.parent_id WHERE (main_table.store_id = 2)

You can see there error, there shouldn't have ` before CASE and after END

`CASE WHEN sales_order_address`.`address_type <> "billing" THEN postcode END` 

Please help me, or welcome for another way

Your Answer

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

Browse other questions tagged or ask your own question.