Operand data type nvarchar is invalid for m

  • 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')

    )

  • 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.

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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.

  • 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

     

  • 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
  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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.

  • Will Cast or Convert only convert for the query or update my data types?

  • 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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply