| Help :: Very Complicated Query [message #3021] |
Fri, 30 August 2002 07:11  |
Sal wheelar
Messages: 3
Registered: February 2002
|
Junior Member |
|
|
It's weird requirement.
The following Query works Okay.
select c.GenericName, d.Form, d.Strength, c.ItemId "NewItemId", a.ItemId "MainItemId", e.DisplayDispenseSize, a.PrimaryAHFS, a.Formularystatus, a.RefillLimit, a.DaysRxValid, a.PrimaryAHFSDescription, a.MaxRefills,
from ItemInventory a, ItemTherapyDefault b, ItemInventory c, MasterInventory d, ItemTherapyDefault e
where a.ItemId=b.ItemId
and a.ItemId=1567
and b.ItFluid=c.Mnemonic(+)
and c.GenericCode=d.GenericCode(+)
and a.FacilityCode=c.FacilityCode
and c.ItemId=e.ItemId and e.ServiceType=b.ServiceType and e.TherapyType=b.TherapyType
Order By a.ItemId, b.ServiceType, b.TherapyType
I can explain this query little bit..
ItemInventory a and ItemTherapyDefault b are tables in first join. Now based on one column value in ItemTherapyDefault I have to get new rows from ItemInventory and with the new values from ItemInventory I have to again go to ItemTherapyDefault table. It is like closed loop.
The above query works only when there is data in b.ItFluid column.
I tried to make outer join like following
select c.GenericName, d.Form, d.Strength, c.ItemId "NewItemId", a.ItemId "MainItemId", e.DisplayDispenseSize, a.PrimaryAHFS, a.Formularystatus, a.RefillLimit, a.DaysRxValid, a.PrimaryAHFSDescription, a.MaxRefills,
b.ServiceType, b.TherapyType,
from ItemInventory a, ItemTherapyDefault b, iteminventory c, masterinventory d, ItemTherapyDefault e
where a.ItemId=b.ItemId
and a.ItemId=688
and b.ItFluid=c.Mnemonic(+)
and c.GenericCode=d.GenericCode(+)
and a.FacilityCode=c.FacilityCode(+)
and c.ItemId=e.ItemId(+) and b.ServiceType=e.ServiceType(+) and b.TherapyType=e.TherapyType(+)
Order By a.ItemId, b.ServiceType, b.TherapyType
I am getting here as Error as
" A table may be outer joined to at most one other table"
Any ideas ????????????
Thanks
|
|
|
|
| Re: Help :: Very Complicated Query [message #3074 is a reply to message #3021] |
Tue, 03 September 2002 03:57  |
Mark
Messages: 284
Registered: July 1998
|
Senior Member |
|
|
Would using a VIEW help here - then you could break your query down a bit.
Use an outer join to bring back all the records you require - then query that view using another outer join.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
