select a.empid, a.ename, a.job, b.salary 
from employee a, emp_details b
where a.empid=b.empid;

select a.empid, a.ename, a.job, b.salary 
from employee a, emp_details b
where a.empid=b.empid;

select
/*+ index(A, ADPO_CABC_IDX) index(B, ABM_CABC_IDX) index(AA, ADPI_PORT_IDX) index(A, ADPO_PORT_IDX) index(AA, ADPI_CABC_IDX) index(C, APV_EQUP_IDX) */
port_name, EQUP_LOCN_TTNAME||'_'||EQUP_index ADSLAM_IDENT_CODE,
case instr(PORT_NAME, '-P')
when 0 then '0000'
else substr(PORT_NAME,-4,4)
end DSLAM_Pair_num,
FRAA_POSITION ADSL_pair_num, FRAC_LOCN_TTNAME||' '||BB.FRAU_POSITION||' '||FRAU_POSITION
ADSL_BAR_IDENT_CODE,
FRAA_STATUS INVENT_STATUS_CODE1, a.CIRT_STATUS INVENT_STATUS_CODE2,
case when a.PORT_STATUS='BADPORT' or a.PORT_STATUS='LOCK' then a.PORT_STATUS
when a.CIRT_STATUS is not null then a.CIRT_STATUS
when a.CIRT_STATUS is null and FRAA_STATUS = 'INSERVICE' then 'SPARE'
else FRAA_STATUS end INVENT_STATUS_CODE,
EQUP_INDEX DSLAM_IND,
start_port START_PORT_NUM, end_port END_PORT_NUM, tonumeric(start_vci) START_VCI_NUM, tonumeric(end_vci) END_VCI_NUM,
case when instr(A.CIRT_DISPLAYNAME, 'SDSL') >0 then substr(A.CIRT_DISPLAYNAME,-16,8)
when instr(A.CIRT_DISPLAYNAME, 'MIPS') >0 then null
when instr(A.CIRT_DISPLAYNAME, 'MS') >0 then null
when instr(A.CIRT_DISPLAYNAME, 'ADSL') >0 then substr(A.CIRT_DISPLAYNAME,-11)
else substr(A.CIRT_DISPLAYNAME,1,11)
end SERVICE_NUM,
CARD_NAME CARD_TYPE,
case instr(PORT_NAME, '-V') when 0 then substr(PORT_NAME,instr(PORT_NAME,'-',-1,1)+1) else substr(PORT_NAME,instr(PORT_NAME,'-V',1)+2,4) end VCI_NUM,
-- nvl(POST_SERT_ABBREVIATION,CIRT_SERT_ABBREVIATION) PRODUCT_OWNERSHIP_CODE, 
PRODUCT_OWNERSHIP PRODUCT_OWNERSHIP_CODE, FRAC_fran_name FRAME_TYPE,
null cabinet_ident_code, null dpsdf_ident_code
from t_nis_adslmpr_dslam_pots_out A join t_nis_adslmpr_bar_mdf B on A.CACE_CABC_ID = B.CACE_CABC_ID
left join  t_NIS_adslmpr_dslam_pots_in AA on AA.PORH_PARENTID = A.PORT_ID
join T_NIS_adslmpr_bar_mdf BB on AA.CACE_CABC_ID = BB.CACE_CABC_ID
left join t_nis_adslmpr_port_vci C on C.EQUP_id = A.EQUP_id

select FRAC_fran_name, FRAC_LOCN_TTNAME, FRAU_POSITION, FRAA_POSITION, fraa_status, FRAA_CIRT_NAME, 
cirt_displayname, cirt_status, fraa_cace_id, CACE_CABC_ID
from frame_containers join FRAME_UNITS on frac_id = frau_frac_id
join FRAME_APPEARANCES on FRAU_ID = FRAA_FRAU_ID
left join circuits on FRAA_CIRT_NAME = cirt_name
left join CABLE_CORE_ENDS on CACE_ID = FRAA_CACE_ID
where FRAU_NAME like 'DSL_IN%' or FRAU_NAME like 'DSL_OUT%' and FRAC_fran_name = 'MDF'
and FRAA_SIDE = 'REAR';

SELECT /*+ index(A, ADPO_CABC_IDX)  index(B, ABM_CABC_IDX)
           index(AA, ADPI_PORT_IDX) index(A, ADPO_PORT_IDX) 
           index(AA, ADPI_CABC_IDX) index(C, APV_EQUP_IDX) */
       port_name,
       equp_locn_ttname
        || '_'
        || equp_index       adslam_ident_code,
       CASE Instr(port_name, '-P')
         WHEN 0 THEN '0000'
         ELSE Substr(port_name, -4, 4)
       END                  dslam_pair_num,
       fraa_position        adsl_pair_num,
       frac_locn_ttname
        || ' '
        || bb.frau_position
        || ' '
        || frau_position    adsl_bar_ident_code,
       fraa_status          invent_status_code1,
       a.cirt_status        invent_status_code2,
       CASE
         WHEN a.port_status = 'BADPORT'
               OR a.port_status = 'LOCK' THEN a.port_status
         WHEN a.cirt_status IS NOT NULL THEN a.cirt_status
         WHEN a.cirt_status IS NULL
              AND fraa_status = 'INSERVICE' THEN 'SPARE'
         ELSE fraa_status
       END                  invent_status_code,
       equp_index           dslam_ind,
       start_port           start_port_num,
       end_port             end_port_num,
       Tonumeric(start_vci) start_vci_num,
       Tonumeric(end_vci)   end_vci_num,
       CASE
         WHEN Instr(a.cirt_displayname, 'SDSL') > 0 THEN Substr(a.cirt_displayname, -16, 8)
         WHEN Instr(a.cirt_displayname, 'MIPS') > 0 THEN NULL
         WHEN Instr(a.cirt_displayname, 'MS') > 0 THEN NULL
         WHEN Instr(a.cirt_displayname, 'ADSL') > 0 THEN Substr(a.cirt_displayname, -11)
         ELSE Substr(a.cirt_displayname, 1, 11)
       END                  service_num,
       card_name            card_type,
       CASE Instr(port_name, '-V')
         WHEN 0 THEN Substr(port_name, Instr(port_name, '-', -1, 1) + 1)
         ELSE Substr(port_name, Instr(port_name, '-V', 1) + 2, 4)
       END                  vci_num,
       -- nvl(POST_SERT_ABBREVIATION,CIRT_SERT_ABBREVIATION) PRODUCT_OWNERSHIP_CODE, 
       product_ownership    product_ownership_code,
       frac_fran_name       frame_type,
       NULL                 cabinet_ident_code,
       NULL                 dpsdf_ident_code
FROM   t_nis_adslmpr_dslam_pots_out a
       JOIN t_nis_adslmpr_bar_mdf b
         ON a.cace_cabc_id = b.cace_cabc_id
       LEFT JOIN t_nis_adslmpr_dslam_pots_in aa
         ON aa.porh_parentid = a.port_id
       JOIN t_nis_adslmpr_bar_mdf bb
         ON aa.cace_cabc_id = bb.cace_cabc_id
       LEFT JOIN t_nis_adslmpr_port_vci c
         ON c.equp_id = a.equp_id