Skip to Content
0
Former Member
Feb 21, 2011 at 03:44 AM

SQL schema doesnt match BO designer schema

32 Views

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' )

)