February 1, 2022 at 11:36 am
Please assist? Error: Msg 8117, Level 16, State 1, Line 14
Operand data type nvarchar is invalid for multiply operator.
select
cast(o.created_datetime as date) Date,
od.product_uid [Product UID],
p.manufacturer Manufacturer,
p.bmc BMC,
p.brand Brand,
od.label SKUs,
p.selling_unit [Unit of Measure],
round(
iif(od.amended_quantity is not null, od.amended_quantity, od.quantity),
0
) [Units Sold],
round(
(iif(od.amended_quantity is not null, od.amended_quantity, od.quantity) * p.content),
2
) [Sales Volume],
round(
(iif(od.amended_quantity is not null, od.amended_quantity, od.quantity) * od.price),
2
) [Sales Value]
from order_detail od
left outer join [order] o
on od.order_uid = o.uid
left outer 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')
)
February 1, 2022 at 11:54 am
What are the data types of
od.amended_quantity, od.quantity, od.price, p.content?
one of them I guess is going to be a string type and not numeric type resulting in an operand conversion issue.
you will need to convert the string to numeric to multiply by it.
February 1, 2022 at 2:15 pm
For the love of Codd, store numbers in a number defined column and strings in string defined columns. Anything else is begging for trouble.
In this case, CAST is your friend (assuming all values are numbers, any value not a number and this will break again on you).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 1, 2022 at 3:27 pm
I can only join the chorus in advicing you to define table columns correctly, according to use.
If a column is to contain numbers then define it as such (int, dec, numeric, float etc as best fit the purpose). The null value can be used to set the value to undefined, if that is why you've used a different type of column.
If you absolutely can't change the column to a number type (i.e. you don't own the database and the column contains a mix of numeric and character data), then depending on context TRY_CAST may be an even better friend than CAST. That'll return null if the cast can't go through. But it's still a cludge at best.
February 2, 2022 at 11:00 am
Please see:
CREATE TABLE [dbo].[Order](
[uid] [bigint] NULL,
[delivery_address] [nvarchar](256) NULL,
[complex_unit_no] [nvarchar](200) NULL,
[delivery_latitude] [nvarchar](20) NULL,
[delivery_longitude] [nvarchar](20) NULL,
[store_uid] [bigint] NULL,
[app_version] [nvarchar](10) NULL,
[delivery_fee] [nvarchar](10) NULL,
[amended_delivery_fee] [nvarchar](10) NULL,
[bottles_fee] [nvarchar](10) NULL,
[amended_bottles_fee] [nvarchar](10) NULL,
[promo_code] [nvarchar](50) NULL,
[promo_discount] [nvarchar](10) NULL,
[promo_discount_amount] [nvarchar](10) NULL,
[promo_discount_type] [nvarchar](10) NULL,
[user_agent_string] [nvarchar](512) NULL,
[amended_promo_code] [nvarchar](50) NULL,
[amended_promo_discount] [nvarchar](10) NULL,
[amended_promo_discount_amount] [nvarchar](10) NULL,
[amended_promo_discount_type] [nvarchar](10) NULL,
[delivery_notes] [nvarchar](max) NULL,
[items_total] [nvarchar](30) NULL,
[amended_items_total] [nvarchar](10) NULL,
[order_total] [nvarchar](10) NULL,
[this_order_total] [nvarchar](10) NULL,
[amended_order_total] [nvarchar](30) NULL,A
[adjusted_items_total] [nvarchar](30) NULL,
[adjusted_datetime] [datetime] NULL,
[adjusted_order_total] [nvarchar](10) NULL,
[outstanding_amount] [nvarchar](10) NULL,
[crc] [nvarchar](10) NULL,
[created_datetime] [datetime] NULL,
[placed_datetime] [datetime] NULL,
[transaction_sequence] [nvarchar](20) NULL,
[user_uid] [int] NULL,
[status] [nvarchar](3) NULL,
[accepted_datetime] [datetime] NULL,
[picking_datetime] [datetime] NULL,
[edi_order_no] [nvarchar](50) NULL,
[edi_invoice_status] [nvarchar](7) NULL,
[edi_invoice_no] [nvarchar](50) NULL,
[edi_message_response] [nvarchar](500) NULL,
[last_status_changed_datetime] [datetime] NULL,
[edi_credit_status] [nvarchar](7) NULL,
[amended_datetime] [datetime] NULL,
[vendor_amended_datetime] [datetime] NULL,
[vendor_viewed_datetime] [datetime] NULL,
[user_amending_datetime] [datetime] NULL,
[user_substituted] [nvarchar](1) NULL,
[rating] [nvarchar](1) NULL,
[rating_comment] [nvarchar](2000) NULL,
[confirmed_datetime] [datetime] NULL,
[delivered_datetime] [datetime] NULL,
[delivery_integration_enabled] [nvarchar](1) NULL,
[paid_in_full] [nvarchar](1) NULL,
[driver_name] [nvarchar](80) NULL,
[driver_contact_no] [nvarchar](40) NULL,
[invoice_no] [nvarchar](20) NULL,
[ppay_requested] [nvarchar](1) NULL,
[ppay_payment_id] [nvarchar](50) NULL,
[driver_cancelled] [nvarchar](1) NULL,
[bottles_notes] [nvarchar](150) NULL,
[bottles_agent_name] [nvarchar](50) NULL,
[driver_started_delivery] [nvarchar](1) NULL,
[driver_tracking_link] [nvarchar](300) NULL,
[vendor_need_help] [nvarchar](1) NULL,
[has_vendor_viewed_order] [nvarchar](1) NULL,
[picking_slip_pn_sent] [nvarchar](20) NULL,
[vendor_ready_for_driver] [nvarchar](1) NULL,
[driver_ready_datetime] [datetime] NULL,
[driver_status] [nvarchar](17) NULL,
[vendor_driver_collected] [nvarchar](1) NULL,
[delivery_integration_service_name] [nvarchar](6) NULL,
[show_driver_tracking] [nvarchar](1) NULL,
[payment_method] [nvarchar](4) NULL,
[possible_fraud] [nvarchar](1) NULL,
[invoice_status] [nvarchar](8) NULL,
[driver_tip] [numeric](10, 2) NULL,
[order_type] [nvarchar](2) NULL,
[picker_name] [nvarchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order_Detail](
[uid] [bigint] NULL,
[order_uid] [bigint] NULL,
[product_uid] [bigint] NULL,
[label] [nvarchar](150) NULL,
[selling_unit] [nvarchar](80) NULL,
[price] [nvarchar](10) NULL,
[quantity] [nvarchar](10) NULL,
[amended_quantity] [nvarchar](10) NULL,
[adjusted_quantity] [nvarchar](30) NULL,
[margin] [nvarchar](30) NULL,
[out_of_stock] [nvarchar](1) NULL,
[comments] [nvarchar](250) NULL,
[edi_cost_price] [nvarchar](50) NULL,
[edi_rsp] [nvarchar](50) NULL,
[edi_promo_price] [nvarchar](50) NULL,
[picked] [nvarchar](1) NULL,
[oos_qty] [int] NULL
) ON [PRIMARY]
GO
AND
CREATE TABLE [dbo].[Product](
[uid] [bigint] NULL,
[product_code] [nvarchar](8) NULL,
[ean_code] [nvarchar](50) NULL,
[alt_ean_1] [nvarchar](45) NULL,
[alt_ean_2] [nvarchar](45) NULL,
[ean_code_single] [nvarchar](50) NULL,
[numerator] [numeric](5, 0) NULL,
[pnp_article_number] [nvarchar](45) NULL,
[pnp_article_department] [nvarchar](10) NULL,
[pnp_article_type] [nvarchar](10) NULL,
[pnp_article_category] [nvarchar](45) NULL,
[catalogue_menu_uid] [int] NULL,
[manufacturer] [nvarchar](50) NULL,
[label] [nvarchar](300) NULL,
[sub_label] [nvarchar](50) NULL,
[category] [nvarchar](100) NULL,
[brand] [nvarchar](100) NULL,
[bmc_code] [nvarchar](60) NULL,
[bmc] [nvarchar](60) NULL,
[description] [nvarchar](1000) NULL,
[container_size] [nvarchar](500) NULL,
[selling_unit] [nvarchar](500) NULL,
[content] [nvarchar](45) NULL,
[content_uom] [nvarchar](45) NULL,
[default_price] [numeric](10, 2) NULL,
[icon] [nvarchar](300) NULL,
[status] [nvarchar](1) NULL,
[default_margin] [nvarchar](5) NULL,
[is_combo] [nvarchar](1) NULL,
[max_order_quantity] [int] NULL,
[override_minimum_stock_balance] [numeric](10, 2) NULL,
[override_atp_percent] [numeric](10, 4) NULL,
[override_margin_percent] [numeric](10, 4) NULL,
[price_at_supplier_rsp] [nvarchar](1) NULL,
[top_picks] [nvarchar](1) NULL,
[licenced_product] [nvarchar](1) NULL,
[max_volume] [numeric](7, 3) NULL,
[min_volume] [numeric](7, 3) NULL,
[variable_volume] [nvarchar](1) NULL
) ON [PRIMARY]
GO
February 2, 2022 at 2:22 pm
In order, you have a lot of columns that appear to be costs (or possibly quantities) that are defined as nvarchar rather than numeric/decimal.
If this is a vendor database or database over which you have no control/influence, we understand you may not be able to change the table definitions even if you know they are wrong. If that is the case, you might consider views that make the right datatype conversions (but keeping in mind that such a schema may allow "garbage" data that can't be converted correctly)
Here is the DDL (with comments) in a more readable format:
CREATE TABLE [dbo].[Order](
[uid] [bigint] NULL,
[delivery_address] [nvarchar](256) NULL,
[complex_unit_no] [nvarchar](200) NULL,
[delivery_latitude] [nvarchar](20) NULL,
[delivery_longitude] [nvarchar](20) NULL,
[store_uid] [bigint] NULL,
[app_version] [nvarchar](10) NULL,
[delivery_fee] [nvarchar](10) NULL, -- Unless this is a description rather than a cost/currency amount, this should probably be decimal/numeric
[amended_delivery_fee] [nvarchar](10) NULL, -- Unless this is a description rather than a cost/currency amount, this should probably be decimal/numeric
[bottles_fee] [nvarchar](10) NULL, -- Unless this is a description rather than a cost/currency amount, this should probably be decimal/numeric
[amended_bottles_fee] [nvarchar](10) NULL,
[promo_code] [nvarchar](50) NULL,
[promo_discount] [nvarchar](10) NULL, -- Unless this is a description rather than a cost/currency amount, this should probably be decimal/numeric
[promo_discount_amount] [nvarchar](10) NULL, -- Unless this is a description rather than a cost/currency amount, this should probably be decimal/numeric
[promo_discount_type] [nvarchar](10) NULL,
[user_agent_string] [nvarchar](512) NULL,
[amended_promo_code] [nvarchar](50) NULL,
[amended_promo_discount] [nvarchar](10) NULL, -- Unless this is a description rather than a cost/currency amount, this should probably be decimal/numeric
[amended_promo_discount_amount] [nvarchar](10) NULL,-- Unless this is a description rather than a cost/currency amount, this should probably be decimal/numeric
[amended_promo_discount_type] [nvarchar](10) NULL,
[delivery_notes] [nvarchar](max) NULL,
[items_total] [nvarchar](30) NULL, -- Is this quantity or cost/currency amount? If cost, this should probably be decimal/numeric. If quantity, it should probably be an int.
[amended_items_total] [nvarchar](10) NULL, -- Is this quantity or cost/currency amount? If cost, this should probably be decimal/numeric. If quantity, it should probably be an int.
[order_total] [nvarchar](10) NULL, -- Is this quantity or cost/currency amount? If cost, this should probably be decimal/numeric. If quantity, it should probably be an int.
[this_order_total] [nvarchar](10) NULL, -- Is this quantity or cost/currency amount? If cost, this should probably be decimal/numeric. If quantity, it should probably be an int.
[amended_order_total] [nvarchar](30) NULL, -- Is this quantity or cost/currency amount? If cost, this should probably be decimal/numeric. If quantity, it should probably be an int.
[adjusted_items_total] [nvarchar](30) NULL, -- Is this quantity or cost/currency amount? If cost, this should probably be decimal/numeric. If quantity, it should probably be an int.
[adjusted_datetime] [datetime] NULL,
[adjusted_order_total] [nvarchar](10) NULL, -- Is this quantity or cost/currency amount? If cost, this should probably be decimal/numeric. If quantity, it should probably be an int.
[outstanding_amount] [nvarchar](10) NULL,
[crc] [nvarchar](10) NULL,
[created_datetime] [datetime] NULL,
[placed_datetime] [datetime] NULL,
[transaction_sequence] [nvarchar](20) NULL,
[user_uid] [int] NULL,
[status] [nvarchar](3) NULL,
[accepted_datetime] [datetime] NULL,
[picking_datetime] [datetime] NULL,
[edi_order_no] [nvarchar](50) NULL,
[edi_invoice_status] [nvarchar](7) NULL,
[edi_invoice_no] [nvarchar](50) NULL,
[edi_message_response] [nvarchar](500) NULL,
[last_status_changed_datetime] [datetime] NULL,
[edi_credit_status] [nvarchar](7) NULL,
[amended_datetime] [datetime] NULL,
[vendor_amended_datetime] [datetime] NULL,
[vendor_viewed_datetime] [datetime] NULL,
[user_amending_datetime] [datetime] NULL,
[user_substituted] [nvarchar](1) NULL,
[rating] [nvarchar](1) NULL,
[rating_comment] [nvarchar](2000) NULL,
[confirmed_datetime] [datetime] NULL,
[delivered_datetime] [datetime] NULL,
[delivery_integration_enabled] [nvarchar](1) NULL,
[paid_in_full] [nvarchar](1) NULL,
[driver_name] [nvarchar](80) NULL,
[driver_contact_no] [nvarchar](40) NULL,
[invoice_no] [nvarchar](20) NULL,
[ppay_requested] [nvarchar](1) NULL,
[ppay_payment_id] [nvarchar](50) NULL,
[driver_cancelled] [nvarchar](1) NULL,
[bottles_notes] [nvarchar](150) NULL,
[bottles_agent_name] [nvarchar](50) NULL,
[driver_started_delivery] [nvarchar](1) NULL,
[driver_tracking_link] [nvarchar](300) NULL,
[vendor_need_help] [nvarchar](1) NULL,
[has_vendor_viewed_order] [nvarchar](1) NULL,
[picking_slip_pn_sent] [nvarchar](20) NULL,
[vendor_ready_for_driver] [nvarchar](1) NULL,
[driver_ready_datetime] [datetime] NULL,
[driver_status] [nvarchar](17) NULL,
[vendor_driver_collected] [nvarchar](1) NULL,
[delivery_integration_service_name] [nvarchar](6) NULL,
[show_driver_tracking] [nvarchar](1) NULL,
[payment_method] [nvarchar](4) NULL,
[possible_fraud] [nvarchar](1) NULL,
[invoice_status] [nvarchar](8) NULL,
[driver_tip] [numeric](10, 2) NULL,
[order_type] [nvarchar](2) NULL,
[picker_name] [nvarchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order_Detail](
[uid] [bigint] NULL,
[order_uid] [bigint] NULL,
[product_uid] [bigint] NULL,
[label] [nvarchar](150) NULL,
[selling_unit] [nvarchar](80) NULL,
[price] [nvarchar](10) NULL,
[quantity] [nvarchar](10) NULL,
[amended_quantity] [nvarchar](10) NULL,
[adjusted_quantity] [nvarchar](30) NULL,
[margin] [nvarchar](30) NULL,
[out_of_stock] [nvarchar](1) NULL,
[comments] [nvarchar](250) NULL,
[edi_cost_price] [nvarchar](50) NULL,
[edi_rsp] [nvarchar](50) NULL,
[edi_promo_price] [nvarchar](50) NULL,
[picked] [nvarchar](1) NULL,
[oos_qty] [int] NULL
) ON [PRIMARY]
GO
AND
CREATE TABLE [dbo].[Product](
[uid] [bigint] NULL,
[product_code] [nvarchar](8) NULL,
[ean_code] [nvarchar](50) NULL,
[alt_ean_1] [nvarchar](45) NULL,
[alt_ean_2] [nvarchar](45) NULL,
[ean_code_single] [nvarchar](50) NULL,
[numerator] [numeric](5, 0) NULL,
[pnp_article_number] [nvarchar](45) NULL,
[pnp_article_department] [nvarchar](10) NULL,
[pnp_article_type] [nvarchar](10) NULL,
[pnp_article_category] [nvarchar](45) NULL,
[catalogue_menu_uid] [int] NULL,
[manufacturer] [nvarchar](50) NULL,
[label] [nvarchar](300) NULL,
[sub_label] [nvarchar](50) NULL,
[category] [nvarchar](100) NULL,
[brand] [nvarchar](100) NULL,
[bmc_code] [nvarchar](60) NULL,
[bmc] [nvarchar](60) NULL,
[description] [nvarchar](1000) NULL,
[container_size] [nvarchar](500) NULL,
[selling_unit] [nvarchar](500) NULL,
[content] [nvarchar](45) NULL,
[content_uom] [nvarchar](45) NULL,
[default_price] [numeric](10, 2) NULL,
[icon] [nvarchar](300) NULL,
[status] [nvarchar](1) NULL,
[default_margin] [nvarchar](5) NULL, -- margin is typically a percentage, which implies this should be numeric/decimal
[is_combo] [nvarchar](1) NULL,
[max_order_quantity] [int] NULL,
[override_minimum_stock_balance] [numeric](10, 2) NULL,
[override_atp_percent] [numeric](10, 4) NULL,
[override_margin_percent] [numeric](10, 4) NULL,
[price_at_supplier_rsp] [nvarchar](1) NULL,
[top_picks] [nvarchar](1) NULL,
[licenced_product] [nvarchar](1) NULL,
[max_volume] [numeric](7, 3) NULL,
[min_volume] [numeric](7, 3) NULL,
[variable_volume] [nvarchar](1) NULL
) ON [PRIMARY]
GO
February 2, 2022 at 3:14 pm
Lat & long are numbers, not characters. Transaction sequence, almost definitely a number. Wow.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2022 at 4:55 pm
So yeah your trying to multiply strings.
ABC x 123 = ????????
SQL cannot multiply a string, so one or more of the columns in the multiplication parts of the query will have a string value.
TRY_CONVERT, CAST will be your friends really, but you should be looking at proper data type mappings. That will be tricky as ratbak said if it’s a vendor DB but if it’s in house it needs to be sorted.
April 20, 2022 at 1:04 pm
Will Cast or Convert only convert for the query or update my data types?
April 20, 2022 at 1:16 pm
Depends on what your query is doing. Cast or Convert does whatever your query tells it to do.
If you are only using it in a select (only returning data), then it returns the casted/converted results.
If you are using them in an update, then then will update.
If you are using them in an insert, then they will insert.
It sounds like you would benefit from reviewing the fundamentals of SQL .
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply