SQL Server Performance

Help !

Discussion in 'General Developer Questions' started by pharoah35, Sep 29, 2003.

  1. pharoah35 New Member

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

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

    Thanks in advance
    Pharoah35
  2. Negative New Member

    Don't use quotes around the field names. SQL Server uses square brackets (i.e. SELECT [fieldname] FROM ...) unlike many other DB vendors.
  3. bambola New Member

    I cannot understand this part of the code
    UPDATE [Line Items]
    SET
    [ALS] = 'QOA'*[DLR-PRC]
    You multiply a string with a column (I assume an int).

    If you mean to use the results you get by this part

    '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,

    you would need to asign it into a variable.

    @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,

    Declare @QOA as numeric before the select statement and it should work.

    Bambola.
  4. pharoah35 New Member

    Bambola

    Thanks so much for your responce, However if you could provide an example
    of a Declare statement (syntax). That would be greatly appreciated.
    I am not as versed in SQL as you clearly are.

    Thanks in advance
    Pharoah35



  5. bambola New Member

    Well, assuming columns [Line Items].QTYORD and [Line Items].QTYSHP are integers and [Line Items].[ALS] is either int or decimal, you could declare @QOA as int

    DECLARE @QOA int

    Just add it before your select statement.

    If [Line Items].QTYORD and/or [Line Items].QTYSHP can have a NULL value, you might want to add ISNULL to the case statement or [Line Items].[ALS] will be NULL.

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

    Bambola.
  6. pharoah35 New Member

    Bambola

    Thanks again for your assistance. However when I implement your
    suggested changes, I get the following error message

    Server: Msg 141, Level 15, State 1, Line 136
    A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

    Any additional help you may be able to provide would be greatly appreciated.

    Thanks
    Pharoah35
  7. bambola New Member

    You are right. I was not paying enough attention. And now that I am, I can also see that
    your update statement has no where clause. So I am not sure I understand...
    How many rows the first select statement (as originally written) returns? how many rows are you supposed to update?

    Bambola.
  8. pharoah35 New Member

    I just need to update the [ALS] column with the math results.

    Pharoah35
  9. bambola New Member

    ok... if I understand you well there are 2 ways to do it.
    1 - repeat the calc of the QOA in the update statement (the same select statement with only that column)
    2 - insert results into a temp table (or table datatype) the result set of the select statement, and join it later for the update.

    Bambola.

Share This Page