Help ! - 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)

Help !

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2003-09-29 : 08:57:13

Good morning All

I was hoping you could assist me with this issue. I am attempting
to update a table with the results of the math at the end of the following SP (Stored Proceedure) and I get the following error

(235 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'QOA' to a column of data type int.

Here is my SP (Stored Proceedure)
----------------------------------------------------------------------
SELECT

'SOURCE_DISPLAY' =
RTRIM(CASE

WHEN ([Line Items].DP# LIKE '999%') THEN 'NO-INV'
WHEN [IDEAS INVENTORY].VENDOR# = '999999' THEN 'NO-INV'
WHEN [IDEAS INVENTORY].BRAND = 'SUBCONTRACT' THEN 'NO-INV'
WHEN [Line Items].QTYORD = [Line Items].QTYSHP THEN 'STOCK'
WHEN tbl_eta.SOURCE IS NULL THEN 'N/A'
ELSE tbl_eta.SOURCE END),

'ETA_DISPLAY' =
CASE

WHEN ([Line Items].DP# LIKE '999%') THEN 'NO-INV'
WHEN [IDEAS INVENTORY].VENDOR# = '999999' THEN 'NO-INV'
WHEN [IDEAS INVENTORY].BRAND = 'SUBCONTRACT' THEN 'NO-INV'
WHEN [Line Items].QTYORD = [Line Items].QTYSHP THEN 'ALLOC'
WHEN tbl_eta.SOURCE = 'STOCK' THEN CONVERT(varchar, DATEPART(m,
{ fn NOW() }-1)) + '/' + CONVERT(varchar,
DATEPART(d, { fn NOW() }-1))
+ '/' + CONVERT(varchar, RIGHT(DATEPART(yy,
{ fn NOW() }-1), 2))

WHEN tbl_eta.ETA = '11/11/25' THEN 'N/A'
WHEN tbl_eta.ETA IS NULL THEN 'N/A' ELSE
CONVERT(varchar, DATEPART(m,
tbl_eta.ETA)) + '/' + CONVERT(varchar,
DATEPART(d, tbl_eta.ETA))
+ '/' + CONVERT(varchar, RIGHT(DATEPART(yy,
tbl_eta.ETA), 2))
END,

tbl_eta.SOURCE,

'ETA' =

CASE

WHEN [IDEAS INVENTORY].VENDOR# = '999999' OR tbl_eta.SOURCE = 'STOCK' THEN CONVERT(varchar, DATEPART(m,
{ fn NOW() }-1)) + '/' + CONVERT(varchar,
DATEPART(d, { fn NOW() }-1))
+ '/' + CONVERT(varchar, RIGHT(DATEPART(yy,
{ fn NOW() }-1), 2))

WHEN [Line Items].QTYORD = [Line Items].QTYSHP THEN

CONVERT(DATETIME,CONVERT(varchar, DATEPART(m,
{ fn NOW() }-1)) + '/' + CONVERT(varchar,
DATEPART(d, { fn NOW() }-1))
+ '/' + CONVERT(varchar, RIGHT(DATEPART(yy,
{ fn NOW() }-1), 2)))

WHEN tbl_eta.ETA IS NULL THEN '11/11/25'

ELSE
CONVERT(DATETIME,tbl_eta.ETA) END,

OpenOrders.STATUS,
OpenOrders.INVOICE#, OpenOrders.[ORDER-DATE],
OpenOrders.[DUE-DATE],
[SALES GROUPS].NAME AS [OUTSIDE REP],
[SALES GROUPS1].NAME AS [INSIDE REP],
OpenOrders.[CUST-PO#], OpenOrders.CUST#,
[IDEAS CUSTOMERS].NAME,
OpenOrders.ATTN2 AS ATTENTION, [Line Items].DP#,
[IDEAS INVENTORY].[VNDR-ITEM#],
[Line Items].QTYORD AS QOO,

'QOA' =
CASE
WHEN [IDEAS INVENTORY].VENDOR# = '999999' THEN [Line Items].QTYORD
WHEN [IDEAS INVENTORY].BRAND = 'SUBCONTRACT' THEN [Line Items].QTYORD
ELSE [Line Items].QTYSHP END,

OpenOrders.[SHIP-TO-NAME],

'DESC' =
CASE
WHEN [Line Items].[DESC] IS NULL THEN [HISTRY SPEC-DESC].[DESC]
ELSE [Line Items].[DESC] END,

[Line Items].SEQ#,
[Line Items].INVOICE# + [Line Items].SEQ# AS [INV-SEQ],
[IDEAS INVENTORY].CAT#, OpenOrders.SLSMN,
OpenOrders.SLSMN1,
[Line Items].[DLR-PRC] / 100 AS [DLR-PRC], [Line Items].[RET-PRC] / 100 AS [RET-PRC], [Line Items].[ALS] AS [ALS],
[IDEAS INVENTORY].[LANDED-COST] / 100 AS [LANDED-COST],
[SALES GROUPS].[GROUP],
[Line Items].QTYORD*[Line Items].[DLR-PRC] / 100 AS EXTENDED,
[Line Items].QTYORD*[IDEAS INVENTORY].[LANDED-COST] / 100 AS EXT_LANDED,

'NO-INV FLAG' = CASE WHEN [IDEAS INVENTORY].VENDOR# = '999999' OR [IDEAS INVENTORY].BRAND = 'SUBCONTRACT' OR ([Line Items].DP# LIKE '999%') THEN [Line Items].QTYORD ELSE 0 END

FROM dbo.[IDEAS CUSTOMERS] INNER JOIN
dbo.OpenOrders ON
dbo.[IDEAS CUSTOMERS].CUST# = dbo.OpenOrders.CUST# INNER
JOIN
dbo.[SALES GROUPS] ON
dbo.OpenOrders.SLSMN = dbo.[SALES GROUPS].TERR# INNER JOIN
dbo.[Line Items] ON
dbo.OpenOrders.[ORIG-INV#] = dbo.[Line Items].INVOICE# INNER
JOIN
dbo.[SALES GROUPS] [SALES GROUPS1] ON
dbo.[SALES GROUPS].TERR# = [SALES GROUPS1].TERR# AND
dbo.OpenOrders.SLSMN = [SALES GROUPS1].TERR# LEFT OUTER
JOIN
dbo.[HISTRY SPEC-DESC] ON
dbo.[Line Items].INVOICE# + dbo.[Line Items].SEQ# = dbo.[HISTRY SPEC-DESC].[INVOICE-SEQ#]
LEFT OUTER JOIN
dbo.[IDEAS INVENTORY] ON
dbo.[Line Items].DP# = dbo.[IDEAS INVENTORY].DP# LEFT OUTER
JOIN
dbo.tbl_eta ON
dbo.[Line Items].INVOICE# + dbo.[Line Items].SEQ# = dbo.tbl_eta.[INV-SEQ]

where OpenOrders.SLSMN1 = '1564' and (dbo.OpenOrders.STATUS = '04')

UPDATE [Line Items]
SET
[ALS] = 'QOA'*[DLR-PRC]
----------------------------------------------------------------------
Any assistance concerning this problem would be greatly appreciated.

Thanks in advance
Pharoah35

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 09:40:05

Huh?

How does that work?

Are you thinking..


UPDATE [Line Items]
SET 
[ALS] = 'QOA'*[DLR-PRC]

That 'QOA' is a reference to a column in your select?

It's really the literal string 'QOA'

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources