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.
still on v4.1.1
need help with this query, its to show cm 'inprg' workorders that were last charged to 45+ days ago. its returns what i want except i want one entry per wonum (the most current or last date charged).i'm getting multiple entries per wonum because (i think) each entry has a different time stamp. It's probably a simple fix but because sql is not my second language i'm stuck at this point. TIA, dallas
select w.wonum, w.description, w.crewid, l.enterdate from workorder w, labtrans l where w.wonum = l.wonum and w.status = 'inprg'
and w.wopm1 is null and w.worktype = 'cm' and l.wonum in (select l.wonum from labtrans l where l.enterdate <= getdate()-45) and w.wonum not in (select w.wonum from workorder w where w.wonum like '%s' or w.wonum like '%J' or w.wo10 like 'swo%');
Hi Dallas! Long time since I've heard from you!
You need to group your SQL, i.e.:
select w.wonum, w.description, w.crewid, max(l.enterdate )
from workorder w, labtrans l
where w.wonum = l.wonum and w.status = 'inprg'
and w.wopm1 is null and w.worktype = 'cm'
and l.wonum in (select l.wonum from labtrans l where l.enterdate <=
getdate()-45)
and w.wonum not in (select w.wonum from workorder w where w.wonum like '%s'
or w.wonum like '%J' or w.wo10 like 'swo%');
group by w.wonum, w.description, w.crewid
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
Big D
Sent: November-07-12 8:14 PM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] need help with an sql query
still on v4.1.1
need help with this query, its to show cm 'inprg' workorders that were last
charged to 45+ days ago. its returns what i want except i want one entry per
wonum (the most current or last date charged).i'm getting multiple entries
per wonum because (i think) each entry has a different time stamp. It's
probably a simple fix but because sql is not my second language i'm stuck at
this point. TIA, dallas
select w.wonum, w.description, w.crewid, l.enterdate from workorder w,
labtrans l where w.wonum = l.wonum and w.status = 'inprg'
and w.wopm1 is null and w.worktype = 'cm' and l.wonum in (select l.wonum
from labtrans l where l.enterdate <= getdate()-45) and w.wonum not in
(select w.wonum from workorder w where w.wonum like '%s' or w.wonum like
'%J' or w.wo10 like 'swo%');
I guess you need to make a couple of simple changes to achieve what and I think that should improve the performance as well. I am assuming you are using Oracle. If not, you may have to find an alternative:
select w.wonum, w.description, w.crewid, l.enterdate from workorder w, labtrans l where w.wonum = l.wonum and w.status = 'inprg'
and w.wopm1 is null and w.worktype = 'cm' and( l.wonum, l.enterdate) = (select inner.wonum, inner.enterdate from labtrans inner where (inner.enterdate <= getdate()-45) and inner.wonum = w.wonum and rownum = 1) and w.wonum not in (select w.wonum from workorder w where w.wonum like '%s' or w.wonum like '%J' or w.wo10 like 'swo%');
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Big D
Sent: Thursday, November 08, 2012 9:44 AM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] need help with an sql query
still on v4.1.1
need help with this query, its to show cm 'inprg' workorders that were last charged to 45+ days ago. its returns what i want except i want one entry per wonum (the most current or last date charged).i'm getting multiple entries per wonum because (i think) each entry has a different time stamp. It's probably a simple fix but because sql is not my second language i'm stuck at this point. TIA, dallas
select w.wonum, w.description, w.crewid, l.enterdate from workorder w, labtrans l where w.wonum = l.wonum and w.status = 'inprg'
and w.wopm1 is null and w.worktype = 'cm' and l.wonum in (select l.wonum from labtrans l where l.enterdate <= getdate()-45) and w.wonum not in (select w.wonum from workorder w where w.wonum like '%s' or w.wonum like '%J' or w.wo10 like 'swo%');
This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
Thanks for the help. We are on SQL server not Oracle. Will try to work thru it when i get to work. Dallas
--- In MAXIMO@yahoogroups.com, "Hongal Narayana, Phanish" <phanish.hn@...> wrote:
>
> I guess you need to make a couple of simple changes to achieve what and I think that should improve the performance as well. I am assuming you are using Oracle. If not, you may have to find an alternative:
>
> select w.wonum, w.description, w.crewid, l.enterdate from workorder w, labtrans l where w.wonum = l.wonum and w.status = 'inprg'
> and w.wopm1 is null and w.worktype = 'cm' and( l.wonum, l.enterdate) = (select inner.wonum, inner.enterdate from labtrans inner where (inner.enterdate <= getdate()-45) and inner.wonum = w.wonum and rownum = 1) and w.wonum not in (select w.wonum from workorder w where w.wonum like '%s' or w.wonum like '%J' or w.wo10 like 'swo%');
>
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Big D
> Sent: Thursday, November 08, 2012 9:44 AM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] need help with an sql query
>
>
>
> still on v4.1.1
>
> need help with this query, its to show cm 'inprg' workorders that were last charged to 45+ days ago. its returns what i want except i want one entry per wonum (the most current or last date charged).i'm getting multiple entries per wonum because (i think) each entry has a different time stamp. It's probably a simple fix but because sql is not my second language i'm stuck at this point. TIA, dallas
>
> select w.wonum, w.description, w.crewid, l.enterdate from workorder w, labtrans l where w.wonum = l.wonum and w.status = 'inprg'
> and w.wopm1 is null and w.worktype = 'cm' and l.wonum in (select l.wonum from labtrans l where l.enterdate <= getdate()-45) and w.wonum not in (select w.wonum from workorder w where w.wonum like '%s' or w.wonum like '%J' or w.wo10 like 'swo%');
>
> This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
>
>
>
>
Thanks Shannon, I'll try this when i get to work. Dallas
--- In MAXIMO@yahoogroups.com, "Incomm Solutions Inc." <shannonrotz@...> wrote:
>
> Hi Dallas! Long time since I've heard from you!
>
>
>
> You need to group your SQL, i.e.:
>
>
>
> select w.wonum, w.description, w.crewid, max(l.enterdate )
>
> from workorder w, labtrans l
>
> where w.wonum = l.wonum and w.status = 'inprg'
> and w.wopm1 is null and w.worktype = 'cm'
>
> and l.wonum in (select l.wonum from labtrans l where l.enterdate <=
> getdate()-45)
>
> and w.wonum not in (select w.wonum from workorder w where w.wonum like '%s'
> or w.wonum like '%J' or w.wo10 like 'swo%');
>
> group by w.wonum, w.description, w.crewid
>
>
>
>
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> Big D
> Sent: November-07-12 8:14 PM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] need help with an sql query
>
>
>
>
>
> still on v4.1.1
>
> need help with this query, its to show cm 'inprg' workorders that were last
> charged to 45+ days ago. its returns what i want except i want one entry per
> wonum (the most current or last date charged).i'm getting multiple entries
> per wonum because (i think) each entry has a different time stamp. It's
> probably a simple fix but because sql is not my second language i'm stuck at
> this point. TIA, dallas
>
> select w.wonum, w.description, w.crewid, l.enterdate from workorder w,
> labtrans l where w.wonum = l.wonum and w.status = 'inprg'
> and w.wopm1 is null and w.worktype = 'cm' and l.wonum in (select l.wonum
> from labtrans l where l.enterdate <= getdate()-45) and w.wonum not in
> (select w.wonum from workorder w where w.wonum like '%s' or w.wonum like
> '%J' or w.wo10 like 'swo%');
>
>
>
>
>
>
>
Thanks Shannon, worked just way i wanted. Why does every workorder field i want showing have to be in the 'group on' script? The error message said something about because they weren't in a select statement. I don't understand.
Thanks again.
--- In MAXIMO@yahoogroups.com, "Incomm Solutions Inc." <shannonrotz@...> wrote:
>
> Hi Dallas! Long time since I've heard from you!
>
>
>
> You need to group your SQL, i.e.:
>
>
>
> select w.wonum, w.description, w.crewid, max(l.enterdate )
>
> from workorder w, labtrans l
>
> where w.wonum = l.wonum and w.status = 'inprg'
> and w.wopm1 is null and w.worktype = 'cm'
>
> and l.wonum in (select l.wonum from labtrans l where l.enterdate <=
> getdate()-45)
>
> and w.wonum not in (select w.wonum from workorder w where w.wonum like '%s'
> or w.wonum like '%J' or w.wo10 like 'swo%');
>
> group by w.wonum, w.description, w.crewid
>
>
>
>
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> Big D
> Sent: November-07-12 8:14 PM
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] need help with an sql query
>
>
>
>
>
> still on v4.1.1
>
> need help with this query, its to show cm 'inprg' workorders that were last
> charged to 45+ days ago. its returns what i want except i want one entry per
> wonum (the most current or last date charged).i'm getting multiple entries
> per wonum because (i think) each entry has a different time stamp. It's
> probably a simple fix but because sql is not my second language i'm stuck at
> this point. TIA, dallas
>
> select w.wonum, w.description, w.crewid, l.enterdate from workorder w,
> labtrans l where w.wonum = l.wonum and w.status = 'inprg'
> and w.wopm1 is null and w.worktype = 'cm' and l.wonum in (select l.wonum
> from labtrans l where l.enterdate <= getdate()-45) and w.wonum not in
> (select w.wonum from workorder w where w.wonum like '%s' or w.wonum like
> '%J' or w.wo10 like 'swo%');
>
>
>
>
>
>
>
The minute you put in a sum() or a max() or a count(*) or something, you
need to do an aggregate, which means a "group by" statement.
It's essentially telling the database WHAT to total . or count, or
whatever.
Shannon
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
Big D
Sent: November-08-12 9:23 AM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: need help with an sql query
Thanks Shannon, worked just way i wanted. Why does every workorder field i
want showing have to be in the 'group on' script? The error message said
something about because they weren't in a select statement. I don't
understand.
Thanks again.
--- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Incomm
Solutions Inc." <shannonrotz@...> wrote:
>
> Hi Dallas! Long time since I've heard from you!
>
>
>
> You need to group your SQL, i.e.:
>
>
>
> select w.wonum, w.description, w.crewid, max(l.enterdate )
>
> from workorder w, labtrans l
>
> where w.wonum = l.wonum and w.status = 'inprg'
> and w.wopm1 is null and w.worktype = 'cm'
>
> and l.wonum in (select l.wonum from labtrans l where l.enterdate <=
> getdate()-45)
>
> and w.wonum not in (select w.wonum from workorder w where w.wonum like
'%s'
> or w.wonum like '%J' or w.wo10 like 'swo%');
>
> group by w.wonum, w.description, w.crewid
>
>
>
>
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of
> Big D
> Sent: November-07-12 8:14 PM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> Subject: [MAXIMO List] need help with an sql query
>
>
>
>
>
> still on v4.1.1
>
> need help with this query, its to show cm 'inprg' workorders that were
last
> charged to 45+ days ago. its returns what i want except i want one entry
per
> wonum (the most current or last date charged).i'm getting multiple entries
> per wonum because (i think) each entry has a different time stamp. It's
> probably a simple fix but because sql is not my second language i'm stuck
at
> this point. TIA, dallas
>
> select w.wonum, w.description, w.crewid, l.enterdate from workorder w,
> labtrans l where w.wonum = l.wonum and w.status = 'inprg'
> and w.wopm1 is null and w.worktype = 'cm' and l.wonum in (select l.wonum
> from labtrans l where l.enterdate <= getdate()-45) and w.wonum not in
> (select w.wonum from workorder w where w.wonum like '%s' or w.wonum like
> '%J' or w.wo10 like 'swo%');
>
>
>
>
>
>
>