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.
Suppose the following table structure :
Assetnum
Itemnum
Id
X
It1
1
X
It2
2
Y
It1
3
Y
It2
4
Z
It1
5
N
It2
6
M
It1
7
M
It2
8
M
It3
9
How to get only asset which share same exact itemnum , only return x and y
and also for any asset has exact same itemnum not using itemnum in
('it1','it2')
Thanks
Technical Specialist
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to which they are addressed.
If you have received this email in error please notify by return e-mail.
This message may contain confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail.
Sql server or oracle?
> On Sep 22, 2013, at 2:40 AM, "Fathi Deghadi" <fdeghadi@futech-eg.com> wrote:
>
> Suppose the following table structure :
>
> Assetnum
>
> Itemnum
>
> Id
>
> X
>
> It1
>
> 1
>
> X
>
> It2
>
> 2
>
> Y
>
> It1
>
> 3
>
> Y
>
> It2
>
> 4
>
> Z
>
> It1
>
> 5
>
> N
>
> It2
>
> 6
>
> M
>
> It1
>
> 7
>
> M
>
> It2
>
> 8
>
> M
>
> It3
>
> 9
>
>
>
>
>
> How to get only asset which share same exact itemnum , only return x and y and also for any asset has exact same itemnum not using itemnum in (’it1’,’it2’)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks
>
> Technical Specialist
>
>
>
>
>
>
>
>
>
> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify by return e-mail. This message may contain confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
>
>
>
>
>
>
>
>
>
>
This should get you at least a set of results where an itemnum is used for more than one asset… based on your question that seems to be what you are looking for… and it should work fine for any SQL db…
select * from <table> a
where exists (select itemnum from <table> b where a.itemnum = b.itemnum and a.assetnum != b.assetnum)
order by itemnum, assetnum
you can add a “and itemnum = xxxx” to limit this to a specific itemnum if desired…
R/
Will
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Yahoo
Sent: Sunday, September 22, 2013 7:47 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] Query help
Sql server or oracle?
On Sep 22, 2013, at 2:40 AM, "Fathi Deghadi" <fdeghadi@futech-eg.com<mailto:fdeghadi@futech-eg.com>> wrote:
Suppose the following table structure :
Assetnum
Itemnum
Id
X
It1
1
X
It2
2
Y
It1
3
Y
It2
4
Z
It1
5
N
It2
6
M
It1
7
M
It2
8
M
It3
9
How to get only asset which share same exact itemnum , only return x and y and also for any asset has exact same itemnum not using itemnum in (’it1’,’it2’)
Thanks
Technical Specialist
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify by return e-mail. This message may contain confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
We are using oracle DB , Maximo 6
Thanks ,
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of Yahoo
Sent: 22 سبتمبر, 2013 03:47 م
To: MAXIMO@yahoogroups.com
Subject: [Spam] Re: [MAXIMO List] Query help
Sql server or oracle?
On Sep 22, 2013, at 2:40 AM, "Fathi Deghadi" <fdeghadi@futech-eg.com> wrote:
Suppose the following table structure :
Assetnum
Itemnum
Id
X
It1
1
X
It2
2
Y
It1
3
Y
It2
4
Z
It1
5
N
It2
6
M
It1
7
M
It2
8
M
It3
9
How to get only asset which share same exact itemnum , only return x and y and also for any asset has exact same itemnum not using itemnum in (’it1’,’it2’)
Thanks
Technical Specialist
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify by return e-mail. This message may contain confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
I’m not exactly sure what you are looking for but the following will pick all assets that have a common shared itemnum
It assumes your not worried about siteid
select * from asset where exists ( select null from asset a where a.itemnum = asset.itemnum group by itemnum having COUNT(*) > 1)
Rgds Ian
BPD Zenith Ltd
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.
**************************************************************************************************************