variable must not be combined with data-retrieval operations.

  • SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

     

    CREATE   PROC  zCM_SP_GETSUBZCMSBAA(

     @zcmSBA_V_SUBSTATIONID varchar(15),

     @zcmSBA_V_WONO varchar(6),

     @zcmSBA_N_JONO numeric,

     @zcmSBA_I_COSTTYPE Int

     

    )

    as

     declare @err int

     

     DECLARE @retItems VARCHAR(1000)

     DECLARE @retAllocAmts VARCHAR(1000)

     

     SET @retItems =''

     SET @retAllocAmts=''

     

    SELECT    

     zcmSBA_V_TRXNO [TRX No],

     zcmSBA_V_TRXDESC [Description],

     zcmSBA_D_TRXDATE [Date],

     zcmSBA_N_ACTUALAMT [Actual Amount],

       A.zcmSBA_V_SUBSTATIONID=B.zcmSBA_V_SUBSTATIONID AND

      A.zcmSBA_V_WONO=B.zcmSBA_V_WONO AND

      A.zcmSBA_N_JONO=B.zcmSBA_N_JONO AND

      A.zcmSBA_V_TRXNO=B.zcmSBA_V_TRXNO

     

    )

    FROM  

     ZCM_SBA B

    WHERE

     zcmSBA_V_SUBSTATIONID = @zcmSBA_V_SUBSTATIONID and

     zcmSBA_V_WONO = @zcmSBA_V_WONO and

     zcmSBA_N_JONO= @zcmSBA_N_JONO

     

    set @err =@@error

    if @err<>0

    return 1

    else

    return 0

     

    IT GIVES

    A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

    THIS ERROR

    PLZ SUGGEST

  • Your problem, I believe, is with the following section of code...
    A.zcmSBA_V_SUBSTATIONID=B.zcmSBA_V_SUBSTATIONID AND

      A.zcmSBA_V_WONO=B.zcmSBA_V_WONO AND

      A.zcmSBA_N_JONO=B.zcmSBA_N_JONO AND

      A.zcmSBA_V_TRXNO=B.zcmSBA_V_TRXNO

     
    Looks more like part of a WHERE clause... anyway, THAT's what is being interpreted as "assigns a value to a variable"... it does not follow the rules for "data-retrieval operations".  If that code is necessary for something, you should turn it into a CASE statement.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The syntax of the whole query is wrong.

    No join operators and no "AND" kaywords are allowed in the SELECT clause.

    In addition there is no table in the FROM clause which has the alias A.

    In the SELECT clause there is a ")" which has no corresponding "("

    So reformulate your query as a whole



    Bye
    Gabor

  • Get both your query output and return success with @@error as a parameter output:

    SET

    QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    CREATE PROC CM_SP_GETSUBZCMSBAA

    @zcmSBA_V_SUBSTATIONID varchar(15),

    @zcmSBA_V_WONO varchar(6),

    @zcmSBA_N_JONO numeric,

    @zcmSBA_I_COSTTYPE Integer,

    @err integer OUTPUT

    AS

    DECLARE @err int,

    @retItems VARCHAR(1000),

    @retAllocAmts VARCHAR(1000)

    SET @retItems ='',

    @retAllocAmts=''

    SELECT

    zcmSBA_V_TRXNO [TRX No],

    zcmSBA_V_TRXDESC [Description],

    zcmSBA_D_TRXDATE [Date],

    zcmSBA_N_ACTUALAMT [Actual Amount],

    FROM ZCM_SBA

    WHERE zcmSBA_V_SUBSTATIONID = @zcmSBA_V_SUBSTATIONID

    and zcmSBA_V_WONO = @zcmSBA_V_WONO

    and zcmSBA_N_JONO= @zcmSBA_N_JONO

    set @err = @@error


    Regards,

    Coach James

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply