Help !

Help, I get the following error when trying to execute the following stored proceedure
when adding the math at the bottom.

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

---------------------------------------------------------------------------------------
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, [Line Items].[RET-PRC] / 100 AS [RET-PRC], [Line Items].[ALLOS] AS [ALLOS],
[Line Items].[ALLOB] AS [ALLOB],
[Line Items].[ALLOGP] AS [ALLOGP],

'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],
[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
dbpenOrders ON
dbo.[IDEAS CUSTOMERS].CUST# = dbpenOrders.CUST# INNER
JOIN
dbo.[SALES GROUPS] ON
dbpenOrders.SLSMN = dbo.[SALES GROUPS].TERR# INNER JOIN
dbo.[Line Items] ON
dbpenOrders.[ORIG-INV#] = dbo.[Line Items].INVOICE# INNER
JOIN
dbo.[SALES GROUPS] [SALES GROUPS1] ON
dbo.[SALES GROUPS].TERR# = [SALES GROUPS1].TERR# AND
dbpenOrders.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 (dbpenOrders.STATUS = '04')

UPDATE [LINE ITEMS]
SET
[ALLOS] = 'QOA'*([DLR-PRC] / 100)

UPDATE [LINE ITEMS]
SET
[ALLOB] = 'QOA'*([RET-PRC] / 100)

------------------------------------------------------------------------------------------

Could you tell me where I am going wrong and how best to correct it.
Thanks in advance for your assistance

Pharoah35

I think the line
'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,
should have some conditon to validate and should be like
'QOA' =
CASE <Value To Comapare>
WHEN [IDEAS INVENTORY].VENDOR# = '999999' THEN [Line Items].QTYORD
WHEN [IDEAS INVENTORY].BRAND = 'SUBCONTRACT' THEN [Line Items].QTYORD
ELSE [Line Items].QTYSHP END,


Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Hello

Thank you so much for your reply, What type of value would you be suggesting.
Could you give me an example.

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

Thanks..
Pharoah35

Sorry about the confusion. I think there is a datatype micmatch in this query. What is the datatype for [Line Items].QTYSHP? I guess it is numeric. Try converting the data to Varchar and see if this helps.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

why is QOA in single quotes in the update statement?

I think it should be...

UPDATE [LINE ITEMS]
SET
[ALLOS] = QOA*([DLR-PRC] / 100)

UPDATE [LINE ITEMS]
SET
[ALLOB] = QOA*([RET-PRC] / 100)

Are you saying that the proc runs fine without the update statements?

Twan

Valid point by Twan

Wait a minute... Is QOA going to be available for UPDATE Query? Is it present in the [Line Items] table?

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Hello

Thanks again for the assistance..

The [Line Items] table does not have a QOA column.
Also when I remove the 's from 'QOA'*([RET-PRC])/100, as suggested I get
an ADO Error.

Thanks
Pharoah35

So the data in QOA field is not available for Update query. Store the data on the client side or in a temporary table on server and then run the update query.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Good morning

Thanks for your assistance on this one. However I am not as proficient in SQL
as you ladies & gentlemen. Could you be so kind as to adding information on how to implement your suggested solution

Thanks again
Pharoah35