This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
I’m attempting meet a requirement to display inventory items below their reorder point in a results set.
The catch is the inventory.curbaltotal field is a non-persistent field .
In theory its: where inventory.curbaltotal < minlevel which seems simple
Any thoughts?
select * from inventory Where minlevel > (select sum(curbal) from invbalances where itemnum = inventory.itemnum and location = inventory.location and itemsetid = inventory.itemsetid)
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Lonnie Stone
Sent: 31 October 2013 12:43
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] display inventory items below their reorder point in a results set
I'm attempting meet a requirement to display inventory items below their reorder point in a results set.
The catch is the inventory.curbaltotal field is a non-persistent field .
In theory its: where inventory.curbaltotal < minlevel which seems simple
Any thoughts?
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************
something like
select * from inventory
LEFT OUTER JOIN invbalances on invbalances.itemnum = inventory.itemnum and invbalances.siteid = inventory.siteid and invbalances.location = inventory.location
LEFT OUTER JOIN item ON item.itemnum = inventory.itemnum AND item.itemsetid = inventory.itemsetid
then add your where clause
---In maximo@yahoogroups.com, <ian.wright@...> wrote:
select * from inventory Where minlevel > (select sum(curbal) from invbalances where itemnum = inventory.itemnum and location = inventory.location and itemsetid = inventory.itemsetid)
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Lonnie Stone
Sent: 31 October 2013 12:43
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] display inventory items below their reorder point in a results set
I’m attempting meet a requirement to display inventory items below their reorder point in a results set.
The catch is the inventory.curbaltotal field is a non-persistent field .
In theory its: where inventory.curbaltotal < minlevel which seems simple
Any thoughts?
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
****************************************************************************************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
****************************************************************************************************************************************************************************************
you should join the result set with invbalances table which has curbal as a persistent field.
---In MAXIMO@yahoogroups.com, <lonnie_s_stone@...> wrote:
I’m attempting meet a requirement to display inventory items below their reorder point in a results set.
The catch is the inventory.curbaltotal field is a non-persistent field .
In theory its: where inventory.curbaltotal < minlevel which seems simple
Any thoughts?
select itemnum, siteid , location ,minlevel from inventory where exists (select null from invbalances
where itemnum = inventory.itemnum and siteid = inventory.siteid and location = inventory.location
group by itemnum,siteid,location having SUM(curbal) < inventory.minlevel)
or
select itemnum, siteid , location ,minlevel from inventory where minlevel < (select SUM(curbal) from invbalances
where itemnum = inventory.itemnum and siteid = inventory.siteid and location = inventory.location )
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of akiporter@yahoo.com
Sent: 21 November 2013 05:45
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] RE: display inventory items below their reorder point in a results set
you should join the result set with invbalances table which has curbal as a persistent field.
---In MAXIMO@yahoogroups.com<mailto:MAXIMO@yahoogroups.com>, <lonnie_s_stone@...<mailto:lonnie_s_stone@...>> wrote:
I’m attempting meet a requirement to display inventory items below their reorder point in a results set.
The catch is the inventory.curbaltotal field is a non-persistent field .
In theory its: where inventory.curbaltotal < minlevel which seems simple
Any thoughts?
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************