Inner join - Outer join porbs??? - 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

Transact-SQL (2000)

Inner join - Outer join porbs???

Author

Topic

miah00
Starting Member

2 Posts

Posted - 2004-06-10 : 11:54:17

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-11 : 11:43:39

Try it using ansi syntax

from tbl1
join tbl2
on ....
left join tbl3
on ...
join tbl4
on ...

That allows left joind tables to particpate in further joins - you probably want them to be left joins though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-11 : 11:56:42

great minds think alike...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36159

cross post

Brett

8-)

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-06-11 : 11:57:30

If this is SQL 7 or SQL 2k then Books online states that you should not use the *= (t-sql joins). It goes on to say that all T-SQL joins should be changed to the SQL-92 standard, as NR suggested. I had done testing with this a few years ago and noted that the SQL-92 joins are faster than T-SQL joins.

This may not be an option for you though if you are using SQL 6.5. I dont know if 6.5 even had SQL-92 joins.

In any case, type *= into the keyword find under the INDEX tab in books online and you will find the entry for "*= operator" that explains the newer syntax.

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources