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 using the poline to get my gldebit account is all.
The sender of this message is responsible for its content and addressing. Any actions not conforming to Petrobras System internal policies are forbidden.
----- Original Message -----
From: Chris Lawless [lawlessc@gmail.com]
Sent: 12/13/2012 07:20 PM EST
To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
Subject: Re: [MAXIMO List] SQL Query help
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
> > >
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
>
Would this work for you (this is for SQL Server):
select top(1) substring(poline.gldebitacct,1,6)as CostCenter ,
glcomponents.comptext as gldesc ,po.ponum, po.description,companies.name as
VendorName,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
I just removed the word DISTINCT and added the TOP operator.
Travis Herron
--- In MAXIMO@yahoogroups.com, ggutierrez@... wrote:
>
> I'm using the poline to get my gldebit account is all.
>
> The sender of this message is responsible for its content and addressing. Any actions not conforming to Petrobras System internal policies are forbidden.
>
>
> ----- Original Message -----
> From: Chris Lawless [lawlessc@...]
> Sent: 12/13/2012 07:20 PM EST
> To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
> Subject: Re: [MAXIMO List] SQL Query help
>
>
>
> 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@... 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@...>
> > 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@... 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@...>
> > > 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@...>
> > > 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
> > > >
> > > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
>
>
>