Operand type clash? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Operand type clash?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-10-25 : 15:12:53

Hi, Thanks in advance for your help!

I got this error on my QA when I run my insert statement listed below. My select did return data, and I did not find any samllint nor text data type.

Operand type clash: smallint is incompatible with text

INSERT INTO stsWebOrders (CustomerID, Contact, ItemNumber, Quantity, [Size], Colors,
CutType, numbering, PrevJob, shipping, Email)
SELECT oh.account_number, oh.head_contact_name, jt.ProductFamily, jt.Qty,
convert(varchar(10), jt.ndim1) + ' x ' + convert(varchar(10),jt.ndim2) as [size],
jt.Colors, jt.CutMethod, jt.Numbering, jt.CO_Number, jt.ShipMethod as Shipping,
act.email FROM (stsJobTracking jt INNER JOIN order_header oh ON jt.CO_Number =
oh.head_order_nbr) INNER JOIN account act ON oh.account_number = act.account_number
where oh.head_order_nbr='824345'

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-25 : 15:21:47

What's the data type of each of the columns used in your query?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-10-25 : 15:24:18

ndim1 and ndim2 are float, and others are all varchar with different sizes.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-25 : 15:30:24

You're using varchar for quantity? Go ahead and post the CREATE script for the stsWebOrders table - without that I doubt you'll get much help here.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-25 : 15:31:36

And actually for the stsJobTracking, order_header and account tables while you're about it.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-10-25 : 15:35:15

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stsWebOrders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[stsWebOrders]
GO

CREATE TABLE [dbo].[stsWebOrders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EditClearDT] [datetime] NULL ,
[DateEntered] [datetime] NULL ,
[Contact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuoteNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Shipping] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Size] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Colors] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CutType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrontBack] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Repeat] [smallint] NOT NULL ,
[PrevJob] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Proof] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitPrice] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalPrice] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Instructions] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Numbering] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaymentType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NumbArtFiles] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NumbVisuals] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-10-25 : 15:46:20

Ok, I have found out the mismatch by going through the process of commenting one pair out at a time. It was numbering. The source is smallint and the destination is text. Don't ask me why they design this way.

And thank you for helping!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-25 : 16:28:26

How about that - the message was spot on!

benjo
Starting Member

8 Posts

Posted - 2006-10-26 : 04:09:36

they both have good sense of humour.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources