The Ultimate Guide to High Availability with MariaDB

Comments - TSQL To Maria DB SQL

3 years, 4 months ago Anthony Apollis
Re: TSQL To Maria DB SQL

Is my forematting wrong? Cant seem to run this code in Maria DB, even though i changed the data types ect.

CREATE TABLE IF NOT EXISTS 'Order'(
	CREATE TABLE IF NOT EXISTS 'Order'(
	uid BIGINT NULL,
	delivery_address VARCHAR(256) NULL,
	complex_unit_no VARCHAR(200) NULL,
	delivery_latitude VARCHAR(20) NULL,
	delivery_longitude VARCHAR(20) NULL,
	store_uid BIGINT NULL,
	app_version VARCHAR(10) NULL,
	delivery_fee VARCHAR(10) NULL,
	amended_delivery_fee VARCHAR(10) NULL,
	bottles_fee VARCHAR(10) NULL,
	amended_bottles_fee VARCHAR(10) NULL,
	promo_code VARCHAR(50) NULL,
	promo_discount VARCHAR(10) NULL,
	promo_discount_amount VARCHAR(10) NULL,
	promo_discount_type VARCHAR(10) NULL,
	user_agent_string VARCHAR(512) NULL,
	amended_promo_code VARCHAR(50) NULL,
	amended_promo_discount VARCHAR(10) NULL,
	amended_promo_discount_amount VARCHAR(10) NULL,
	amended_promo_discount_type VARCHAR(10) NULL,
	delivery_notes VARCHAR(max) NULL,
	items_total VARCHAR(30) NULL,
	amended_items_total VARCHAR(10) NULL,
	order_total VARCHAR(10) NULL,
	this_order_total VARCHAR(10) NULL,
	amended_order_total VARCHAR(30) NULL,
	adjusted_items_total VARCHAR(30) NULL,
	adjusted_DATETIME DATETIME NULL,
	adjusted_order_total VARCHAR(10) NULL,
	outstanding_amount VARCHAR(10) NULL,
	crc VARCHAR(10) NULL,
	created_DATETIME DATETIME NULL,
	placed_DATETIME DATETIME NULL,
	transaction_sequence VARCHAR(20) NULL,
	user_uid int NULL,
	status VARCHAR(3) NULL,
	accepted_DATETIME DATETIME NULL,
	picking_DATETIME DATETIME NULL,
	edi_order_no VARCHAR(50) NULL,
	edi_invoice_status VARCHAR(7) NULL,
	edi_invoice_no VARCHAR(50) NULL,
	edi_message_response VARCHAR(500) NULL,
	last_status_changed_DATETIME DATETIME NULL,
	edi_credit_status VARCHAR(7) NULL,
	amended_DATETIME DATETIME NULL,
	vendor_amended_DATETIME DATETIME NULL,
	vendor_viewed_DATETIME DATETIME NULL,
	user_amending_DATETIME DATETIME NULL,
	user_substituted VARCHAR(1) NULL,
	rating VARCHAR(1) NULL,
	rating_comment VARCHAR(2000) NULL,
	confirmed_DATETIME DATETIME NULL,
	delivered_DATETIME DATETIME NULL,
	delivery_integration_enabled VARCHAR(1) NULL,
	paid_in_full VARCHAR(1) NULL,
	driver_name VARCHAR(80) NULL,
	driver_contact_no VARCHAR(40) NULL,
	invoice_no VARCHAR(20) NULL,
	ppay_requested VARCHAR(1) NULL,
	ppay_payment_id VARCHAR(50) NULL,
	driver_cancelled VARCHAR(1) NULL,
	bottles_notes VARCHAR(150) NULL,
	bottles_agent_name VARCHAR(50) NULL,
	driver_started_delivery VARCHAR(1) NULL,
	driver_tracking_link VARCHAR(300) NULL,
	vendor_need_help VARCHAR(1) NULL,
	has_vendor_viewed_order VARCHAR(1) NULL,
	picking_slip_pn_sent VARCHAR(20) NULL,
	vendor_ready_for_driver VARCHAR(1) NULL,
	driver_ready_DATETIME DATETIME NULL,
	driver_status VARCHAR(17) NULL,
	vendor_driver_collected VARCHAR(1) NULL,
	delivery_integration_service_name VARCHAR(6) NULL,
	show_driver_tracking VARCHAR(1) NULL,
	payment_method VARCHAR(4) NULL,
	possible_fraud VARCHAR(1) NULL,
	invoice_status VARCHAR(8) NULL,
	driver_tip numeric(10, 2) NULL,
	order_type VARCHAR(2) NULL,
	picker_name VARCHAR(100) NULL
);
	delivery_address VARCHAR(256) NULL,
	complex_unit_no VARCHAR(200) NULL,
	delivery_latitude VARCHAR(20) NULL,
	delivery_longitude VARCHAR(20) NULL,
	store_uid BIGINT NULL,
	app_version VARCHAR(10) NULL,
	delivery_fee VARCHAR(10) NULL,
	amended_delivery_fee VARCHAR(10) NULL,
	bottles_fee VARCHAR(10) NULL,
	amended_bottles_fee VARCHAR(10) NULL,
	promo_code VARCHAR(50) NULL,
	promo_discount VARCHAR(10) NULL,
	promo_discount_amount VARCHAR(10) NULL,
	promo_discount_type VARCHAR(10) NULL,
	user_agent_string VARCHAR(512) NULL,
	amended_promo_code VARCHAR(50) NULL,
	amended_promo_discount VARCHAR(10) NULL,
	amended_promo_discount_amount VARCHAR(10) NULL,
	amended_promo_discount_type VARCHAR(10) NULL,
	delivery_notes VARCHAR(max) NULL,
	items_total VARCHAR(30) NULL,
	amended_items_total VARCHAR(10) NULL,
	order_total VARCHAR(10) NULL,
	this_order_total VARCHAR(10) NULL,
	amended_order_total VARCHAR(30) NULL,
	adjusted_items_total VARCHAR(30) NULL,
	adjusted_DATETIME DATETIME NULL,
	adjusted_order_total VARCHAR(10) NULL,
	outstanding_amount VARCHAR(10) NULL,
	crc VARCHAR(10) NULL,
	created_DATETIME DATETIME NULL,
	placed_DATETIME DATETIME NULL,
	transaction_sequence VARCHAR(20) NULL,
	user_uid int NULL,
	status VARCHAR(3) NULL,
	accepted_DATETIME DATETIME NULL,
	picking_DATETIME DATETIME NULL,
	edi_order_no VARCHAR(50) NULL,
	edi_invoice_status VARCHAR(7) NULL,
	edi_invoice_no VARCHAR(50) NULL,
	edi_message_response VARCHAR(500) NULL,
	last_status_changed_DATETIME DATETIME NULL,
	edi_credit_status VARCHAR(7) NULL,
	amended_DATETIME DATETIME NULL,
	vendor_amended_DATETIME DATETIME NULL,
	vendor_viewed_DATETIME DATETIME NULL,
	user_amending_DATETIME DATETIME NULL,
	user_substituted VARCHAR(1) NULL,
	rating VARCHAR(1) NULL,
	rating_comment VARCHAR(2000) NULL,
	confirmed_DATETIME DATETIME NULL,
	delivered_DATETIME DATETIME NULL,
	delivery_integration_enabled VARCHAR(1) NULL,
	paid_in_full VARCHAR(1) NULL,
	driver_name VARCHAR(80) NULL,
	driver_contact_no VARCHAR(40) NULL,
	invoice_no VARCHAR(20) NULL,
	ppay_requested VARCHAR(1) NULL,
	ppay_payment_id VARCHAR(50) NULL,
	driver_cancelled VARCHAR(1) NULL,
	bottles_notes VARCHAR(150) NULL,
	bottles_agent_name VARCHAR(50) NULL,
	driver_started_delivery VARCHAR(1) NULL,
	driver_tracking_link VARCHAR(300) NULL,
	vendor_need_help VARCHAR(1) NULL,
	has_vendor_viewed_order VARCHAR(1) NULL,
	picking_slip_pn_sent VARCHAR(20) NULL,
	vendor_ready_for_driver VARCHAR(1) NULL,
	driver_ready_DATETIME DATETIME NULL,
	driver_status VARCHAR(17) NULL,
	vendor_driver_collected VARCHAR(1) NULL,
	delivery_integration_service_name VARCHAR(6) NULL,
	show_driver_tracking VARCHAR(1) NULL,
	payment_method VARCHAR(4) NULL,
	possible_fraud VARCHAR(1) NULL,
	invoice_status VARCHAR(8) NULL,
	driver_tip Decimal (10, 2) NULL,
	order_type VARCHAR(2) NULL,
	picker_name VARCHAR(100) NULL
);
 
