สอบถามครับ ถ้าเราต้องการเอา Queries มาใช้Access แทนExcelยังไงครับ
select
case when o.city_id in (983099,4421,1,687622,1151444,169418,10,428520,457889,1180815,716991,198787,913628,11,558966,299864,818068,7,1250275,1306351,874101,15,355897,614999)
then 'Bangkok'
else c.city_name
end as "City",
o.order_date as "Order date",
o.status_date as "Delivery date",
o.order_id as "ID",
order_code_google as "Order code",
--right(order_code_google::) as "Code",
v.vendor_name as "Vendor",
o.gmv_eur*o.fx as "Total Order (local)",
o.gfv_eur as "Order Volume (EUR) excl. VAT",
o.gfv_eur*o.fx as "Order Volume (local)",
o.service_fee_eur*o.fx as "Service Fee",
o.delivery_fee_eur*o.fx as "Delivery Fee",
o.commission_eur as "Margin (EUR)",
case when o.gfv_eur = 0 then null else
commission_eur/gfv_eur end as "Margin %",
commission_eur*o.fx as "Revenue from Margin (local)",
o.discount_value_eur*o.fx as "Discount Value (local)",
o.voucher_value_eur*o.fx as "Voucher Value (local)",
case when o.status_id in (1) then '40 - open, wait for sms verification'
when o.status_id in (2) then '41 - open, auto inform vendor'
when o.status_id in (3) then '42 - order accepted, auto inform client'
when o.status_id in (33) then '49 - Autodispatch'
when o.status_id in (30) then '491 - Vendor accepted, autodispatch rider'
when o.status_id in (25) then '50 - Open, assign rider and add delivery time'
when o.status_id in (27) then '501 - Order accepted. Add delivery time'
when o.status_id in (4) then '51 - open, call vendor'
when o.status_id in (5) then '52 - order declined, contact customer'
when o.status_id in (6) then '53 - confirmation overdue, call vendor'
when o.status_id in (7) then '54 - order cancelled, call vendor'
when o.status_id in (8) then '55 - error, call vendor or customer'
when o.status_id in (9) then '56 - Potential duplicate order, call customer'
when o.status_id in (23) then '57 - Verify Order/Customer Information'
when o.status_id in (32) then '58 - Order delivery late, customer complained'
when o.status_id in (37) then '59 - Traffic manager declined, call customer'
when o.status_id in (41) then '591 - Traffic manager declined, call customer and vendor'
when o.status_id in (42) then '592 - Traffic manager cancelled, call customer and vendor'
when o.status_id in (10) then '61 - vendor informed, wait for confirmation'
when o.status_id in (34) then '613 - Rider arrived at restaurant'
when o.status_id in (35) then '614 - Order picked up'
when o.status_id in (36) then '615 - Rider arrived at customer'
when o.status_id in (11) then '62 - order accepted, customer informed'
when o.status_id in (24) then '621 - Order delivered'
when o.status_id in (39) then '622 - Customer confirmed delivery'
when o.status_id in (12) then '63 - vendor cancelled - customer informed'
when o.status_id in (38) then '631 - Traffic manager cancelled, customer and vendor informed'
when o.status_id in (13) then '64 - customer cancelled - vendor informed'
when o.status_id in (14) then '65 - order not delivered'
when o.status_id in (15) then '66 - Test order'
when o.status_id in (16) then '67 - Incomplete order'
when o.status_id in (43) then '679 - System Cancelled'
when o.status_id in (18) then '68 - Order replaced'
when o.status_id in (31) then '681 - Order outdated'
when o.status_id in (17) then '69 - Unifinished Order'
when o.status_id in (19) then '70 - Preorder waiting'
when o.status_id in (20) then '71 - Waiting online payment'
when o.status_id in (28) then '711 - Waiting online payment, customer informed'
when o.status_id in (29) then '712 - Waiting online payment, overdue'
when o.status_id in (22) then 'Order assembled'
when o.status_id in (21) then 'Rider informed'
else null end as "Status Code",
cu.first_order_date::date as "First Order Date",
null as "Delivery Time",
o.own_delivery as "Own Delivery",
o.paymenttype_id as "Payment type ID",
o.customer_id as "Customer ID",
null as "Voucher FP ratio",
v.vendor_code as "Vendor Code",
null as "Total Products",
dp.title as "Delivery Provider",
o.first_order_all as "New Customer",
null as "location_title",
o.gmv_eur*o.fx -
(case when o.voucher_value_eur is null then 0.00
else o.voucher_value_eur*o.fx end)
- (case when o.discount_value_eur is null then 0.00
else o.discount_value_eur*o.fx end)
as "Customer Pays Local",
null as "Container Price"
from il_country_th.v_fct_orders o
---left join merge_layer_rdbms.orders mo on o.order_id=mo.id and o.rdbms_id = mo.rdbms_id
left join il_country_th.v_dim_city c on o.city_id =c.city_id and o.rdbms_id=c.rdbms_id
left join il_country_th.v_dim_vendors v on o.vendor_id = v.vendor_id and o.rdbms_id = v.rdbms_id
left join il_country_th.v_dim_deliveryprovider dp on o.deliveryprovider_id = dp.deliveryprovider_id and o.rdbms_id=dp.rdbms_id
left join il_country_th.v_dim_customer cu on o.customer_id = cu.customer_id and o.rdbms_id = cu.rdbms_id
---left join dwh_il_fo.v1_dim_location l on o.location_id=l.location_id and o.rdbms_id=l.rdbms_id
where o.rdbms_id=17
and o.status_date::date between ? and ?
and v.vendor_name not ilike '%test restaurant%' and v.vendor_name not ilike '%do not touch%' and v.vendor_name not ilike '%foodpanda%'
พอดีต้องการดึงข้อมูลมาแต่ EXCEL ไม่สามารถดึงRows เกิน1m ได้
จึงต้องการดึงจากในACCESSแทน
รบกวนผู้รู้ด้วยครับ
ขอบคุณครับ
