created below code in SQL runs fine and produces the correct results.
I want to create the same joins/schema in BO designer however it doesnt produce the same results
-- SQL Query
--Query 1:
SELECT
FPIS_Planner_details.Planner_name as 'From Planner',
FPIS_Planner_details.Regionaloffice as 'Office',
FPIS_Planner_details_1.Planner_name AS 'To Planner',
FPIS_Planner_details_1.Regionaloffice as 'Office',
TransactionAdjustmentRequest.clientId,
TransactionAdjustmentRequest.adjustmentDate as 'Month of Transaction',
TransactionAdjustmentRequest.transactionType as 'Transaction Type',
TransactionAdjustmentRequest.amount as 'Amount Transferred'
FROM TransactionAdjustmentRequest
INNER JOIN FPIS_Planner_details ON TransactionAdjustmentRequest.fromPlannerDetailsId = FPIS_Planner_details.id
INNER JOIN FPIS_Planner_details FPIS_Planner_details_1 ON TransactionAdjustmentRequest.toPlannerDetailsId = FPIS_Planner_details_1.id
where TransactionAdjustmentRequest.reportMonth = '01 jan 2011'
and status = 'APPROVED'
--Query 2:
SELECT
FPIS_Planner_details.Planner_name as 'From Planner',
FPIS_Planner_details.Regionaloffice as 'Office',
FPIS_Planner_details_1.Planner_name AS 'To Planner',
FPIS_Planner_details_1.Regionaloffice as 'Office',
FUAAdjustmentRequest.amount as 'Amount Transferred'
FROM FUAAdjustmentRequest
INNER JOIN FPIS_Planner_details ON FUAAdjustmentRequest.fromPlannerDetailsId = FPIS_Planner_details.id
INNER JOIN FPIS_Planner_details FPIS_Planner_details_1 ON FUAAdjustmentRequest.toPlannerDetailsId = FPIS_Planner_details_1.id
where FUAAdjustmentRequest.reportMonth = '01 jan 2011'
and status = 'APPROVED'
-- Query1 from BO webi
anyone know what I am doing wrong?
SELECT
TransactionAdjustmentRequest.status,
TransactionAdjustmentRequest.reportMonth,
TransactionAdjustmentRequest.amount,
TransactionAdjustmentRequest.transactionType,
TransactionAdjustmentRequest.adjustmentDate,
TransactionAdjustmentRequest.clientId,
FPIS_Planner_details_1.RegionalOffice,
FPIS_Planner_details_1.Planner_name,
FPIS_Planner_details.RegionalOffice,
FPIS_Planner_details.Planner_name
FROM
TransactionAdjustmentRequest,
FPIS_Planner_details FPIS_Planner_details_1,
FPIS_Planner_details
WHERE
( TransactionAdjustmentRequest.id=FPIS_Planner_details.id )
AND ( TransactionAdjustmentRequest.id=FPIS_Planner_details_1.id ) AND
(
TransactionAdjustmentRequest.reportMonth = '01/01/2011 00:0:0'
AND
TransactionAdjustmentRequest.status In ( 'APPROVED' )
)