Hi,

I am using Business Objects with Sybase. We have two types of tables(Daily and Monthly) for every entity (Customer, Card, Account etc.,) with the same number of columns. I created a derived table using the Daily and Monthly tables to combine the values with union all function and joined this derived table to a normal table and I'm trying to perform a SELECT.

I am getting the following error when I run the query..

Exception: DBD, [Server imalrep] [Procedure CTDynamic] Adaptive Server cannot perform the requested action because column 'CUSTOMER_ID' is not within the scope of the joined table expression. Check your command for missing or incorrect database objects, variable names, and/or input data.
State: ZZZZZ

Here is my query:

SELECT
MIS_ACCOUNT.ACCOUNT_ID,
MIS_CUSTOMER.CUSTOMER_ID,
MIS_PRMTR_COMPANY.CODE
FROM
MIS_PRMTR_COMPANY INNER JOIN ( SELECT * FROM MIS_DAILY_CUSTOMER UNION ALL SELECT * FROM MIS_MONTHLY_CUSTOMER) MIS_CUSTOMER
ON (MIS_PRMTR_COMPANY.ID=MIS_CUSTOMER.COMPANY_ID)
INNER JOIN ( SELECT * FROM MIS_DAILY_PRD_ACCOUNT UNION ALL SELECT * FROM MIS_MONTHLY_PRD_ACCOUNT) MIS_ACCOUNT
ON (MIS_CUSTOMER.BUSINESS_DATE=MIS_ACCOUNT.BUSINESS_D ATE and MIS_CUSTOMER.CUSTOMER_ID=MIS_ACCOUNT.CUSTOMER_ID)
WHERE
(
MIS_ACCOUNT.BUSINESS_DATE = '01/05/2011 12:0:0 AM'
)

This query is built by BO automatically

if I place the company join at the end, it works, I dont want to change the sequence of the tables. Here is the query that works.

SELECT
MIS_ACCOUNT.ACCOUNT_ID,
MIS_CUSTOMER.CUSTOMER_ID,
MIS_PRMTR_COMPANY.CODE
FROM
( SELECT * FROM MIS_DAILY_CUSTOMER UNION ALL SELECT * FROM MIS_MONTHLY_CUSTOMER) MIS_CUSTOMER
INNER JOIN ( SELECT * FROM MIS_DAILY_PRD_ACCOUNT UNION ALL SELECT * FROM MIS_MONTHLY_PRD_ACCOUNT) MIS_ACCOUNT
ON (MIS_CUSTOMER.BUSINESS_DATE=MIS_ACCOUNT.BUSINESS_D ATE and MIS_CUSTOMER.CUSTOMER_ID=MIS_ACCOUNT.CUSTOMER_ID)
INNER JOIN MIS_PRMTR_COMPANY ON (MIS_PRMTR_COMPANY.ID=MIS_CUSTOMER.COMPANY_ID)
WHERE
(
MIS_ACCOUNT.BUSINESS_DATE = '01/05/2011 12:0:0 AM'
)

Could anybody help me in this regard

Regards,
Sadiq.