The multi-part identifier "TableA.act_voidCode" could not be bound

Debbie Erickson


Hi. I read that I can use a stored function to return a table. I created a function that takes 3 input parameters and returns a "table". The table it passes back is really a single record with just three fields that I want to select into my main query. In my test of the function, I used hardcoded values and it returned what I wanted. Now I want to use this new function by passing input data from the table it just created. As my input parameters, I have tried both TableA.act_voidCode and act.act_voidcode and get the same error.

This query is intended to be a commandtext parameter, not as part of a stored procedure. Any ideas on how to get the values into/out of my new function I tried declaring some variables and setting them as part of the query and it complained about that as well. Apparently you cannot mix selecting data and setting variables within the same select code.

I provided only a fraction of the selection code. In actuality, I have 6 different select statements I am doing a union on within the TableA structure. The error happens whether I have one or 6 statements, though. Part of the reason I tried to move this voidcode checking to a function was to avoid needing 12 select statements, one when the code is void, the second for when the code was not void.

Here's a bit of background on what I hoped to accomplish with the function: In my data I have a void code and a request code. In my function I look up the requestcode in another table. That table has void fields and nonvoid fields. Using a case statement, the function returns either the three void or three nonvoid values. If the record is not found, returns a default value of "unknown" for each of the 3 fields.

Thanks in Advance!

Debbie Erickson

SELECT
GTA.*,
act_id, act_AcctNo, act_VoidCode, act_TrxAmtN, act_HostApproveLowerAmount, act_LaneNoN,
act_Cashier, act_CardName, act_ExpDate, act_Field_4_CU_KB, act_TrxDate, act_TrxTime,
act_AuthCode, act_SeqNo, ManagerID
FROM
(
SELECT act.act_id, act.act_AcctNo, act.act_VoidCode, act.act_TrxAmtN, act.act_HostApproveLowerAmount,
act.act_LaneNoN, act.act_Cashier, act.act_CardName, act.act_ExpDate, act.act_Field_4_CU_KB,
SUBSTRING(act.act_TrxDate, 3, 2) + SUBSTRING(act.act_TrxDate, 5, 2) + SUBSTRING(act.act_TrxDate, 1, 2) AS act_TrxDate,
act.act_TrxTime, act.act_AuthCode, act.act_SeqNo, SUBSTRING(act.act_ManagerID, 2, 8) AS ManagerID
FROM
actlog AS act
WHERE (dbo.mtx_weps_rpt_IsBalInq(act.act_ReqCode) = 0) AND
(dbo.mtx_weps_rpt_IsPreAuth(act.act_ReqCode) = 0) AND
(act.act_TrxFinalDisp = 'A' OR
act.act_TrxFinalDisp = 'C' OR
( (act.act_TrxFinalDisp IN ('H', 'I', 'E', 'K') AND
(act.act_BankOff <> '*') )))
) AS TableA,
dbo.mtx_weps_rpt_GetTransactionAmtAndCount(TableA.act_voidCode,TableA.act_reqcode,TableA.act_TrxAmtN) GTA

ORDER BY act_id

Arnie Rowland

What is the error you are getting

And what is the Function code

Are you using SQL 2000 or SQL 2005

Louis Davidson

You cannot use a stored procedure in a query like this, but you could use a function like this in 2005 using CROSS APPLY. In 2000, function parameters could not not come from tables like this. So if this is 2005, you could do:

(act.act_BankOff <> '*') )))
) AS TableA
CROSS APPLY
dbo.mtx_weps_rpt_GetTransactionAmtAndCount(TableA.act_voidCode,TableA.act_reqcode,TableA.act_TrxAmtN) GTA


If this is not clear, can you provide a more simple example of what you are trying to do

Debbie Erickson

This is in sql2005 Express. I tried it and it worked just as I wanted. Thanks so much Louis! I've been hounding my co-workers because I just felt there HAD to be a way to do this. These message boards are awesome! I have an sql200 book and it did say I could return a table, and I could use the function anywhere in the query, but I was missing knowledge of the cross apply statement. Your answer made my day!! <<Passing some virtual flowers as a Thank You>> ROFL!

Debbie