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 2 7113
Hi
You don't post the ddl for the tables used but at a guess either
pv.IP_ADDRESS or i.INVENTORY_ITEM_ID is an it.
John
"Anthony" <an**************@wamu.net> wrote in message
news:fb**************************@posting.google.c om... 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 sa***************@wamu.net (Anthony) wrote in message news:<fb**************************@posting.google. com>... 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.
I believe you will find the datatype of one of the following to be an
integer, not a string.
dbo.INVENTORY_ITEM.INVENTORY_ITEM_ID
or
dbo.IP_TO_IVNETORY.IP_ADDRESS
FROM dbo.INVENTORY_ITEM i
LEFT OUTER JOIN dbo.IP_TO_INVENTORY pv
ON pv.IP_ADDRESS = i.INVENTORY_ITEM_ID This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Toonman |
last post by:
I'm trying to use a couple of variables in a stored procedure. Things work
fine when I hard code the data into the variables and also work fine when I
use the variable in the WHERE clause and hard...
|
by: Anthony |
last post by:
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...
|
by: Viktor Popov |
last post by:
Hi,
I'm trying to execute stored procedure and I collect its parameters from
WebForm. There is an error: Error converting data type varchar to int.
What could be the problem? Here it is the...
|
by: luna |
last post by:
got so far then it broke and i cant get it working again - it was updating
fine but not inserting and now im getting a "Error converting data type
varchar to numeric" which i didnt have before.......
|
by: Dan |
last post by:
Hi. I'm having a problem with searching a databse entry. I'm new to dotnet
and still trying to figure some things out.
In order to locate a row in an access databse file .mdb, i am using this...
|
by: ronca |
last post by:
Error Syntax error converting the varchar value $568.25 to a column of data type
Statement I'm using
Select Top 1000 *
From mass.dbo.dbo_MASS
where _amount > 5000
Want only row with _amount...
|
by: othellomy |
last post by:
select convert(float,'1.2334e+006')
1233400.0
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
can I set...
|
by: Krishna Ladwa |
last post by:
In Sql Server 2000 Version, I found that no Notification message box appears when converting text column to varchar but the data gets truncated to the given size for the varchar. Whereas it appears...
|
by: Rahul Babbar |
last post by:
Hi,
I am getting the following error, while executing the simple insert
script on a few tables.
INSERT INTO <table_name>(<col1>) VALUES (1);
DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884,...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |