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.
The statement should show all inventory items where the current balance is less than the reorder point. I need help converting the following statement to a where clause I can plug into Maximo.
SELECT MAXIMO_INVENTORY.ITEMNUM, MAXIMO_INVENTORY.REORDER, MAXIMO_INVBALANCES.CURBAL
FROM MAXIMO_INVENTORY INNER JOIN MAXIMO_INVBALANCES ON MAXIMO_INVENTORY.ITEMNUM = MAXIMO_INVBALANCES.ITEMNUM
WHERE (((MAXIMO_INVBALANCES.CURBAL)<[MAXIMO_INVENTORY].[REORDER]));
Thanks
Dear,
This what you should type in the where clause of inventory application:
minlevel > (select sum(curbal) from invbalances where itemnum=inventory.itemnum and location=inventory.location and siteid=inventory.siteid)
Best Regards,
Amir Anwar
________________________________
From: mutss1 <mutss1@hotmail.com>
To: maximo@yahoogroups.com
Sent: Monday, October 22, 2012 5:23 PM
Subject: [MAXIMO List] Need SQL Statement converted to Where Clause I can use in Maximo
The statement should show all inventory items where the current balance is less than the reorder point. I need help converting the following statement to a where clause I can plug into Maximo.
SELECT MAXIMO_INVENTORY.ITEMNUM, MAXIMO_INVENTORY.REORDER, MAXIMO_INVBALANCES.CURBAL
FROM MAXIMO_INVENTORY INNER JOIN MAXIMO_INVBALANCES ON MAXIMO_INVENTORY.ITEMNUM = MAXIMO_INVBALANCES.ITEMNUM
WHERE (((MAXIMO_INVBALANCES.CURBAL)<[MAXIMO_INVENTORY].[REORDER]));
Thanks
Try THIS
exists ( SELECT null
FROM Inventory a JOIN
(SELECT sum(curbal) as sumcurbal,itemnum,itemsetid,location FROM invbalances
group by itemnum,itemsetid,location) tt
ON a.itemnum = tt.itemnum and a.itemsetid = tt.itemsetid and a.reorder >= tt.sumcurbal
and a.location = tt.location
where inventory.itemnum = tt.itemnum and inventory.itemsetid = tt.itemsetid
and inventory.location = tt.location)
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of mutss1
Sent: 22 October 2012 16:24
To: maximo@yahoogroups.com
Subject: [MAXIMO List] Need SQL Statement converted to Where Clause I can use in Maximo
The statement should show all inventory items where the current balance is less than the reorder point. I need help converting the following statement to a where clause I can plug into Maximo.
SELECT MAXIMO_INVENTORY.ITEMNUM, MAXIMO_INVENTORY.REORDER, MAXIMO_INVBALANCES.CURBAL
FROM MAXIMO_INVENTORY INNER JOIN MAXIMO_INVBALANCES ON MAXIMO_INVENTORY.ITEMNUM = MAXIMO_INVBALANCES.ITEMNUM
WHERE (((MAXIMO_INVBALANCES.CURBAL)<[MAXIMO_INVENTORY].[REORDER]));
Thanks
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.
**************************************************************************************************************
Thanks Amir. That seemed to do the trick.
--- In MAXIMO@yahoogroups.com, amir samir <amir_s_anwar@...> wrote:
>
> Dear,
> This what you should type in the where clause of inventory application:
>
> minlevel > (select sum(curbal) from invbalances where itemnum=inventory.itemnum and location=inventory.location and siteid=inventory.siteid)
>
>
> Best Regards,
> Amir Anwar
>
>
> ________________________________
> From: mutss1 <mutss1@...>
> To: maximo@yahoogroups.com
> Sent: Monday, October 22, 2012 5:23 PM
> Subject: [MAXIMO List] Need SQL Statement converted to Where Clause I can use in Maximo
>
>
> Â
> The statement should show all inventory items where the current balance is less than the reorder point. I need help converting the following statement to a where clause I can plug into Maximo.
>
> SELECT MAXIMO_INVENTORY.ITEMNUM, MAXIMO_INVENTORY.REORDER, MAXIMO_INVBALANCES.CURBAL
> FROM MAXIMO_INVENTORY INNER JOIN MAXIMO_INVBALANCES ON MAXIMO_INVENTORY.ITEMNUM = MAXIMO_INVBALANCES.ITEMNUM
> WHERE (((MAXIMO_INVBALANCES.CURBAL)<[MAXIMO_INVENTORY].[REORDER]));
>
> Thanks
>
>
>
>
>
>
Ian,
I tried your statement but it returned several results where the current balance was greater than the reorder point. Amir's solution seems to get me what I need. Thanks.
--- In MAXIMO@yahoogroups.com, Ian Wright <ian.wright@...> wrote:
>
> Try THIS
>
> exists ( SELECT null
> FROM Inventory a JOIN
> (SELECT sum(curbal) as sumcurbal,itemnum,itemsetid,location FROM invbalances
> group by itemnum,itemsetid,location) tt
> ON a.itemnum = tt.itemnum and a.itemsetid = tt.itemsetid and a.reorder >= tt.sumcurbal
> and a.location = tt.location
> where inventory.itemnum = tt.itemnum and inventory.itemsetid = tt.itemsetid
> and inventory.location = tt.location)
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of mutss1
> Sent: 22 October 2012 16:24
> To: maximo@yahoogroups.com
> Subject: [MAXIMO List] Need SQL Statement converted to Where Clause I can use in Maximo
>
>
>
> The statement should show all inventory items where the current balance is less than the reorder point. I need help converting the following statement to a where clause I can plug into Maximo.
>
> SELECT MAXIMO_INVENTORY.ITEMNUM, MAXIMO_INVENTORY.REORDER, MAXIMO_INVBALANCES.CURBAL
> FROM MAXIMO_INVENTORY INNER JOIN MAXIMO_INVBALANCES ON MAXIMO_INVENTORY.ITEMNUM = MAXIMO_INVBALANCES.ITEMNUM
> WHERE (((MAXIMO_INVBALANCES.CURBAL)<[MAXIMO_INVENTORY].[REORDER]));
>
> Thanks
>
>
> 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.
> **************************************************************************************************************
>
>
>
>
>
Just to ask - what database, what version of Maximo and how often/where in Maximo is this to be ran? (ie, report; occasionally/ startcenter; all the time/ workflow engine; potentially a lot of records...)
R/
Will
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of mutss1
Sent: Monday, October 22, 2012 11:40 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] Need SQL Statement converted to Where Clause I can use in Maximo
Thanks Amir. That seemed to do the trick.
--- In MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>, amir samir <amir_s_anwar@...<mailto:amir_s_anwar@...>> wrote:
>
> Dear,
> This what you should type in the where clause of inventory application:
>
> minlevel > (select sum(curbal) from invbalances where itemnum=inventory.itemnum and location=inventory.location and siteid=inventory.siteid)
>
>
> Best Regards,
> Amir Anwar
>
>
> ________________________________
> From: mutss1 <mutss1@...<mailto:mutss1@...>>
> To: maximo@yahoogroups.com<mailto:maximo%40yahoogroups.com>
> Sent: Monday, October 22, 2012 5:23 PM
> Subject: [MAXIMO List] Need SQL Statement converted to Where Clause I can use in Maximo
>
>
> Â
> The statement should show all inventory items where the current balance is less than the reorder point. I need help converting the following statement to a where clause I can plug into Maximo.
>
> SELECT MAXIMO_INVENTORY.ITEMNUM, MAXIMO_INVENTORY.REORDER, MAXIMO_INVBALANCES.CURBAL
> FROM MAXIMO_INVENTORY INNER JOIN MAXIMO_INVBALANCES ON MAXIMO_INVENTORY.ITEMNUM = MAXIMO_INVBALANCES.ITEMNUM
> WHERE (((MAXIMO_INVBALANCES.CURBAL)<[MAXIMO_INVENTORY].[REORDER]));
>
> Thanks
>
>
>
>
>
>
Hi again ,
The query should be:
minlevel >= (select isnull(sum(curbal),0) from invbalances where itemnum=inventory.itemnum and location=inventory.location and siteid=inventory.siteid))
the update is for the items that doesn't have a record in the invbalances table.
Best Regards
________________________________
From: mutss1 <mutss1@hotmail.com>
To: MAXIMO@yahoogroups.com
Sent: Monday, October 22, 2012 6:39 PM
Subject: Re: [MAXIMO List] Need SQL Statement converted to Where Clause I can use in Maximo
Thanks Amir. That seemed to do the trick.
--- In MAXIMO@yahoogroups.com, amir samir <amir_s_anwar@...> wrote:
>
> Dear,
> This what you should type in the where clause of inventory application:
>
> minlevel > (select sum(curbal) from invbalances where itemnum=inventory.itemnum and location=inventory.location and siteid=inventory.siteid)
>
>
> Best Regards,
> Amir Anwar
>
>
> ________________________________
> From: mutss1 <mutss1@...>
> To: maximo@yahoogroups.com
> Sent: Monday, October 22, 2012 5:23 PM
> Subject: [MAXIMO List] Need SQL Statement converted to Where Clause I can use in Maximo
>
>
> Â
> The statement should show all inventory items where the current balance is less than the reorder point. I need help converting the following statement to a where clause I can plug into Maximo.
>
> SELECT MAXIMO_INVENTORY.ITEMNUM, MAXIMO_INVENTORY.REORDER, MAXIMO_INVBALANCES.CURBAL
> FROM MAXIMO_INVENTORY INNER JOIN MAXIMO_INVBALANCES ON MAXIMO_INVENTORY.ITEMNUM = MAXIMO_INVBALANCES.ITEMNUM
> WHERE (((MAXIMO_INVBALANCES.CURBAL)<[MAXIMO_INVENTORY].[REORDER]));
>
> Thanks
>
>
>
>
>
>