Help with DECODE function and crosstab query

[Mark Hoffman]

I'm a newbie at Oracle..Be gentle!

I have a table that stores information (WMI data) about computers on
our network. The table looks like:

ComputerID
ItemID
Class
Property
Value

Each row in the table contains WMI data for a specific WMI class and
property and for a specific computer. A sample of data that shows a
software package installed on a computer might look like:

ComputerID ItemID Class Property Value
---------------------------------------------------------------------------
1 1 Win32_Product Name Visio
1 1 Win32_Product Vendor Microsoft
1 1 Win32_Product InstallDate 100103
1 2 Win32_Product Name Symnatec
1 2 Win32_Product Vendor Norton
Antivirus
1 2 Win32_Product InstallDate 092503

In this case, ItemID is used to "group" the data together, since a
single software package will contain multiple rows of data. (Three in
this case.)

What I need to be able to do is write a query that will do something
like show the software installed for a particular computer, and
display something like:

Software Vendor Installed
---------------------------------------------------------------------
Visio Microsoft 100103
Norton Antivirus Symnatec 092503

I've read up on the DECODE function but I've not been able to get my
queries to work and I'm really not sure I'm even going in the right
direction? Can anyone shed some light on what this query would look
like?

Remember, showing installed software is just an example of what one
query would look. I understand that if this is all I wanted out of
WMI, then I would just build a normalized table. If we were to build a
database that resembled everything WMI could hold, it would be a huge.
This is a small application, but we want the flexibility of allowing
the users to request any piece of WMI data without having to change
the database schema. Potentially, it might be storing CPU information,
hotfix info, TCP/IP stack info, etc. That's why the de-normalized
table structure was chosen. Is there a better way to accomplish what
we need?

Thanks in advance for any pointers or tips!

[Siva Valiveru]

/* create table gtbl(ComputerID number,ItemID number,Property
varchar2(20),value varchar2(20));

insert into gtbl values(1,1,'Name','Visio');
insert into gtbl values(1,1,'Vendor','Microsoft');
insert into gtbl values(1,1,'InstallDate','100103');
insert into gtbl values(1,2,'Name','Symnatec');
insert into gtbl values(1,2,'Vendor','Norton');
insert into gtbl values(1,2,'InstallDate','092503');

*/

SQL Query:

1 select
2 max( decode( property, 'Name', value, null ) ) Software,
3 max( decode( property, 'Vendor', value, null ) ) Vendor,
4 max( decode( property, 'InstallDate', value, null ) ) Installed
5 from gtbl
6 where computerid = &cid
7* group by itemid
SQL> /
Enter value for cid: 1
old 6: where computerid = &cid
new 6: where computerid = 1

SOFTWARE VENDOR INSTALLED
-------------------- -------------------- --------------------
Visio Microsoft 100103
Symnatec Norton 092503

Regards,
Siva Valiveru

[Jan]

didn`t test it, but you are looking for something like this:

SELECT computer_id,
item_id,
MAX(Name) Name,
MAX(Vendor) Vendor,
Max(Installed) Installed
FROM (
SELECT computer_id,
item_id,
DECODE(Property,'Name',Value) Name,
DECODE(Property,'Vendor',Value) Vendor,
DECODE(Property,'InstallDate',Value) Installed
FROM your_table)
GROUP BY computer_id, item_id

po********@hotmail.com (Mark Hoffman) wrote in message news:<a0**************************@posting.google. com>...

I'm a newbie at Oracle..Be gentle!

I have a table that stores information (WMI data) about computers on
our network. The table looks like:

ComputerID
ItemID
Class
Property
Value

Each row in the table contains WMI data for a specific WMI class and
property and for a specific computer. A sample of data that shows a
software package installed on a computer might look like:

ComputerID ItemID Class Property Value
---------------------------------------------------------------------------
1 1 Win32_Product Name Visio
1 1 Win32_Product Vendor Microsoft
1 1 Win32_Product InstallDate 100103
1 2 Win32_Product Name Symnatec
1 2 Win32_Product Vendor Norton
Antivirus
1 2 Win32_Product InstallDate 092503

In this case, ItemID is used to "group" the data together, since a
single software package will contain multiple rows of data. (Three in
this case.)

What I need to be able to do is write a query that will do something
like show the software installed for a particular computer, and
display something like:

Software Vendor Installed
---------------------------------------------------------------------
Visio Microsoft 100103
Norton Antivirus Symnatec 092503

I've read up on the DECODE function but I've not been able to get my
queries to work and I'm really not sure I'm even going in the right
direction? Can anyone shed some light on what this query would look
like?

Remember, showing installed software is just an example of what one
query would look. I understand that if this is all I wanted out of
WMI, then I would just build a normalized table. If we were to build a
database that resembled everything WMI could hold, it would be a huge.
This is a small application, but we want the flexibility of allowing
the users to request any piece of WMI data without having to change
the database schema. Potentially, it might be storing CPU information,
hotfix info, TCP/IP stack info, etc. That's why the de-normalized
table structure was chosen. Is there a better way to accomplish what
we need?

Thanks in advance for any pointers or tips!