Convert Mysql to TSQL
-
I am trying to correct the following code in SQL Server, please assist?
-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT convert(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(case when(od.amended_quantity is not null then od.amended_quantity else od.quantity)) as 'Units Sold',
ROUND((case when(od.amended_quantity IS NOT NULL then od.amended_quantity else od.quantity))*p.content,2) as 'Sales Volume',
ROUND((case when(od.amended_quantity is not null then od.amended_quantity else 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
where o.status dbo.in ('D',1,2,3,4,5)
and not od.label ='Plastic Bag'
and convert(date, o.created_datetime) >= '2020-04-01'
-
Looks like you're missing the END of the CASE statements.
-
This line looks weird. What is it supposed to do? Strings and INTs mixed in the same list?
where o.status dbo.in ('D',1,2,3,4,5)The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
This line looks weird. What is it supposed to do? Strings and INTs mixed in the same list?
where o.status dbo.in ('D',1,2,3,4,5)It doesn't make sense. I don't know if the dbo is a typo or if the column is called [status dbo]. I would guess it should be like this.
where o.[status] in ('D','1','2','3','4','5')The first ROUND does not have a number of decimal places. All the case statements have an open paren/bracket in a strange place, and as mentioned they are all missing an end. I think this should work, but I don't have tables to check.
ROUND((case when od.amended_quantity is not null then od.amended_quantity else od.quantity end),2) as 'Units Sold',
ROUND((case when od.amended_quantity IS NOT NULL then od.amended_quantity else od.quantity end * p.content),2) as 'Sales Volume',
ROUND((case when od.amended_quantity is not null then od.amended_quantity else od.quantity end * od.price),2) as 'Sales Value'The joins should probably be inner joins. The where statement filters the order table, so it undoes the left outer join, and there is no handling of nulls from either joined table, so I assume an inner join is intended.
-
Here is the complete code from Mysql that i am trying to convert/change to TSQL:
App Filter Categories
select a.label as ‘App Category’
, b.label as ‘Menu’
, c.product_uid as ‘Product UID’
from app_menu_filters a
left join app_menu_filters b on b.parent_uid=a.uid
left join app_menu_filter_products c on b.uid=c.app_menu_filter_uid
where a.status=‘A’ and a.level=1
order by 1,2,3;
Sales and Product Data
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
where o.status in (‘D’,1,2,3,4,5)
and not od.label =‘Plastic Bag’
and date(o.created_datetime) >= ‘2020-04-01’ -
you just need to replace that "if(od.amend..." with a "coalesce(od.amend.." and the "date(..." with a "convert(date, ...".. and fix the IN clause to have all values enclosed in single quotes as mentioned before... and obviously with proper single quotes, not the fancy ones you have on your post.
-
Is the quantity a decimal or an integer? In MySQL what do you get if you use ROUND without specifying a number of decimal places?
Does this work?
SELECT CONVERT(DATE, o.created_datetime) as [Date],
od.product_uid as 'Product UID',
p.manufacturer as Manufacturer, p.bmc as BMC, p.brand as Brand,
od.[label] as SKUs, p.selling_unit as 'Unit of Measure',
ROUND(ISNULL(od.amended_quantity, od.quantity),0) as 'Units Sold',
ROUND((ISNULL(od.amended_quantity, od.quantity) * p.content),2) as 'Sales Volume',
ROUND((ISNULL(od.amended_quantity, od.quantity) * od.price),2) as 'Sales Value'
FROM order_detail od
inner join order o on od.order_uid=o.uid
inner join product p on od.product_uid=p.uid
where o.[status] in ('D','1','2','3','4','5')
and od.[label] <> 'Plastic Bag'
and CONVERT(DATE, o.created_datetime) >= '2020-04-01' -
I would remove the convert from the where clause - it isn't needed.
AND o.created_datetime >= '2020-04-01'
Unless that column is not actually a datetime data type - if that is the case then I would fix the column definition.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Still getting an error.
-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT CONVERT(DATE, o.created_datetime) as [Date],
od.product_uid as 'Product UID',
p.manufacturer as Manufacturer, p.bmc as BMC, p.brand as Brand,
od.[label] as SKUs, p.selling_unit as 'Unit of Measure',
ROUND(ISNULL(od.amended_quantity, od.quantity),0) as 'Units Sold',
ROUND((ISNULL(od.amended_quantity, od.quantity) * p.content),2) as 'Sales Volume',
ROUND((ISNULL(od.amended_quantity, od.quantity) * od.price),2) as 'Sales Value'
FROM order_detail od
inner join order o on od.order_uid=o.uid
inner join product p on od.product_uid=p.uid
where o.[status] in ('D','1','2','3','4','5')
and od.[label] <> 'Plastic Bag'
AND o.created_datetime >= '2020-04-01'
-
order is a reserved word - either use 2 naming convention (which you should be doing anyway) or enclose in square brackets - or both
- frederico_fonseca wrote:
order is a reserved word - either use 2 naming convention (which you should be doing anyway) or enclose in square brackets - or both
Typical, I put all the other reserved words in square brackets, but not the one that really mattered.
-
Please give me completed code.
- yrstruly wrote:
Please give me completed code.
Try this. I have added the schema (assuming it is dbo) and put the order table name in square brackets. If you type the name of an object and the colour changes (usually blue) is is a reserved word and best not used for an object name.
SELECT CONVERT(DATE, o.created_datetime) as [Date],
od.product_uid as 'Product UID',
p.manufacturer as Manufacturer, p.bmc as BMC, p.brand as Brand,
od.[label] as SKUs, p.selling_unit as 'Unit of Measure',
ROUND(ISNULL(od.amended_quantity, od.quantity),0) as 'Units Sold',
ROUND((ISNULL(od.amended_quantity, od.quantity) * p.content),2) as 'Sales Volume',
ROUND((ISNULL(od.amended_quantity, od.quantity) * od.price),2) as 'Sales Value'
FROM dbo.order_detail AS od
inner join dbo.[order] AS o on od.order_uid=o.uid
inner join dbo.product AS p on od.product_uid=p.uid
where o.[status] in ('D','1','2','3','4','5')
and od.[label] <> 'Plastic Bag'
and o.created_datetime >= '2020-04-01'
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply