Inner join - Outer join probs?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inner join - Outer join probs??

Author

Topic

miah00
Starting Member

2 Posts

Posted - 2004-06-10 : 11:56:55

The table 'ORGSN is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

I keep getting the above error message. Any ideas on what could be wrong??

I think it has got something to do with the following lines:

and (Counterparty.OGID=CPartyAlias.OGID_CHILD
and CPartyAlias.ASPP= 'ALIAS'
and CPartyAlias.OGID_PARENT = 'REUTERS')

The code can be seen below ( abit long!)

select isnull(LIMITKEY.ANLS_RISK_SET,'') ANLS_RISK_SET,LIMITKEY.DBID_RISK_CAT,LIMITKEY.DBID_LIMIT_KEY,

isnull(LIMITKEY.UID_RISK_POINT,'?') UID_RISK_POINT,isnull(LIMITKEY.OGID_LIMIT,'?')
OGID_LIMIT,LIMITKEY.DBID_LIMIT_DETAIL_ACTIVE,LIMITDETAIL.DBID_LIMIT_DETAIL,LIMITDETAIL.DBID_STAGE StgDBID,
LIMITDETAIL.INST_CCY_LIMIT,LIMITDETAIL.LOGL_MEMO,
isnull(SUBPERIOD.GSNM,'') 'GSNM',isnull(SUBPERIOD.DATE_START,'') 'DATE_START',isnull(SUBPERIOD.DATE_END,'') 'DATE_END',
LIMITCAPDETAIL.SEQN_SUBPERIOD,LIMITCAPDETAIL.AMTA_ORIG_LIMIT,LIMITCAPDETAIL.AMTA_XFR_ADJUST,LIMITCAPDETAIL.AMTA_AUTH_EXCESS,
LIMITCAPDETAIL.DATE_START LimitCapDetailDateStart,LIMITCAPDETAIL.DATE_END LimitCapDetailDateEnd,
(LIMITCAPDETAIL.AMTA_ORIG_LIMIT+LIMITCAPDETAIL.AMTA_XFR_ADJUST+LIMITCAPDETAIL.AMTA_AUTH_EXCESS) 'AUTHLIMIT',
LIMITTYPE.DBID_STAGE_SET StgSetDBID,LIMITTYPE.UID UID_LIMIT_TYPE,LIMITTYPE.LTCH_CHARACTERISTIC,
LIMITTYPE.LOGL_BALLOONDATES,RISKCAT.UID UID_RISK_CAT,
RISKCAT.UID RiskCategoryName,isnull(( OrgUnit.DOGI_ORG_NAME +'-'+isnull(OrgUnit.OGID, LIMITKEY.OGID_LIMIT)),
'?') OrgUnitName,LIMITTYPE.UID LimitTypeName,STAGESET.UID StageSetName,STAGE.UID StageName,
isnull( Counterparty.DOGI_ORG_NAME , '?') RiskPointName

from LIMITKEY,LIMITDETAIL,LIMITCAPDETAIL,SUBPERIOD,ORGSN Counterparty ,ORGRFREF CPartyAlias,ORGSN OrgUnit,
RISKCAT,LIMITTYPE,STAGESET,STAGE

where LIMITKEY.DBID_LIMIT_KEY=LIMITDETAIL.DBID_LIMIT_KEY

and LIMITDETAIL.DBID_LIMIT_DETAIL=LIMITCAPDETAIL.DBID_LIMIT_DETAIL
and LIMITDETAIL.LOGL_PROFORMA='1'
and LIMITDETAIL.DBID_PERIOD*=SUBPERIOD.DBID_PERIOD
and LIMITCAPDETAIL.SEQN_SUBPERIOD*=SUBPERIOD.SEQN
and LIMITKEY.UID_RISK_POINT='?'
and convert(varchar(10),LIMITKEY.UID_RISK_POINT)*=Counterparty.OGID
and (Counterparty.OGID=CPartyAlias.OGID_CHILD
and CPartyAlias.ASPP= 'ALIAS'
and CPartyAlias.OGID_PARENT = 'REUTERS')
and LIMITKEY.OGID_LIMIT='?'
and LIMITKEY.OGID_LIMIT*=OrgUnit.OGID
and RISKCAT.UID='CPARTY'
and LIMITKEY.DBID_RISK_CAT=RISKCAT.DBID
and LIMITKEY.DBID_LIMIT_TYPE=LIMITTYPE.DBID
and LIMITTYPE.DBID_STAGE_SET=STAGESET.DBID_STAGE_SET
and LIMITDETAIL.DBID_STAGE=STAGE.DBID_STAGE

order by RiskCategoryName,RiskPointName,OrgUnitName,LimitTypeName,StageName,SEQN_SUBPERIOD

Thanks for any help

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-10 : 12:32:43

what version?

I'd rewrite it to use ansi joins...

And I only see it here

and LIMITKEY.OGID_LIMIT='?'
and LIMITKEY.OGID_LIMIT*=OrgUnit.OGID

I'd say sql is throwing an erroneous error...

I think you confused the optimizer..

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources