Hello
In this project I'm working on, I have to show related data from a few tables. Simple enough, except those tables aren't relational, they're just separate tables.
The tables are like this.
Code:
private Long ciscoworks_id;
private String ciscoworks_mac;
private String ciscoworks_switch;
private String ciscoworks_port;
private String ciscoworks_vlan;
private Date ciscoworks_lastseen;
private Long dhcp_id;
private String dhcp_ip;
private String dhcp_mac;
private String dhcp_hostname;
private Long patch_unit;
private String patch_unit;
private String patch_office;
private String patch_location;
private String patch_socket;
private String patch_cable;
private String patch_port;
private String patch_switch;
Up until now, linking ciscoworks and dhcp wasn't too hard, but I got stuck at the patch.
Code:
<class name="Table" table="CISCOWORKS">
<id name="ciscoworks_id"
column="CISCOWORKS_ID"
type="long">
<generator class="increment"/>
</id>
<property
name="ciscoworks_mac"
column="CISCOWORKS_MAC"
type="string"
not-null="false"/>
<property
name="ciscoworks_switch"
column="CISCOWORKS_SWITCH"
type="string"
not-null="false"/>
<property
name="ciscoworks_port"
column="CISCOWORKS_PORT"
type="string"
not-null="false"/>
<property
name="ciscoworks_vlan"
column="CISCOWORKS_VLAN"
type="string"
not-null="false"/>
<property
name="ciscoworks_lastseen"
column="CISCOWORKS_LASTSEEN"
type="date"
not-null="false"/>
<property
name="dhcp_ip"
formula="(SELECT d.DHCP_IP
FROM DHCP d
WHERE d.DHCP_MAC = CISCOWORKS_MAC)"
type="string"
not-null="false"/>
<property
name="dhcp_hostname"
formula="(SELECT d.DHCP_HOSTNAME
FROM DHCP d
WHERE d.DHCP_MAC = CISCOWORKS_MAC)"
type="string"
not-null="false"/>
<property
name="patch_office"
formula="(
SELECT p.PATCH_OFFICE
FROM PATCH p
WHERE p.PATCH_SWITCH = UPPER(SUBSTR(CISCOWORKS_SWITCH,6))
AND p.PATCH_PORT = SUBSTR(CISCOWORKS_PORT,3,1) || '-' || SUBSTR(CISCOWORKS_PORT,5)
)"
type="string"
not-null="false"/>
</class>
I'm sure you can tell, this code is ugly and leaves no freedom, and it's only just beginning. Oh, and it gives an error on the last query as well, but at least it gives an idea what it looks.
Both ciscoworks and dhcp contain a mac address, and I link them on that. Ciscoworks and patch share a combination of switch and port, but they are in different formats (for example, ciscoworks has 'netswf1' with 'Gi2/28', patch has 'f1' with '2-28', hence the substrings and all), and there might be multiple entries in patch for a single 'unique' combination of switch and port.
Now, I am wondering if there is a way to do a join on the tables, then just read the information from that join. Makes it easier to filter results as well.
Is this possible, and how would I start on it?
Thanks in advance.
Login
FAQ
Search