DB View Visualization in Service Center 1

[kwinter]

Hi all,

I try to configure a visualization of an Oracle Database View within Service Center. The View contains information about harddisks (and certainly one host has several harddisks) The structure is pretty easy:
1.hostname/diskinfo1A/diskinfo1B/diskinfo1C
1.hostname/diskinfo2A/diskinfo2B/diskinfo2C
2.hostname/diskinfo1A/diskinfo1B/diskinfo1C

etc

I got a visualization but I can only see the information about one harddisk per hostname (the first entry within the view). No difference wheather the fields (to display the information) are arrays or normal character fields. Does anybody know how to manage such things?
Any reply is very welcome!!! Greetings Kai

 

[chrishaw]

How is the hard disk data structured? Is it in an array inside the record about the host or is it a bunch of separate records with a field relating to the main host?

When you pushed to SQL, what did you do about array disposition?

 

[kwinter]

The hard disk data is actually a bunch of seperate records. I tried all options concerning array mapping. Unfortunately in oracle there are only tables allowed with one long text field only, so SC is creating a number of tables when pushing to SQL...

 

[chrishaw]

Okay, so your database is laid out like this:

(using oob tablenames, yours should be similar)

devicem1 - main table, has non-arrayed info about the host device. Unique key is the device ID

devicea1 - hard disc data, one record per disc. Unique key is the device ID plus a record number (ie 1, 2, 3).

Is that right?

Next, how are you joining these two tables together?

 

[kwinter]

Yes, that's exactly my point! I don't know how to join these tables togesther!!!
I hoped, direkting all tables (devicem1, devicea1...)to one certain view in the database (e.g. within the SQL mapping I changed all these tablenames devicem1, devicea1 etc to my single view name HDISK_VIEW) would solve it, but that's probably a bit naive... anyway, it didn't work - I only see one entry (the first)

 

[chrishaw]

Look at the design of the view within Oracle. It should have something like:

SELECT (list of fields)
FROM devicem1 (join type) devicea1 ON devicem1.logical_name=devicea1.logical_name

The (join type) is the important thing. It sounds like you have an EQUAL when you really want an INNER.

What is the join syntax in your view?

 

[kwinter]

Your're right, the jointype is an EQUAL indeed!

 

[chrishaw]

Okay, that would be the problem. Here's a quick description of what you might want:

INNER - all records where there are matches in both tables (ie there is a device and it has hard drives)

LEFT OUTER - all records in device and their corresponding hard drives (ie will get everything in device, but if there are hard drives that don't match a device, they won't be shown)

RIGHT OUTER - all records in the hard drive table and their corresponding devices (ie will get everything in hard drive, but if there is a device without a hard drive, it won't be shown)

FULL OUTER - all records in both tables

From your description, it sounds like you want either an INNER or a LEFT OUTER

 

[kwinter]

Hi thanks, that's it! I need the LEFT OUTER!!! Thanks a lot for your help! And have a nice weekend!
Kai