 traspaso de consulta de sql a mysql
ola...
soy nueva en esto de mysql me pasaron una consulta realizada en SQL y la debo traspasar a MYSQL pero tengo muchas dudad xq ahi cosas q mysql no reconoce ojala me puedan ayudar
les dejo la consulta originla realizada en SQL..
1. tengo q hacer un tabla temporal
2. mysql no soporta el INTO PASO_INVENTARIO
3. tengo problemas con (SELECT MAX(CAST(ADSL_Node_Port_Conn_Start_Dt as DATETIME))
ojala me puedan ayudar...
esto es URGENTE!!!!
gracias....
select B.*
INTO PASO_INVENTARIO
from
(SELECT MAX(CAST(ADSL_Node_Port_Conn_Start_Dt as DATETIME)) ADSL_Node_Port_Conn_Start_Dt,
ADSL_Node_Equip_Instance_Id,
ADSL_Node_Port_Conn_End_Dt,
Room_Code_Val,
Row_Code_Val,
Bastidor_Code_Val,
Node_Code_Val,
Rack_Code_Val ,
Chassis_Code_Val,
Card_Code_Val,
Lock_Code_Val,
MDF_V_Pos_Code_Val,
MDF_H_Pos_Code_Val,
MDF_Pos_Code_Val,
CTC_Ref_Code_Val,
VCI_Vlan_Usr_Code_Val,
Technology_Type_Code_Val
FROM `adsl_node_port_connection`
WHERE ADSL_Node_Port_Conn_End_Dt = '9999-12-31 00:00:00' AND NAS_Port_Val is not null and NAS_IP_Address_Val is not null
GROUP BY ADSL_Node_Equip_Instance_Id
,ADSL_Node_Port_Conn_End_Dt ,Room_Code_Val,Row_Code_Val,Bastidor_Code_Val,Node _Code_Val
,Rack_Code_Val,Chassis_Code_Val,Card_Code_Val,Lock _Code_Val,MDF_V_Pos_Code_Val,MDF_H_Pos_Code_Val
,MDF_Pos_Code_Val,CTC_Ref_Code_Val,VCI_Vlan_Usr_Co de_Val,Technology_Type_Code_Val )A
LEFT JOIN `adsl_node_port_connection` B ON
A.ADSL_Node_Port_Conn_Start_Dt = B.ADSL_Node_Port_Conn_Start_Dt
AND A.ADSL_Node_Equip_Instance_Id = B.ADSL_Node_Equip_Instance_Id
AND A.ADSL_Node_Port_Conn_End_Dt = B.ADSL_Node_Port_Conn_End_Dt
AND A.Room_Code_Val = B.Room_Code_Val
AND A.Row_Code_Val = B.Row_Code_Val
AND A.Bastidor_Code_Val = B.Bastidor_Code_Val
AND A.Node_Code_Val = B.Node_Code_Val
AND A.Rack_Code_Val = B.Rack_Code_Val
AND A.Chassis_Code_Val = B.Chassis_Code_Val
AND A.Card_Code_Val = B.Card_Code_Val
AND A.Lock_Code_Val = B.Lock_Code_Val
AND A.MDF_V_Pos_Code_Val = B.MDF_V_Pos_Code_Val
AND A.MDF_H_Pos_Code_Val = B.MDF_H_Pos_Code_Val
AND A.MDF_Pos_Code_Val = B.MDF_Pos_Code_Val
AND A.CTC_Ref_Code_Val = B.CTC_Ref_Code_Val
AND A.VCI_Vlan_Usr_Code_Val = B.VCI_Vlan_Usr_Code_Val
AND A.Technology_Type_Code_Val = B.Technology_Type_Code_Val
------------------------------------------------------------------------------------------------------------
CREATE TEMPORARY TABLE PASO_INVENTARIO2 (
ADSL_Node_Equip_Instance_Id int ,
ADSL_Node_Port_Seq int ,
ADSL_Node_Port_Conn_Start_Dt DATETIME,
ADSL_Node_Port_Conn_End_Dt DATETIME,
Access_Method_Id varchar (255) ,
Emplacement_Agency_Id varchar (255) ,
Room_Code_Val varchar (255) ,
Row_Code_Val varchar (255) ,
Bastidor_Code_Val varchar (255) ,
Node_Code_Val varchar (255) ,
Rack_Code_Val varchar (255) ,
Chassis_Code_Val varchar (255),
Card_Code_Val varchar (255) ,
Lock_Code_Val varchar (255) ,
MDF_V_Pos_Code_Val varchar (255) ,
MDF_H_Pos_Code_Val varchar (255) ,
MDF_Pos_Code_Val varchar (255) ,
CTC_Ref_Code_Val varchar (255) ,
VCI_Vlan_Usr_Code_Val varchar (255) ,
Calling_Station_Code_Val varchar (255) ,
Technology_Type_Code_Val varchar (255) ,
ADSL_Conn_Status_Cd varchar (255) ,
NAS_Port_Val varchar (255) ,
NAS_IP_Address_Val varchar (255) ,
Quality_Type_Cd varchar (255) ,
Load_Process_Id varchar (255) )
INSERT INTO PASO_INVENTARIO2(
ADSL_Node_Equip_Instance_Id,
ADSL_Node_Port_Seq ,
ADSL_Node_Port_Conn_Start_Dt,
ADSL_Node_Port_Conn_End_Dt ,
Access_Method_Id ,
Emplacement_Agency_Id,
Room_Code_Val,
Row_Code_Val ,
Bastidor_Code_Val,
Node_Code_Val,
Rack_Code_Val ,
Chassis_Code_Val,
Card_Code_Val ,
Lock_Code_Val,
MDF_V_Pos_Code_Val,
MDF_H_Pos_Code_Val,
MDF_Pos_Code_Val,
CTC_Ref_Code_Val,
VCI_Vlan_Usr_Code_Val,
Calling_Station_Code_Val,
Technology_Type_Code_Val,
ADSL_Conn_Status_Cd,
NAS_Port_Val,
NAS_IP_Address_Val,
Quality_Type_Cd,
Load_Process_Id
)
select B.* from
(SELECT MAX(CAST(ADSL_Node_Port_Conn_Start_Dt as DATETIME)) ADSL_Node_Port_Conn_Start_Dt,
ADSL_Node_Equip_Instance_Id,
ADSL_Node_Port_Conn_End_Dt,
Room_Code_Val,
Row_Code_Val,
Bastidor_Code_Val,
Node_Code_Val,
Rack_Code_Val ,
Chassis_Code_Val,
Card_Code_Val,
Lock_Code_Val,
MDF_V_Pos_Code_Val,
MDF_H_Pos_Code_Val,
MDF_Pos_Code_Val,
CTC_Ref_Code_Val,
VCI_Vlan_Usr_Code_Val,
Technology_Type_Code_Val
FROM `adsl_node_port_connection`
WHERE ADSL_Node_Port_Conn_End_Dt = '9999-12-31 00:00:00' AND NAS_Port_Val is not null and NAS_IP_Address_Val is not null
GROUP BY ADSL_Node_Equip_Instance_Id
,ADSL_Node_Port_Conn_End_Dt ,Room_Code_Val,Row_Code_Val,Bastidor_Code_Val,Node _Code_Val
,Rack_Code_Val,Chassis_Code_Val,Card_Code_Val,Lock _Code_Val,MDF_V_Pos_Code_Val,MDF_H_Pos_Code_Val
,MDF_Pos_Code_Val,CTC_Ref_Code_Val,VCI_Vlan_Usr_Co de_Val,Technology_Type_Code_Val )A
LEFT JOIN `adsl_node_port_connection` B ON
A.ADSL_Node_Port_Conn_Start_Dt = B.ADSL_Node_Port_Conn_Start_Dt
AND A.ADSL_Node_Equip_Instance_Id = B.ADSL_Node_Equip_Instance_Id
AND A.ADSL_Node_Port_Conn_End_Dt = B.ADSL_Node_Port_Conn_End_Dt
AND A.Room_Code_Val = B.Room_Code_Val
AND A.Row_Code_Val = B.Row_Code_Val
AND A.Bastidor_Code_Val = B.Bastidor_Code_Val
AND A.Node_Code_Val = B.Node_Code_Val
AND A.Rack_Code_Val = B.Rack_Code_Val
AND A.Chassis_Code_Val = B.Chassis_Code_Val
AND A.Card_Code_Val = B.Card_Code_Val
AND A.Lock_Code_Val = B.Lock_Code_Val
AND A.MDF_V_Pos_Code_Val = B.MDF_V_Pos_Code_Val
AND A.MDF_H_Pos_Code_Val = B.MDF_H_Pos_Code_Val
AND A.MDF_Pos_Code_Val = B.MDF_Pos_Code_Val
AND A.CTC_Ref_Code_Val = B.CTC_Ref_Code_Val
AND A.VCI_Vlan_Usr_Code_Val = B.VCI_Vlan_Usr_Code_Val
AND A.Technology_Type_Code_Val = B.Technology_Type_Code_Val
select
cast(ADSL_Node_Equip_Instance_Id as INT)ID_EQUIPO,
NAS_Port_Val as NAS_PORT,
NAS_IP_Address_Val as NAS_IP,
' ' as ID_PROVEEDOR,
' ' as ID_MODELO,
' ' as ID_ENLACE,
cast(ADSL_Node_Equip_Instance_Id as INT)ID_EGREGADOR,
cast(ADSL_Conn_Status_Cd as INT)ID_ESTADOS,
cast(Emplacement_Agency_Id as INT)ID_EMPLAZAMIENTO_AGENCIA,
cast(Area_Code_Num_Val as VARCHAR(3))AREA,
cast(Telephone_Num_Val AS VARCHAR(10))FONO,
ADSL_Node_Port_Conn_Start_Dt FECHA_START,
Room_Code_Val,
Row_Code_Val,
Bastidor_Code_Val,
Node_Code_Val,
Rack_Code_Val,
Chassis_Code_Val,
Card_Code_Val,
Lock_Code_Val,
MDF_V_Pos_Code_Val,
MDF_H_Pos_Code_Val,
MDF_Pos_Code_Val,
CTC_Ref_Code_Val,
VCI_Vlan_Usr_Code_Val,
Calling_Station_Code_Val,
Technology_Type_Code_Val,
--INTO EQUIPO
from (select Telephone_Num_Val,Area_Code_Num_Val, A.* from
(select * FROM
(SELECT COUNT(NAS_Port_Val+NAS_IP_Address_Val) REPETIDOS
,CONCAT( NAS_Port_Val,'|',NAS_IP_Address_Val)NAS_POR_NAS_IP
FROM PASO_INVENTARIO2
GROUP BY NAS_Port_Val,NAS_IP_Address_Val) A
WHERE REPETIDOS = 1 ) b left join PASO_INVENTARIO2 A
ON NAS_POR_NAS_IP = CONCAT( NAS_Port_Val,'|',NAS_IP_Address_Val) left join `access_telephone_number` C
ON A. Access_Method_Id = C.Access_Method_Id ) d
WHERE Access_Method_Id NOT IN
(select Access_Method_Id from
(select count(Access_Method_Id) repetidos,Access_Method_Id from
(select A.* from
(select * FROM
(SELECT COUNT(NAS_Port_Val+NAS_IP_Address_Val) REPETIDOS
,CONCAT( NAS_Port_Val,'|',NAS_IP_Address_Val)NAS_POR_NAS_IP
FROM PASO_INVENTARIO2
GROUP BY NAS_Port_Val,NAS_IP_Address_Val) A
WHERE REPETIDOS = 1 ) b left join PASO_INVENTARIO2 A
ON NAS_POR_NAS_IP = CONCAT( NAS_Port_Val,'|',NAS_IP_Address_Val)) x
where Access_Method_Id <> '?'
group by Access_Method_Id) t
where repetidos > 1) |