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-08-11 : 09:49:57

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
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
[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

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-08-11 : 09:56:29

Just what the message says. In these statements:

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

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

You are trying to multiply the string 'QOA' with a number. Maybe just lose the 's if it's a column name you are reffering to?

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

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

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2003-08-11 : 11:17:54

Hello

Thank you so much for your reply,
Then I remove the 's as you suggested, I get the following Error.

ADO error: Invalid column name 'QOA'
Invalid column name 'QOA'

Thanks
Pharoah35

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-11 : 11:31:50

As the previous poster suggested you ought to remove the single quotes, i.e. the ' from your column names.

Something like this :

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

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

I assume that QOA exists in both your tables. If it does and you still get an error then you should check the data type of the QOA.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

lee_h
Starting Member

36 Posts

Posted - 2003-08-11 : 23:49:22

QOA is an alias from the select statement right?
So the second and third query statements don't know about it.
How many rows are you expecting back in the select statement? 1 or a batch of records?
If you are only ever returning one row, you could place all the values into variables, otherwise you will have to replicate the case statement for the updates.

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2003-08-12 : 08:38:43

Good morning

Thanks for your assistance on this one. I am not extremly proficient
in SQL, so could you tell me how one would implement your suggestion.

Thanks agaon
pharoah35

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources