Sql Error Number 141

Question

Post reply

Sql Error Number 141

shayrosenfeld

Grasshopper

Points: 23
More actions
January 24, 2008 at 8:01 am

#202509

Hi.
I have a T-SQL Query that give me the above Error.
Here is the SQL Query. The name of the fields got changed to Field1, Field2, Etc.
The reason is that I cannot show you the real names because of confidentuality essues, sorry.
DECLARE
@KOD_1 SMALLINT,
@KOD_2 SMALLINT,
@KOD_3 SMALLINT,
@KOD_4 SMALLINT
SELECT
Field1 ,
Field2 ,
Field3 ,
@KOD_1=Field4,
Field5,
Field6,
Field7,
Field8,
@KOD_2 = Field9,
@KOD_3=Field10,
@KOD_4=Field11,
Field12,
DBO.Fn_Get_something(@KOD_1,@KOD_2,@KOD_3,@KOD_4)
FROM table1 DBG WITH (NOLOCK)
JOIN table2 POL WITH (NOLOCK)
ON Field3 = table2.someField1 AND DBG.teudat_zeut = table2.someField2
AND table1.someField3 = table2.someField3
AND table2.someField4 = (SELECT table3.someField5 FROM table3 KPB WITH (NOLOCK) WHERE
table3.someField6 = Field4 AND table3.someField6 = table1.Field5
AND table3.someField7 = table1.someField7)
WHERE month(table1.Field1) = 9 AND year(table1.Field1) = 2007
ORDER BY Field9
I hope someone will know how to get over this error.
The function I am using is a Scalar-Valued Function, so no problem should be because of a Table-Valued function not working.
with thanks,
Shay.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

DECLARE

@KOD_1 SMALLINT,

@KOD_2 SMALLINT,

@KOD_3 SMALLINT,

@KOD_4 SMALLINT

SELECT

Field1 ,

Field2 ,

Field3 ,

Field5,

Field6,

Field7,

Field8,

Field12,

DBO.Fn_Get_something(Field4,Field9,Field10,Field11)

FROM table1 DBG WITH (NOLOCK)

JOIN table2 POL WITH (NOLOCK)

ON Field3 = table2.someField1 AND DBG.teudat_zeut = table2.someField2

AND table1.someField3 = table2.someField3

AND table2.someField4 = (SELECT table3.someField5 FROM table3 KPB WITH (NOLOCK) WHERE

table3.someField6 = Field4 AND table3.someField6 = table1.Field5

AND table3.someField7 = table1.someField7)

WHERE month(table1.Field1) = 9 AND year(table1.Field1) = 2007

ORDER BY Field9

_____________
Code for TallyGenerator

shayrosenfeld Grasshopper Points: 23 More actions · Answer 2

Thanks for your prompt reply.

Your solution is acceptable, and working of course, but I need to show all the fields in the finale table which the procedure will produce.

Sorry I havn't replyed right away, as our work week in my counrty (Israel) is Sunday through Thursday.

Jo Pattyn SSC-Dedicated Points: 32545 More actions · Answer 3

The problem is when the query returns multiple records

@KOD_1 can contain only one value

shayrosenfeld Grasshopper Points: 23 More actions · Answer 4

Yes, I can see that. But, the meaning was to take the current value of the select, because as far as

I know the query only takes one line at a time, so I wanted to take the results of the current line. I'm pretty sure the query works like that, and if so there is no reason for multiply results for the current single line in the query.