I have an inventory database that Im trying to create a report out of
the IP address are a lookup on a seperat table but I keep getting the
above error can I change the table row to something to fix this or
what.
SELECT i.INVENTORY_ITEM_ID AS [IP Address],i.HOST_NAME AS [Server
Name], '' AS Flag, i.MEMO AS Comments, 'Seattle' AS City, 'Washington'
AS State,
CASE WHEN fv.value = 'EL EET1410' THEN '1111 3rd
Ave.' WHEN fv.value = 'EL WFL17' THEN '999 3rd Ave.' ELSE '' END AS
[Address 1],
' ' AS [Address 2], CASE WHEN fv.value = 'EL
EET1410' THEN '1111' WHEN fv.value = 'EL WFL17' THEN '2222' ELSE ''
END AS [Building ID],
fv.VALUE AS Building, '98101' AS Zip,
CASE WHEN fv.value = 'EL EET1410' THEN '14' WHEN
fv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Computing Facility
Level],
CASE WHEN fv.value = 'EL EET1410' THEN '14' WHEN
fv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Bldg Floor],
CASE WHEN fv.value = 'EL EET1410' THEN 'C028'
WHEN fv.value = 'EL WFL17' THEN 'C123' ELSE '' END AS WSPID,
i.LOCATION_IN_FACILITY AS Location,
i.SERIAL_NUMBER AS [Serial Number], i.ASSET_TAG AS [Asset Tag], ' ' AS
[Second Asset Tag(s)],
vv.VALUE AS Manufacture, mv.VALUE AS Model,
'Yes' AS [Rack Mountable], rv.VALUE AS [Rack Units], 'DEV' AS [Server
Enviroment],
lv.VALUE AS [Sever Type], ov.VALUE AS OS,
CASE WHEN lv.value = 'Server - Intel Blade' THEN
'Windows' WHEN lv.value = 'Server - Intel' THEN 'Windows' WHEN
lv.value = 'Server - Unix' THEN 'Unix'
ELSE '' END AS [OS Type], '19x28' AS Footprint,
ev.VALUE AS [Technical Owner of Server], 'N/A' AS [SLA Category],
i.ON_BOARD_NIC_PORT_COUNT AS [NW Connection
quantity], 'Devlopment/Test Machine' AS [Application(s) Name],
ev.VALUE AS [PW Contact],
'N/A' AS RTO, 'N/A' AS RPO, 'No' AS [Is Box
Clustered?], 'N/A' AS [Buisness Critical],
CASE WHEN mv.value = 'Proliant DL580G1' THEN
'90lbs' WHEN mv.value = 'Proliant DL360G2' THEN '85lbs' WHEN mv.value
= 'Enterprise 220R' THEN
'45lbs' ELSE '' END AS Weight,
i.LAST_MODIFIED_DATE AS [Date of Last Install], 'No' AS [Dedicated
Circuit]
FROM dbo.INVENTORY_ITEM i LEFT OUTER JOIN
dbo.IP_TO_INVENTORY pv ON pv.IP_ADDRESS = i.INVENTORY_ITEM_ID
LEFT OUTER JOIN
dbo.LOOKUP_VALUE lv ON lv.LOOKUP_VALUE_ID =
i.DEVICE_TYPE_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE fv ON fv.LOOKUP_VALUE_ID =
i.FACILITY_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE vv ON vv.LOOKUP_VALUE_ID =
i.VENDOR_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE mv ON mv.LOOKUP_VALUE_ID =
i.MODEL_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE ov ON ov.LOOKUP_VALUE_ID =
i.SOFTWARE_VERSION_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE ev ON ev.LOOKUP_VALUE_ID =
i.CHECKED_OUT_BY_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE rv ON rv.LOOKUP_VALUE_ID =
i.U_HEIGHT_ID
