Magento Shipping country ID is shown as NULL in the reports

[sandeep_salim]

Hi All,

I'm using PostgreSQL in my data ware house for my reporting purposes, for a certain report i have to show the sum of revenue for each month grouped by country.But when i pulled the report the shipping_country_id is showing as null for the last two months(previous months it seems fine - Facing the problem from 2019,April onwards).

When i cross checked there are about 36000 orders having the shipping country ID as null in the database side, but in the magento portal it is showing correctly.

So i'm not sure the table where which im taking the data is correct or not ? If there is any alternative tables or columns to look for?

Any help will be deeply appreciated ! Thanks in advance.

Note: currently using magento 1.7 (in the process of upgrading it to 2)

Below is the query i'm using,

select shipping_address_id,billing_address_id from  sales_flat_order where entity_id=557273 ;
--- The result of this query is 
    1074666(shipping_address_id),1074667(billing_address_id)

SELECT * FROM sales_flat_order_address   where entity_id =   1074666 or entity_id=1074667 ;
-- The above query is showing nothing(Expected to show a row with shipping country details , but no row is coming).

[Vimal Kumar · Accepted solution]

Hi @sandeep_salim,

Try the following queries which I written for you to check the data and comparison. 

SELECT * FROM `sales_flat_order_address` where entity_id in(1074666,1074667);

SELECT b.* from sales_flat_order as a join `sales_flat_order_address` as b on a.shipping_address_id=b.entity_id where a.entity_id = 557273; 

SELECT b.* from sales_flat_order as a join `sales_flat_order_address` as b on a.billing_address_id=b.entity_id where a.entity_id = 557273;

SELECT b.* from sales_flat_order as a join `sales_flat_order_address` as b on a.billing_address_id=b.entity_id limit 50;

SELECT b.* from sales_flat_order as a join `sales_flat_order_address` as b on a.shipping_address_id=b.entity_id limit 50;

I hope it will help you!