qry work in one db but not another

grnzbra

Registered User.
Local time
Today, 19:01
Joined
Dec 5, 2001
Messages
376
I have two database which are very similar. They both use the same back end table. I have a query that I developed in one db and it works correctly there. However, when I import it into the other db, I get the Ambiguous Outer Joins message. I've tried copying the SQL from one and pasting it into the other and that didn't help. I've checked the table used and they are all properly linked in the second db. Any ideas about what could be going on?
 
One of the tables in the query is named differently in the other database.

1) Post your query here
2) Run this on both databases

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=4));

3) Post results here

Is the query using SQL Server linked tables? If so, the owner of the object comes along with the name. A lot of people will remove the owner from the name, then, not remove it in another DB. It can then be difficult to identify the issue.
 
1: SELECT MVADMP_LENDER.SELR_SRVR_NO AS [Lender Number], MVADMP_LENDER.NAME AS [Lender Name], MVADMP_STRUCTURED_FACILITY.DL_NME AS [CF Name], MVADMP_POOL.POOL_NO AS [Pool Number], MVADMP_POOL.SECU_FACE_VAL_AMT AS UPB, MVADMP_SECURITY.CUSIP_NO AS [CUSIP#], IIf(MVADMP_EXECUTION_TYPE.VALUE="MBS","Fixed Rate",MVADMP_EXECUTION_TYPE.VALUE) AS Execution, "fed" AS Agent, MVADMP_SECURITY.PUR_PRC_PCT AS PurchasePrice, [qry_Wiring Instruction-DVP].[DVP Wire Face Amount]-[qry_Wiring Instruction-DVP].[Proceeds Amount] AS Discount, [qry_Wiring Instruction-DVP].[Proceeds Amount] AS Proceeds, MVADMP_SECURITY.BK_ENT_DT AS [Wire Date], MVADMP_SECURITY.ISS_DT AS [Issue Date], MVADMP_POOL.LAST_LN_MTRY_DT AS [Maturity Date], MVADMP_POOL_STATISTICS.WT_AVG_MTRY_TERM AS Term, MVADMP_POOL.POOL_PRFX_CD AS Prefix, MVADMP_POOL_STATISTICS.WT_AVG_GNTY_FEE_RT AS [Guaranty Fee], Min(MVADMP_LOAN.SRVG_FEE_RT_PCT) AS Servicing, First(MVADMP_LOAN.LQDY_FEE_PCT) AS [Liquidity Fee], IIf(MVADMP_POOL_STATISTICS.LN_CNT>1,MVADMP_POOL_STATISTICS.LN_CNT & " Loans",MVADMP_LOAN.FM_LN_NO) AS [Fannie Mae Loan #], IIf(MVADMP_STRUCTURED_FACILITY_TYPE.value Is Not Null,MVADMP_STRUCTURED_FACILITY_TYPE.value,MVADMP_TRANSACTION_TYPE.value) AS Structure, [CF Info].[MSFMS Deal ID] AS Code, MVADMP_SECURITY.TRD_DT AS [Commitment Date], MVADMP_STRUCTURED_FACILITY.DL_ID, MVADMP_DEAL_TERMS.CLSNG_DT, MVADMP_POOL_STATISTICS.LN_CNT, [tbl_Security Meta Status].[Meta Status], MVADMP_SECURITY_STATUS_TYPE.VALUE
FROM (((((((((((((((MVADMP_LENDER RIGHT JOIN MVADMP_STRUCTURED_FACILITY ON MVADMP_LENDER.SELR_SRVR_NO = MVADMP_STRUCTURED_FACILITY.SELR_SRVR_NO) RIGHT JOIN MVADMP_SECURITY ON MVADMP_STRUCTURED_FACILITY.DL_ID = MVADMP_SECURITY.DL_ID) RIGHT JOIN MVADMP_POOL ON MVADMP_SECURITY.CUSIP_NO = MVADMP_POOL.CUSIP_NO) LEFT JOIN MVADMP_DEAL_TERMS ON MVADMP_STRUCTURED_FACILITY.DL_ID = MVADMP_DEAL_TERMS.DL_ID) LEFT JOIN MVADMP_TRANSACTION_TYPE ON MVADMP_STRUCTURED_FACILITY.TRAN_TYP_ID = MVADMP_TRANSACTION_TYPE.TRAN_TYP_ID) LEFT JOIN MVADMP_WIRING_INSTRUCTION ON MVADMP_SECURITY.CUSIP_NO = MVADMP_WIRING_INSTRUCTION.CUSIP_NO) LEFT JOIN MVADMP_POOL_STATISTICS ON MVADMP_POOL.POOL_NO = MVADMP_POOL_STATISTICS.POOL_NO) LEFT JOIN MVADMP_EXECUTION_TYPE ON MVADMP_SECURITY.EXCN_TYP_ID = MVADMP_EXECUTION_TYPE.EXCN_TYP_ID) LEFT JOIN MVADMP_STRUCTURED_FACILITY_TYPE ON MVADMP_STRUCTURED_FACILITY.STRCD_FAC_TYP_ID = MVADMP_STRUCTURED_FACILITY_TYPE.STRCD_FAC_TYP_ID) LEFT JOIN MVADMP_DEAL_STATUS_TYPE ON MVADMP_STRUCTURED_FACILITY.DL_STAT_TYP_ID = MVADMP_DEAL_STATUS_TYPE.DL_STAT_TYP_ID) LEFT JOIN MVADMP_SECURITY_STATUS_TYPE ON MVADMP_SECURITY.SECU_STAT_TYP_ID = MVADMP_SECURITY_STATUS_TYPE.SECU_STAT_TYP_ID) LEFT JOIN MVADMP_LOAN ON MVADMP_POOL.POOL_NO = MVADMP_LOAN.POOL_NO) LEFT JOIN [CF Info] ON MVADMP_SECURITY.DL_ID = [CF Info].[MSFMS Deal ID]) LEFT JOIN [qry_Wiring Instruction-DVP] ON MVADMP_SECURITY.CUSIP_NO = [qry_Wiring Instruction-DVP].[CUSIP Number]) LEFT JOIN [qry_Wiring Instruction-Free] ON MVADMP_SECURITY.CUSIP_NO = [qry_Wiring Instruction-Free].[CUSIP Number]) LEFT JOIN [tbl_Security Meta Status] ON MVADMP_SECURITY.SECU_STAT_TYP_ID = [tbl_Security Meta Status].SECU_STAT_TYP_ID
GROUP BY MVADMP_LENDER.SELR_SRVR_NO, MVADMP_LENDER.NAME, MVADMP_STRUCTURED_FACILITY.DL_NME, MVADMP_POOL.POOL_NO, MVADMP_POOL.SECU_FACE_VAL_AMT, MVADMP_SECURITY.CUSIP_NO, IIf(MVADMP_EXECUTION_TYPE.VALUE="MBS","Fixed Rate",MVADMP_EXECUTION_TYPE.VALUE), "fed", MVADMP_SECURITY.PUR_PRC_PCT, [qry_Wiring Instruction-DVP].[DVP Wire Face Amount]-[qry_Wiring Instruction-DVP].[Proceeds Amount], [qry_Wiring Instruction-DVP].[Proceeds Amount], MVADMP_SECURITY.BK_ENT_DT, MVADMP_SECURITY.ISS_DT, MVADMP_POOL.LAST_LN_MTRY_DT, MVADMP_POOL_STATISTICS.WT_AVG_MTRY_TERM, MVADMP_POOL.POOL_PRFX_CD, MVADMP_POOL_STATISTICS.WT_AVG_GNTY_FEE_RT, IIf(MVADMP_POOL_STATISTICS.LN_CNT>1,MVADMP_POOL_STATISTICS.LN_CNT & " Loans",MVADMP_LOAN.FM_LN_NO), IIf(MVADMP_STRUCTURED_FACILITY_TYPE.value Is Not Null,MVADMP_STRUCTURED_FACILITY_TYPE.value,MVADMP_TRANSACTION_TYPE.value), [CF Info].[MSFMS Deal ID], MVADMP_SECURITY.TRD_DT, MVADMP_STRUCTURED_FACILITY.DL_ID, MVADMP_DEAL_TERMS.CLSNG_DT, MVADMP_POOL_STATISTICS.LN_CNT, [tbl_Security Meta Status].[Meta Status], MVADMP_SECURITY_STATUS_TYPE.VALUE
ORDER BY MVADMP_POOL.POOL_NO;

3:
Name Type
dbo_CommtPlus 4
dbo_MtgLn 4
MVADMP_ACCRUAL_RATE_METHOD_TYPE 4
MVADMP_ADDITION_TEST_TYPE 4
MVADMP_ADDL_COLLATERAL_ALLOWED_TYPE 4
MVADMP_ALLOCABLE_FACILITY_FRQY_TYPE 4
MVADMP_ALLOWED_EXECUTION_TYPE 4
MVADMP_AMI_THRESHOLD_PERCENT_TYPE 4
MVADMP_AMORTIZATION_TERM_TYPE 4
MVADMP_APN_POOL_NUMBER 4
MVADMP_ASMP_ALWD_TYPE 4
MVADMP_BORROWER_LOAN_PRPS_DTL_CD_TYPE 4
MVADMP_BORROWER_LOAN_PURPOSE_TYPE 4
MVADMP_BORROWER_UP_ALLOWED_TYPE 4
MVADMP_BUILDING_TYPE 4
MVADMP_BUSINESS_EVENT_TYPE 4
MVADMP_CASH_BOND 4
MVADMP_CERTIFIED_PROP_VALUES 4
MVADMP_CF_APP_ENV 4
MVADMP_CF_CONTACT 4
MVADMP_CF_CONTACT_TYPE 4
MVADMP_CLOSED_LOAN 4
MVADMP_CLOSED_POOL 4
MVADMP_COLLATERAL 4
MVADMP_COLLATERAL_GROUP 4
MVADMP_COLLATERAL_GROUP_STATE_TYPE 4
MVADMP_COLLATERAL_PROPERTY 4
MVADMP_COLLATERAL_PURPOSE_TYPE 4
MVADMP_COLLATERAL_STATE_TYPE 4
MVADMP_COLLATERAL_TYPE 4
MVADMP_COLT_GROUP_CONTRACT_COMPLIANCE 4
MVADMP_COLT_REF_NO_RANGE 4
MVADMP_COMPLEX_FEE 4
MVADMP_COMPLEX_FEE_PARAMETER_SET 4
MVADMP_COMPLEX_FEE_TEMPLATE_TYPE 4
MVADMP_COMPLEX_FEE_TYPE 4
MVADMP_CONTACT 4
MVADMP_CONTACT_ROLES 4
MVADMP_CONTACT_TYPE 4
MVADMP_COUNTERPARTY_TYPE 4
MVADMP_CTRT_CPNC_ADDN_TEST_TYPE_POOL 4
MVADMP_CTRT_CPNC_ADDN_TEST_TYPE_PROP 4
MVADMP_CTRT_CPNC_ALCB_FAC_TYPE 4
MVADMP_DEAL_CONTACTS 4
MVADMP_DEAL_PARTICIPANT 4
MVADMP_DEAL_STATUS_TYPE 4
MVADMP_DEAL_TERMS 4
MVADMP_DEAL_TERMS_AMRT_TERM_TYPE 4
MVADMP_DEAL_TERMS_COLLATERAL_TYPE 4
MVADMP_DEAL_TERMS_EXECUTION_TYPE 4
MVADMP_DEFEASANCE_TYPE 4
MVADMP_DWELLING_UNIT 4
MVADMP_ELDERLY_PROPERTY_TYPE 4
MVADMP_ESCROW_REVALUE_FREQUENCY_TYPE 4
MVADMP_EXECUTION_TYPE 4
MVADMP_FEE_GROUP 4
MVADMP_FEE_GROUP_STATE_TYPE 4
MVADMP_FEE_LEDGER 4
MVADMP_FHA_VA_SECTION_ACT_TYPE 4
MVADMP_FIXED_PREPAY_SCHEDULE_TYPE 4
MVADMP_FM_HOLIDAY 4
MVADMP_FM_INVESTOR_BORROWER_TYPE 4
MVADMP_GUARANTY_TYPE 4
MVADMP_HEDGE 4
MVADMP_HEDGE_INDEX_TYPE 4
MVADMP_HEDGE_PROPERTY 4
MVADMP_HEDGE_STATE_TYPE 4
MVADMP_INT_RATE_HEDGE_SHARING_TYPE 4
MVADMP_INTEREST_ACCRUAL_METHOD_TYPE 4
MVADMP_INTEREST_TYPE 4
MVADMP_LAND_OWNERSHIP_RIGHTS_TYPE 4
MVADMP_LARGE_BORROWER 4
MVADMP_LEGAL_CONFORMANCE_STATUS_TYPE 4
MVADMP_LENDER 4
MVADMP_LETTER_OF_CREDIT 4
MVADMP_LIEN_PRIORITY_TYPE 4
MVADMP_LOAN 4
MVADMP_LOAN_NO_RANGE 4
MVADMP_LOAN_PLANNED_REHAB_TYPE 4
MVADMP_LOAN_PREPAYMENT 4
MVADMP_LOAN_SPCL_FEAT_TYPE 4
MVADMP_LOAN_SPCL_FEAT_TYPE_DRVD 4
MVADMP_LOAN_TYPE 4
MVADMP_LOC_COLLATERAL_GROUP 4
MVADMP_LOSS_SHARING_LEVEL_TYPE 4
MVADMP_MESSAGE_EVENT_LOG 4
MVADMP_MESSAGE_TYPE 4
MVADMP_MONITORING_REQD_FRQY_TYPE 4
MVADMP_MONTHLY_DMBS_FEES_CLCTD_TYPE 4
MVADMP_OPERATING_STATEMENT 4
MVADMP_OPERATING_STATEMENT_TYPE 4
MVADMP_OTHER_COLLATERAL 4
MVADMP_OTHER_COLLATERAL_COLT_GROUP 4
MVADMP_OTHER_COLLATERAL_TYPE 4
MVADMP_PARTICIPANT_STAT_TYPE 4
MVADMP_PAYMENT_ELECTRIC_SOURCE_TYPE 4
MVADMP_PAYMENT_FREQUENCY_TYPE 4
MVADMP_PAYMENT_GAS_SOURCE_TYPE 4
MVADMP_POOL 4
MVADMP_POOL_CLOSE_TYPE 4
MVADMP_POOL_STATISTICS 4
MVADMP_POOL_STATUS_TYPE 4
MVADMP_PRICING_TIER_TYPE 4
MVADMP_PROP_SPCL_FEAT_TYPE 4
MVADMP_PROP_SPCL_FEAT_TYPE_DRVD 4
MVADMP_PROPERTY 4
MVADMP_PROPERTY_CONSTRUCTION_PHASE 4
MVADMP_PROPERTY_FINANCIAL_TYPE 4
MVADMP_PROVIDER 4
MVADMP_PROVIDER_RECOURSE 4
MVADMP_PROVIDER_RECOURSE_TYPE 4
MVADMP_RECOURSE_TYPE 4
MVADMP_REGION_TYPE 4
MVADMP_REMITTANCE_TYPE 4
MVADMP_ROLES 4
MVADMP_RULESET 4
MVADMP_SECURITY 4
MVADMP_SECURITY_EDIT_OVERRIDE_COMMENT 4
MVADMP_SECURITY_STATUS_TYPE 4
MVADMP_SENIOR_HOUSING_TYPE 4
MVADMP_SF_AUDIT 4
MVADMP_SPCL_DELIVERY_INSTRUCTION_TYPE 4
MVADMP_SPCL_FEATURE_TYPE 4
MVADMP_SPCL_FLOOD_HZRD_INSURANCE_TYPE 4
MVADMP_SPECIAL_NEED_TYPE 4
MVADMP_STATE_TYPE 4
MVADMP_STRUCTURED_FACILITY 4
MVADMP_STRUCTURED_FACILITY_TYPE 4
MVADMP_SUB_LEDG_ACCT 4
MVADMP_SUBSEQUENT_HEDGE_TYPE 4
MVADMP_TAX_CREDIT_PERCENT_TYPE 4
MVADMP_TRANSACTION_TYPE 4
MVADMP_VALUATION_FRQY_TYPE 4
MVADMP_VALUATION_METHOD_TYPE 4
MVADMP_WIRING_INSTRUCTION 4
MVADMP_WIRING_INSTRUCTION_TYPE 4

The back end is an Oracle database.

The query is an attempt to simulate a local table that is loaded using four action queries. The word has come down from the upper management that there will be no more local tables; all local tables must be simulated with queries going against the original tables. [CF Info] is another such query.
 
Last edited:
I second the No Local Tables mandate.

What is the result set from the second database? #3 should have two sets of data. A listing from Database A and a listing from Database B. These two items are what are needed to be compared for the objects referenced in your query. Here is where your difference is.
 
How do I run that? I assumed that it was supposed to be a query in the second db. Should I run it twice, or is it run some other way that it looks at both dbs?
 

Users who are viewing this thread

Back
Top Bottom