3 years, 4 months ago Ian Gilfillan
Re: TSQL To Maria DB SQL [Answer]

Your formatting of the code in the question was certainly hard to read, but I've fixed that for you :) FYI, you can preview the comment, and use the "editing help" link visible when you are typing. In this case, wrapping <<code>> or <<sql>> tags around your sample code makes it much more readable.

As to the question, the way you've pasted it is definitely broken - you have two CREATE TABLE statements inside of each other. Presumably a copy and paste error. But even if you take the inner query there are errors. When you enter this in the MariaDB client you'll see an error, and this helps you find where the problem is. The first error would be:

ERROR 1064 (42000): 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 ''Order'(...

This was answered in your previous question - the statement is again not correctly escaping the identifier - single quotes are not acceptable.

Your next error would be:

ERROR 1064 (42000): 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 'max) NULL...

That points you to the next error - look for where the string max etc. occurs in your query, and try see what could be wrong. In this case you have declared VARCHAR(max) which is not valid - it needs to be a number. Go through your statements in this way trying to identify your errors. If you are still unsure, use the Knowledge Base search to look up, for example, VARCHAR to get further help.

 
3 years, 4 months ago Anthony Apollis
Re: TSQL To Maria DB SQL

Why does this Maria DB syntax differ so much from MySQL and i found it in Xammp package which includes MySql. Im gettin error in this code also :(

SELECT date(o.created_datetime) as Date, od.product_uid as 'Product UID',

p.manufacturer Manufacturer, p.bmc BMC, p.brand Brand, od.label as SKUs, p.selling_unit as 'Unit of Measure',

ROUND(if(od.amended_quantity is not null, od.amended_quantity, od.quantity)) as 'Units Sold',

ROUND((if(od.amended_quantity IS NOT NULL, od.amended_quantity, od.quantity))*p.content,2) as 'Sales Volume',

ROUND((if(od.amended_quantity is not null, od.amended_quantity, od.quantity))*od.price,2) as 'Sales Value'

FROM order_detail od

left join order o on od.order_uid=o.uid

left join product p on od.product_uid=p.uid

and not od.label ='Plastic Bag'

and date(o.created_datetime) >= '2020-03-01'

 
3 years, 4 months ago Anthony Apollis
Re: TSQL To Maria DB SQL

Sorry, it's suppose to be orders and not order :)

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.