variable must not be combined with data-retrieval operations.

Question

Post reply

variable must not be combined with data-retrieval operations.

sqlquery-101401

SSCarpal Tunnel

Points: 4951
More actions
April 25, 2004 at 6:11 am

#107773

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

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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)

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

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

Coach James Ten Centuries Points: 1272 More actions · Answer 3

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