Help with script.
-
Please forgive me if im in the wrong area. New To SQL.
I have 2 SQL statements but i need to run it as one...It keeps failing...Please help. What am i doing wrong.
1st SQL Query
SELECT ACCESSPANE.NAME AS IC_Panel_Name, ACCESSPANE.ADDRESS AS IC_Panel_Address, PANELTYPE.NAME AS IC_Panel_Type, READER.READERDESC AS Reader_Name, READER.CTRLTYPE,
READER.PORTNUM AS Port_Number, READER.COMMADDR AS Board_Address, READER.READER_NUMBER AS Reader_Port_Number, READER.STRIKEMODE AS Strike_Mode,
READER.STRIKETIME AS Strike_Time, READER.OPENTIME AS Held_Open_Time, READER.DOORCONTACT_SUPERVISION, READER.REX_SUPERVISION, READER.AUX1NAME AS Aux_Input_1,
READER.AUX2NAME AS Aux_Input_2, READER.AUX1_SUPERVISION AS Aux_1_Supervision, READER.AUX2_SUPERVISION AS Aux_2_Supervision, READER.OUT1NAME AS Output_1,
READER.OUT2NAME AS Output_2
FROM READER INNER JOIN
ACCESSPANE ON READER.PANELID = ACCESSPANE.PANELID INNER JOIN
PANELTYPE ON ACCESSPANE.PANELTYPE = PANELTYPE.PANELTYPEIDdoes this:
Picture 1.
2nd SQL Query
SELECT
dbo.ACCESSPANE.PRIMARYIP,
CAST(ROUND( (cast(dbo.ACCESSPANE.PRIMARYIP as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.ACCESSPANE.PRIMARYIP as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.ACCESSPANE.PRIMARYIP as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((cast(dbo.ACCESSPANE.PRIMARYIP as bigint) % 256 ) AS varchar(4)) as REAL_IP
FROM
dbo.ACCESSPANEDoes This.
how do i get them both to one SQL query. when I do the Second one it fails in SQL. The second SQL should in the last column and look like this as finished product.:
-
Hi,
What error you are getting if you try this?
SELECT
ACCESSPANE.NAME AS IC_Panel_Name,
ACCESSPANE.ADDRESS AS IC_Panel_Address,
PANELTYPE.NAME AS IC_Panel_Type,
READER.READERDESC AS Reader_Name,
READER.CTRLTYPE,
READER.PORTNUM AS Port_Number,
READER.COMMADDR AS Board_Address,
READER.READER_NUMBER AS Reader_Port_Number,
READER.STRIKEMODE AS Strike_Mode,
READER.STRIKETIME AS Strike_Time,
READER.OPENTIME AS Held_Open_Time,
READER.DOORCONTACT_SUPERVISION,
READER.REX_SUPERVISION,
READER.AUX1NAME AS Aux_Input_1,
READER.AUX2NAME AS Aux_Input_2,
READER.AUX1_SUPERVISION AS Aux_1_Supervision,
READER.AUX2_SUPERVISION AS Aux_2_Supervision,
READER.OUT1NAME AS Output_1,
READER.OUT2NAME AS Output_2,
ACCESSPANE.PRIMARYIP,
CAST(ROUND( (cast(dbo.ACCESSPANE.PRIMARYIP as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.ACCESSPANE.PRIMARYIP as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.ACCESSPANE.PRIMARYIP as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((cast(dbo.ACCESSPANE.PRIMARYIP as bigint) % 256 ) AS varchar(4)) as REAL_IP
FROM READER INNER JOIN
ACCESSPANE ON READER.PANELID = ACCESSPANE.PANELID INNER JOIN
PANELTYPE ON ACCESSPANE.PANELTYPE = PANELTYPE.PANELTYPEID -
-
Hi,
Does converting to unsigned int prior to converting to Bigint solve your problem as shown below?
SELECT
ACCESSPANE.NAME AS IC_Panel_Name,
ACCESSPANE.ADDRESS AS IC_Panel_Address,
PANELTYPE.NAME AS IC_Panel_Type,
READER.READERDESC AS Reader_Name,
READER.CTRLTYPE,
READER.PORTNUM AS Port_Number,
READER.COMMADDR AS Board_Address,
READER.READER_NUMBER AS Reader_Port_Number,
READER.STRIKEMODE AS Strike_Mode,
READER.STRIKETIME AS Strike_Time,
READER.OPENTIME AS Held_Open_Time,
READER.DOORCONTACT_SUPERVISION,
READER.REX_SUPERVISION,
READER.AUX1NAME AS Aux_Input_1,
READER.AUX2NAME AS Aux_Input_2,
READER.AUX1_SUPERVISION AS Aux_1_Supervision,
READER.AUX2_SUPERVISION AS Aux_2_Supervision,
READER.OUT1NAME AS Output_1,
READER.OUT2NAME AS Output_2,
ACCESSPANE.PRIMARYIP,
CAST(ROUND( (cast(cast(dbo.ACCESSPANE.PRIMARYIP as binary(4)) as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(cast(dbo.ACCESSPANE.PRIMARYIP as binary(4)) as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(cast(dbo.ACCESSPANE.PRIMARYIP as binary(4)) as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((cast(cast(dbo.ACCESSPANE.PRIMARYIP as binary(4)) as bigint) % 256 ) AS varchar(4)) as REAL_IP
FROM READER INNER JOIN
ACCESSPANE ON READER.PANELID = ACCESSPANE.PANELID INNER JOIN
PANELTYPE ON ACCESSPANE.PANELTYPE = PANELTYPE.PANELTYPEID -
Also, is the IP stored as below?
"192.168.13.12" -> "192168013012"If that is the case, we can make use of SUBSTRING function to extract that.
-
no...
The Primary Field Shows...
177460285 this query:
ACCESSPANE.PRIMARYIP,
CAST(ROUND( (cast(cast(dbo.ACCESSPANE.PRIMARYIP as binary(4)) as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(cast(dbo.ACCESSPANE.PRIMARYIP as binary(4)) as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(cast(dbo.ACCESSPANE.PRIMARYIP as binary(4)) as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((cast(cast(dbo.ACCESSPANE.PRIMARYIP as binary(4)) as bigint) % 256 ) AS varchar(4)) as REAL_IPMakes it
10.147.212.61 which is correct.
But...for some reason...
-1871722730 is becomming
-111.-144.-56.-234 when its really...
144.111.199.22
-
no ...it still errors out in View..
- justin.lamantia - Sunday, November 11, 2018 12:38 PM
I think this is to do with int and bigint being stored as 2's complement signed integers in SQL Server.
What data type is dbo.ACCESSPANE.PRIMARYIP stored as in your SQL Server?
144.111.199.22 would be stored as a positive integer (2423244566) if held in a bigint (8 bytes), if held in an int (32-bits) it would be stored as a negative number.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply