Changeset 1234

Timestamp:

12/18/06 13:01:42 (6 years ago)

Author:

mglb1

Message:

Fix bugs in the available host asset view

• Don't include NULL asset_id's in the subselect results or none of the available assets at the site will be matched
• Restrict the returned results to host assets ONLY, otherwise anything in site_assets will be returned, including wireless cards, etc.

File:

• ccsd/trunk/dbschema/ccs.schema (modified) (1 diff)

ccsd/trunk/dbschema/ccs.schema

@@ -1045 +1045 @@
 
 -- List of assets available for use as a new host at a site
-CREATE VIEW site_host_assets_avail AS SELECT * FROM site_assets sa WHERE
+CREATE VIEW site_host_assets_avail AS SELECT sa.* FROM site_assets sa, asset a,
+    asset_type_map atm WHERE sa.asset_id=a.asset_id AND 
+    a.asset_type_id=atm.asset_type_id AND atm.asset_function='host' AND
     sa.asset_id NOT IN (SELECT asset_id FROM site_hosts WHERE 
-    site_id=sa.site_id);
+    asset_id IS NOT NULL AND site_id=sa.site_id);
 
 -- List of interface subassets available for use on a host