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.
Hey everyone I'm currently working on a PO report for our inspection department and I would like to know how to not have it return all po lines.
In my sql I also have a few joins
companies
poline
postatus
glcomponents
Hope someone can shed some light on this.
Maximo 6.2.1
Weblogic
SQL 2005
thx,
mrggutz
Can you post your SQL and some details on what lines you want to exclude?
On Dec 13, 2012, at 6:46 PM, "George" <ggutierrez@pasadenarefining.com> wrote:
> Hey everyone I'm currently working on a PO report for our inspection department and I would like to know how to not have it return all po lines.
>
> In my sql I also have a few joins
> companies
> poline
> postatus
> glcomponents
>
> Hope someone can shed some light on this.
>
> Maximo 6.2.1
> Weblogic
> SQL 2005
>
> thx,
> mrggutz
>
>
Here is my sql query
select distinct substring(poline.gldebitacct,1,6)as CostCenter ,
glcomponents.comptext as gldesc ,po.ponum, po.description,companies.name as
VendorName,po.originalpovalue,po.
lastpovalue,totalcost,po.status,po.changeby,po.receipts,postatus.changeby
as approver,postatus.changedate from po
LEFT OUTER JOIN companies ON po.vendor = companies.company
LEFT OUTER JOIN poline ON po.ponum = poline.ponum
LEFT OUTER JOIN postatus ON po.ponum = postatus.ponum
LEFT OUTER JOIN glcomponents on substring(poline.gldebitacct,1,6) =
glcomponents.compvalue
where substring(poline.gldebitacct,1,6) = '603300'
and DATEPART(yyyy, POSTATUS.CHANGEDATE) = 2012--:Year
and po.status not in ('CAN')
and po.receipts not in ('COMPLETE')
order by po.status
Here is and example of what all its returning. I just want the first line
and not all the po lines.
(Embedded image moved to file: pic00153.gif)
Thank You,
George Gutierrez Jr.
Maximo Administration
Pasadena Refining Systems
713.920.3908 Direct
832.473.5330 Cell
U1O2 - Chave
From: Chris Lawless <lawlessc@gmail.com>
To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
Date: 12/13/2012 05:53 PM
Subject: Re: [MAXIMO List] SQL Query help
Sent by: MAXIMO@yahoogroups.com
Can you post your SQL and some details on what lines you want to exclude?
On Dec 13, 2012, at 6:46 PM, "George" <ggutierrez@pasadenarefining.com>
wrote:
> Hey everyone I'm currently working on a PO report for our inspection
department and I would like to know how to not have it return all po lines.
>
> In my sql I also have a few joins
> companies
> poline
> postatus
> glcomponents
>
> Hope someone can shed some light on this.
>
> Maximo 6.2.1
> Weblogic
> SQL 2005
>
> thx,
> mrggutz
>
>
How do you define the first line? Is it the one with the lowest line number or the first one the database happens to return?
On Dec 13, 2012, at 6:59 PM, ggutierrez@pasadenarefining.com wrote:
> Here is my sql query
>
> select distinct substring(poline.gldebitacct,1,6)as CostCenter ,
> glcomponents.comptext as gldesc ,po.ponum, po.description,companies.name as
> VendorName,po.originalpovalue,po.
> lastpovalue,totalcost,po.status,po.changeby,po.receipts,postatus.changeby
> as approver,postatus.changedate from po
> LEFT OUTER JOIN companies ON po.vendor = companies.company
> LEFT OUTER JOIN poline ON po.ponum = poline.ponum
> LEFT OUTER JOIN postatus ON po.ponum = postatus.ponum
> LEFT OUTER JOIN glcomponents on substring(poline.gldebitacct,1,6) =
> glcomponents.compvalue
> where substring(poline.gldebitacct,1,6) = '603300'
> and DATEPART(yyyy, POSTATUS.CHANGEDATE) = 2012--:Year
> and po.status not in ('CAN')
> and po.receipts not in ('COMPLETE')
> order by po.status
>
> Here is and example of what all its returning. I just want the first line
> and not all the po lines.
>
> (Embedded image moved to file: pic00153.gif)
>
>
>
>
>
> Thank You,
>
> George Gutierrez Jr.
> Maximo Administration
> Pasadena Refining Systems
> 713.920.3908 Direct
> 832.473.5330 Cell
> U1O2 - Chave
>
>
>
> From: Chris Lawless <lawlessc@gmail.com>
> To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
> Date: 12/13/2012 05:53 PM
> Subject: Re: [MAXIMO List] SQL Query help
> Sent by: MAXIMO@yahoogroups.com
>
>
>
>
>
>
> Can you post your SQL and some details on what lines you want to exclude?
>
> On Dec 13, 2012, at 6:46 PM, "George" <ggutierrez@pasadenarefining.com>
> wrote:
>
> > Hey everyone I'm currently working on a PO report for our inspection
> department and I would like to know how to not have it return all po lines.
> >
> > In my sql I also have a few joins
> > companies
> > poline
> > postatus
> > glcomponents
> >
> > Hope someone can shed some light on this.
> >
> > Maximo 6.2.1
> > Weblogic
> > SQL 2005
> >
> > thx,
> > mrggutz
> >
> >
>
>
>
>
>
>
>
>
>
Its the first one the database returns.
Thank You,
George Gutierrez Jr.
Maximo Administration
Pasadena Refining Systems
713.920.3908 Direct
832.473.5330 Cell
U1O2 - Chave
From: Chris Lawless <lawlessc@gmail.com>
To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
Date: 12/13/2012 06:04 PM
Subject: Re: [MAXIMO List] SQL Query help
Sent by: MAXIMO@yahoogroups.com
How do you define the first line? Is it the one with the lowest line number
or the first one the database happens to return?
On Dec 13, 2012, at 6:59 PM, ggutierrez@pasadenarefining.com wrote:
> Here is my sql query
>
> select distinct substring(poline.gldebitacct,1,6)as CostCenter ,
> glcomponents.comptext as gldesc ,po.ponum, po.description,companies.name
as
> VendorName,po.originalpovalue,po.
> lastpovalue,totalcost,po.status,po.changeby,po.receipts,postatus.changeby
> as approver,postatus.changedate from po
> LEFT OUTER JOIN companies ON po.vendor = companies.company
> LEFT OUTER JOIN poline ON po.ponum = poline.ponum
> LEFT OUTER JOIN postatus ON po.ponum = postatus.ponum
> LEFT OUTER JOIN glcomponents on substring(poline.gldebitacct,1,6) =
> glcomponents.compvalue
> where substring(poline.gldebitacct,1,6) = '603300'
> and DATEPART(yyyy, POSTATUS.CHANGEDATE) = 2012--:Year
> and po.status not in ('CAN')
> and po.receipts not in ('COMPLETE')
> order by po.status
>
> Here is and example of what all its returning. I just want the first line
> and not all the po lines.
>
> (Embedded image moved to file: pic00153.gif)
>
>
>
>
>
> Thank You,
>
> George Gutierrez Jr.
> Maximo Administration
> Pasadena Refining Systems
> 713.920.3908 Direct
> 832.473.5330 Cell
> U1O2 - Chave
>
>
>
> From: Chris Lawless <lawlessc@gmail.com>
> To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
> Date: 12/13/2012 05:53 PM
> Subject: Re: [MAXIMO List] SQL Query help
> Sent by: MAXIMO@yahoogroups.com
>
>
>
>
>
>
> Can you post your SQL and some details on what lines you want to exclude?
>
> On Dec 13, 2012, at 6:46 PM, "George" <ggutierrez@pasadenarefining.com>
> wrote:
>
> > Hey everyone I'm currently working on a PO report for our inspection
> department and I would like to know how to not have it return all po
lines.
> >
> > In my sql I also have a few joins
> > companies
> > poline
> > postatus
> > glcomponents
> >
> > Hope someone can shed some light on this.
> >
> > Maximo 6.2.1
> > Weblogic
> > SQL 2005
> >
> > thx,
> > mrggutz
> >
> >
>
>
>
>
>
>
>
>
>
I want to say POLINE.ROWNUM = 1 but I'm not sure what fields you're wanting to retrieve from POLINE.
On Dec 13, 2012, at 7:07 PM, ggutierrez@pasadenarefining.com wrote:
> Its the first one the database returns.
>
>
>
> Thank You,
>
> George Gutierrez Jr.
> Maximo Administration
> Pasadena Refining Systems
> 713.920.3908 Direct
> 832.473.5330 Cell
> U1O2 - Chave
>
>
>
> From: Chris Lawless <lawlessc@gmail.com>
> To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
> Date: 12/13/2012 06:04 PM
> Subject: Re: [MAXIMO List] SQL Query help
> Sent by: MAXIMO@yahoogroups.com
>
>
>
>
>
>
> How do you define the first line? Is it the one with the lowest line number
> or the first one the database happens to return?
>
> On Dec 13, 2012, at 6:59 PM, ggutierrez@pasadenarefining.com wrote:
>
> > Here is my sql query
> >
> > select distinct substring(poline.gldebitacct,1,6)as CostCenter ,
> > glcomponents.comptext as gldesc ,po.ponum, po.description,companies.name
> as
> > VendorName,po.originalpovalue,po.
> > lastpovalue,totalcost,po.status,po.changeby,po.receipts,postatus.changeby
>
> > as approver,postatus.changedate from po
> > LEFT OUTER JOIN companies ON po.vendor = companies.company
> > LEFT OUTER JOIN poline ON po.ponum = poline.ponum
> > LEFT OUTER JOIN postatus ON po.ponum = postatus.ponum
> > LEFT OUTER JOIN glcomponents on substring(poline.gldebitacct,1,6) =
> > glcomponents.compvalue
> > where substring(poline.gldebitacct,1,6) = '603300'
> > and DATEPART(yyyy, POSTATUS.CHANGEDATE) = 2012--:Year
> > and po.status not in ('CAN')
> > and po.receipts not in ('COMPLETE')
> > order by po.status
> >
> > Here is and example of what all its returning. I just want the first line
>
> > and not all the po lines.
> >
> > (Embedded image moved to file: pic00153.gif)
> >
> >
> >
> >
> >
> > Thank You,
> >
> > George Gutierrez Jr.
> > Maximo Administration
> > Pasadena Refining Systems
> > 713.920.3908 Direct
> > 832.473.5330 Cell
> > U1O2 - Chave
> >
> >
> >
> > From: Chris Lawless <lawlessc@gmail.com>
> > To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
> > Date: 12/13/2012 05:53 PM
> > Subject: Re: [MAXIMO List] SQL Query help
> > Sent by: MAXIMO@yahoogroups.com
> >
> >
> >
> >
> >
> >
> > Can you post your SQL and some details on what lines you want to exclude?
>
> >
> > On Dec 13, 2012, at 6:46 PM, "George" <ggutierrez@pasadenarefining.com>
> > wrote:
> >
> > > Hey everyone I'm currently working on a PO report for our inspection
> > department and I would like to know how to not have it return all po
> lines.
> > >
> > > In my sql I also have a few joins
> > > companies
> > > poline
> > > postatus
> > > glcomponents
> > >
> > > Hope someone can shed some light on this.
> > >
> > > Maximo 6.2.1
> > > Weblogic
> > > SQL 2005
> > >
> > > thx,
> > > mrggutz
> > >
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
>