SQL schema doesnt match BO designer schema

[Former Member]

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

)

[MariannevL · Accepted solution]

hi ilbo,

You have your universe generation native (=legacy/old) join syntax, in the universe parameters you can switch that to use ANSI instead. Note that for normal inner joins the results will be the same, the main difference is in handling outer joins.

Also you may not have set your contexts correctly if you want BO to split the query in two (chasm trap may be).

Double check those, set the cardinalities right and hit the detect context button...

Good luck,

Marianne

[Former Member · Accepted solution]

thanks Praveen and Marianne!

I will test asap and let you know the outcome

[Former Member]

copy each and every table include with joins( Ctrl + A ) and paste on new universe. you will get correct result.

All the best

Praveen