column prefix does not match table name(but it does)

[sue1127]

I'm using Sql Server 2000 on the client, and Sql Server 7 on the server. I copied sql generated by Crystal reports into a stored procedure and modified it to include 2 new tables--budget_lists, and budget_list_accounts. When I run it in query analyzer, it parses ok, but when I try to execute, it gives the error message:

The column prefix 'budget_lists' does not match with a table name or alias name used in the query.

I don't understand this because I do not use an alias, and I do use the table name in the query. I've copied the stored procedure below. Can anyone help?

Thanks very much, Sue

ALTER proc uc_cr_frs_program_deptPcode

@period smallint,
@fiscal_year char(5),
@user varchar(25)

As

SELECT
ledger_organizations.organization_description,
ledger_accounts.account_class_1, ledger_accounts.account_class_2, ledger_accounts.account_class_3,
ledger_balances.balance_name, ledger_balances.period, ledger_balances.ledger, ledger_balances.ledger_account, ledger_balances.fiscal_year, ledger_balances.period_amount,
chart_of_accounts.account, chart_of_accounts.account_description,
accounts_class_1.account_class_1, accounts_class_1.description,
budget_lists.approver,budget_list_accounts.fiscal_year

FROM
{ oj (((dbo.ledger_organizations ledger_organizations INNER JOIN Dbo.ledger_accounts ledger_accounts ON
ledger_organizations.fiscal_year = ledger_accounts.fiscal_year AND
ledger_organizations.ledger = ledger_accounts.ledger AND
budget_lists.fiscal_year=budget_list_accounts.fiscal_year AND
ledger_organizations.organization_level = ledger_accounts.organization_level AND
ledger_organizations.organization = ledger_accounts.organization)
INNER JOIN Dbo.chart_of_accounts chart_of_accounts ON
ledger_accounts.fiscal_year = chart_of_accounts.fiscal_year AND
ledger_accounts.coa = chart_of_accounts.coa AND
ledger_accounts.account = chart_of_accounts.account)
INNER JOIN Dbo.accounts_class_1 accounts_class_1 ON
ledger_accounts.account_class_1 = accounts_class_1.account_class_1)
LEFT OUTER JOIN Dbo.ledger_balances ledger_balances ON
ledger_accounts.ledger = ledger_balances.ledger AND
ledger_accounts.ledger_account = ledger_balances.ledger_account AND
ledger_accounts.fiscal_year = ledger_balances.fiscal_year}

WHERE
ledger_balances.ledger = 'UC' AND
(ledger_balances.balance_name = 'ACTUAL' OR
ledger_balances.balance_name = 'BUDGET') AND
chart_of_accounts.account <> '4910' AND
ledger_balances.fiscal_year=@fiscal_year AND
ledger_balances.period <= @period AND
ledger_accounts.account_class_1 >= '3100' AND
ledger_accounts.account_class_1 <= '3899'
ORDER BY
ledger_accounts.account_class_2 ASC,
accounts_class_1.account_class_1 ASC

 

[vongrunt]

Check FROM section... there is no "budget_lists" table specified, only JOIN condition.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[sue1127]

vongrunt,

Thanks for getting back to me. I'm still not quite sure about how to get around with the newer form of sql, but since the sql was already in place(from Crystal), I tried to follow that form. I'm more accustomed to putting just the table names in the From section and the joins in the Where section. I'll try to rewrite the part with the new tables.

Sue

 

[SQLBill]

SQL Server TSQL and Crystal Reports SQL are not completely the same.

I suggest you refer to the BOL for help with SQL Server's TSQL.

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

-SQLBill

Posting advice: FAQ481-4875