SQL Server Performance

Help !

Discussion in 'General Developer Questions' started by pharoah35, Aug 11, 2003.

  1. pharoah35 New Member

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






  2. gaurav_bindlish New Member

    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
  3. pharoah35 New Member

    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
  4. gaurav_bindlish New Member

    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
  5. Twan New Member

    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
  6. gaurav_bindlish New Member

    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
  7. pharoah35 New Member

    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
  8. gaurav_bindlish New Member

    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
  9. pharoah35 New Member

    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

Share This Page