* ROWNUMBER() OVER(PARTITION BY T1.N_CONT ORDER BY T1.N_CONT ) AS NUMBER
SELECT
ROWNUMBER() OVER(PARTITION BY T1.N_CONT ORDER BY T1.N_CONT ) AS NUMBER , T1.N_CONT ,
CHAR(T1.R_FINAL)||'%' as Our_share,
T2.D_OFACP ,
T2.D_CFROM,
T2.D_CTO ,
T2.C_RITYPE ,
T5.R_USDEX ,
T2.c_intpd,
T2.c_intmb,
T2.c_intbi,
T2.c_intcgl,
T2.c_intoth,
T2.C_LOC ,
T2.c_covfa,
T2.c_coveq,
T2.c_covtp,
T2.c_covdl,
T2.c_covall,
T2.c_covoth,
T2.c_sacyc,
T2.A_TSI ,
T2.A_TSI / T5.R_USDEX AS TSI_USD,
CASE WHEN T2.C_METD = 'E' THEN 'EML'
WHEN T2.C_METD = 'P' THEN 'PML'
WHEN T2.C_METD = 'M' THEN 'MML'
WHEN T2.C_METD = 'L' THEN 'LIMIT'
WHEN T2.C_METD = 'T' THEN 'TOP'
END AS METHOD_EML,
T2.a_metd,
T2.a_metd / T5.R_USDEX AS METHOD_USD,
T2.A_LOL ,
T2.A_LOL / T5.R_USDEX AS LOL_USD,
T2.A_PRE ,
T2.A_PRE / T5.R_USDEX AS PRE_USD,
T2.r_pre ,
T2.r_ricom,
T2.r_brkcom,
T2.r_etc,
T2.r_cpcopro ,
T2.A_LOL * T2.r_cpcopro * 0.01 AS LOL_RCP,
T2.A_PRE * T2.r_cpcopro * 0.01 AS PRE_PCP,
T2.r_opt1pro ,
T2.A_LOL * T2.r_opt1pro * 0.01 AS LOL_O1,
T2.A_PRE * T2.r_opt1pro * 0.01 AS PRE_O1,
T2.r_opt2pro ,
T2.A_LOL * T2.r_opt2pro * 0.01 AS LOL_O2,
T2.A_PRE * T2.r_opt2pro * 0.01 AS PRE_O2,
T2.r_opt3pro ,
T2.A_LOL * T2.r_opt3pro * 0.01 AS LOL_O3,
T2.A_PRE * T2.r_opt3pro * 0.01 AS PRE_O3,
T2.r_kricpro ,
T2.A_LOL * T2.r_kricpro * 0.01 AS LOL_RK,
T2.A_PRE * T2.r_kricpro * 0.01 AS PRE_RK,
T2.r_final ,
T2.A_LOL * T2.r_final * 0.01 AS LOL_RF,
T2.A_PRE * T2.r_final * 0.01 AS PRE_RF,
t2.a_uwlay / T5.R_USDEX AS UWLAYER_USD,
T2.a_tsipd,
T2.a_tsimb,
T2.a_tsibi,
T2.a_tsibiip,
T2.a_tsimlop,
T2.a_tsimlopip,
T2.a_tsicgl,
T2.a_tsipd / T5.R_USDEX AS TSIPD_USD,
T2.a_tsimb / T5.R_USDEX AS TSIMB_USD,
T2.a_tsibi / T5.R_USDEX AS TSIBI_USD,
T2.a_tsibiip / T5.R_USDEX AS TSIBIIP_USD,
T2.a_tsimlop / T5.R_USDEX AS TSIMLOP_USD,
T2.a_tsimlopip / T5.R_USDEX AS TSIMLOPIP_USD,
T2.a_tsicgl / T5.R_USDEX AS TSICGL_USD,
T2.C_ZONE , t4.n_area,
T2.c_multi,
T2.C_PMLYN,
T2.A_DEDEQ,
DEC((T2.A_DEDEQ/ T5.R_USDEX),17,2) AS A_DEDEQUSD ,
CHAR(T2.R_DEDEQ)||'%' AS R_DEDEQ ,
T2.A_DEDTP,
DEC((T2.A_DEDTP/ T5.R_USDEX),17,2) AS A_DEDTPUSD ,
CHAR(T2.R_DEDTP)||'%' AS R_DEDTP,
T2.A_DEDDL,
DEC((T2.A_DEDDL/ T5.R_USDEX),17,2) AS A_DEDDLUSD ,
CHAR(T2.R_DEDDL)||'%' AS R_DEDDL,
T2.a_dedpd,
DEC((T2.a_dedpd/ T5.R_USDEX),17,2) AS A_DEDPDUSD ,
CHAR(T2.r_dedpd)||'%' AS r_dedpd,
T2.a_dedmb,
DEC((T2.a_dedmb/ T5.R_USDEX),17,2) AS A_DEDMBUSD ,
CHAR(T2.r_dedmb)||'%' AS r_dedmb,
T2.a_dedbi,
DEC((T2.a_dedbi/ T5.R_USDEX),17,2) AS A_DEDBIUSD ,
CHAR(T2.r_dedbi)||'%' AS r_dedbi,
T2.a_dedcgl,
DEC((T2.a_dedcgl/ T5.R_USDEX),17,2) AS A_DEDCGLUSD ,
CHAR(T2.r_dedcgl)||'%' AS r_dedcgl,
T2.a_dedoth,
DEC((T2.a_dedoth/ T5.R_USDEX),17,2) AS A_DEDOTHUSD ,
CHAR(T2.r_dedoth)||'%' AS r_dedoth ,
T2.a_uwlay,
DEC((T2.a_uwlay/ T5.R_USDEX),17,2) AS a_uwlayUSD ,
T2.a_eelsir,
DEC((T2.a_eelsir/ T5.R_USDEX),17,2) AS a_eelsirUSD ,
T2.a_aggsir,
DEC((T2.a_aggsir/ T5.R_USDEX),17,2) AS a_aggsirUSD ,
T2.a_hrfl,
T2.a_hrst,
T2.a_hreq,
T2.a_hrfs,
T2.q_eql,
T2.q_tpl,
T2.q_fll,
T2.q_terrl,
T2.q_srccl,
T2.c_losstype,
T2.c_exclterr,
T2.c_excltd ,
T2.a_tdcov ,
CASE WHEN T2.c_tdmea = 'F' THEN 'feet'
WHEN T2.c_tdmea = 'M' THEN 'meters'
WHEN T2.c_tdmea = 'I' THEN 'miles'
END AS COVER,
T2.c_exclnucl ,
T2.c_exclpol ,
T2.c_exclwar ,
T2.c_exclsanc ,
T2.c_exclasbe ,
T2.c_exclsrcc ,
T2.c_srcccur ,
T2.a_srcclimit
FROM RF_CONT T1 ,RF_FAC_CONT T2, rs_mexratet t5 ,MAJOR.rf_cd_zonet t4
WHERE
T1.N_CONT = T2.N_CONT
AND T1.N_CONT IN
('KEIO RAILROADS CECR ',
'TAIWAN POWER COMPANY ',
'500 KV KURGAN-KOZYREVO TRANSMISSION LINE',
'MACAU LIGHT RAILWAY ',
'AU OPTRONICS L8B PLANT CONST. ',
'SHANGHAI METRO LINE 11 NORTH P2 ',
'FANGCHENGGANG NUCLEAR POWER UNIT 1 & 2 ',
'HONGMEI-JINHAI CROSS RIVER SECTION ',
'HANGZHOU METRO LINE 2 SOUTHEAST SECTION ',
'XINJIANG DUSHANZI PETROCHEMICAL ',
'HANGZHOU METRO LINE 2 SOUTHEAST SECTION ',
'KUNMING METRO PHASE 1 ',
'HZMB, HONGKONG TO ZHUHAI TO MACAO BRIDGE',
'GUANGZHOU METRO LINE 9 ',
'GUANGZHOU METRO LINE 6 PHASE2, 7 ',
'YANGJIANG NUCLEAR POWER PLANT 3 & 4 ',
'FANGCHENGGANG NUCLEAR POWER UNIT 1 & 2 ',
'LIAONING HONGYANHE NPP UNIT 5 & 6 PHASE ',
'XIAN METRO LINE 2 SOUTH EXTENSION ',
'ZHENGZHOU METRO LINE 1 PHASE 1 ',
'WUHAN METRO LINE 4 PHASE 1 ',
'HZMB, HONGKONG TO ZHUHAI TO MACAO BRIDGE',
'FUQING NUCLEAR POWER PHASE 2 ',
'CHINA RAILWAY CONSTRUCTION CORP ',
'OMAN CEMENT COMPANY OCC 2ND EXPANSION ',
'SHAZAND ARAK EXPANSION & UPGRADING PROJE',
'SHAZAND ARAK EXPANSION & UPGRADING PROJE',
'SHANGHAI METRO LINE NO.9 PART II ',
'FUZHOU HUADONG SHIPBUILDING CONSTRUCTION',
'SHANGHAI METRO LINE 11 NORTH P2 ',
'DALIAN METRO LINE 1&2 PHASE 2 ',
'DALIAN METRO LINE 1&2 PHASE 3 ',
'CHONGQING METRO LINE 6 PH1 EXTENSION(COM',
'CHONGQING METRO LINE 6 PHASE 2 ',
'BAOLAN RAILROAD GANSU(BLBX-1) ',
'ALERIA WATER PUMPING STATION PROJECT ',
'NANGAO(NANJING TO GAOCHUN) RAILWAY ',
'GUANGZHOU METRO LINE 9 ',
'GUANGZHOU METRO LINE 6 PHASE2, 7 ',
'BEIJING METRO LINE 15 WEST SECTION ',
'YINGLITE NINGDONG COAL TO METHANOL PLANT',
'CNPC FUSHUN PETRO CHEMICAL PLANT ',
'BEIJING METRO LINE 14 TEMPORARY COVER(3 ',
'SINOCHEM QUANZHOU REFINERY(12M T/YEAR) ',
'JIN AN QIAO HYDRO POWER ',
'QUANG NINH COAL-FIRED THERMAL PLANT ',
'DONGGI SENORO LNG(DSLNG) ',
'ELECTRICITY WATER OR KUWAIT ARAB ',
'TRANSMISSION LINE 500KV MAE MOH 3 - THA ',
'TURBINE INLET AIR COOLING SYSTEM AT POWE',
'ExPANSION OF CAUSTIC SODA PLANT ',
'CONSTRUCTION OF 1B+1G+57 FLOOR ',
'BOUBYAN SEAPORT PROJECT ',
'AL KHAIRAT & MUSUL POWER PLANT ',
'PT. AGRO PERKASA MEWAH OIL ',
'FORMOSA HA TINH(STEEL PLANT) VIETNAM ',
'THAI PARAXYLENE TPX REVAMP PROJECT ',
'ALGERIA HYFLUX DESALINATION PROJECT ',
'PINNACLE HEALTH HOSPITAL ORANGE CITY ',
'DORAD ENERGY ASHKELON ISRAEL ',
'HONDURAS AMERICAN BRIDGE ',
'CITRA METRO MANILA TOLLWAYS ',
'ANDERSON ASPHALT MAJURO INTERNATIONAL ',
'ADCOP PIPELINE ADN TERMINAL WORKS ',
'SAMUR PROJECT ',
'FORMOSA PETROCHEMICAL REFINERY PROJECT ',
'EVER POWER IPP CCPP HAI FU ',
'TAIPEI FUBON COMMERCIAL BANK ',
'CHIAHUI POWER CORP CMI 2011 ',
'ARDENTEC CAPITAL EXPENDITURE PROJECT ',
'SIAM YAMATO STEEL MINI MILL PROJECT ',
'SONG TRANH II HPP ',
'HOANG THACH CEMENT PLANT ',
'A LUOI HYDOR POWER PLANT 170MW ',
'SON LA HYDRO POWER DAM ',
'HAI PHONG THERMAL PLANT PROJECT ',
'EVN TELECOMMUNICATION INFORMATION AND OP',
'OASIS HOTEL ',
'AIRPORT OF LUBANGO, AT THE HUILA PROVINC',
'MMK ATAKAS STEEL MILL PLANT ',
'KRAYOT BYPASS SHAPIR ENGINEERING ',
'LAJA SUEZ ENERGY HYDRO POWER ',
'COLBUN ANGOSTURA HYDROPOWER ',
'GDF QUITARACSA 1 HYDROPOWER ',
'KARAGAILINSKAYA COAL MINE ',
'EFT STANARI POWER PLANT ',
'TUPRAS RESIDUUM UPGRADING PROJECT ',
'RENAISSANCE FERTILIZER TURKMENISTAN ',
'ENTENSION OF LINE 3 HAIDARI-PIRAEUS SECT',
'CHAGLLA HYDROELECTRIC POWER PLANT ',
'REHABILITATION AND CONSTRUCTION OF THE R',
'ELIZABETH RIVER CROSSING TUNNEL PROJECT ',
'ISRAEL DEFENCE FORCE CITY PROJECT ',
'LLX TX2 ACU SUPER PORT COMPLEX PROJECT B',
'GUACOLDA COAL FIRED POWER PLANT ',
'BELORUSSKAYA NUCLEAR POWER PLANT BELARUS',
'FENIX COMBINED CYCLE CCPP ',
'PETROBRAS DEC 20 LPG TERMINAL ',
'EAST SIDE ACCESS TBM OPERATION ',
'REFICAR CARTAGENA REFINERY EXPANSION ',
'MAPLE ETHANOL PLANT ',
'YPF HTG-B PROJECT ',
'REHABILITATION OF LLAMA TO COCHABAMBA ',
'OYU TOLGOI MINE DEVELOPMENT ',
'ITUANGO HYDRO POWER COLOMBIA ',
'MIGDAL TOP PROPERTIES ',
'ENRC KAZCHROME MELT SHOP PROJECT ',
'GEDERA JUNCTION ISRAEL ',
'CAIRO METRO LINE 3 VINCI & BOUYGES ',
'TOBOLSK POLYMER PHD/PP PLANT ',
'NOVY URENGOY GAS CHEMICALS COMPLEX2 ',
'BAITUN HYDRO POWER ',
'PERAVIA WATER SUPPLY SYSTEM ',
'SOLAR POLYSILICON PLANT ',
'YELLOW EXPRESS ROAD OAS ',
'URENGOI POWER PLANT CCPP ',
'BAHA MAR BEACH RESORT ',
'PORTOVAYA COMPRESSOR STATION ',
'PERM THERMAL POWER PLANT PROJECT ',
'NIZHNEVARTOVSKAYA GRES POWER PLANT PROJE',
'1600MW POWER PROJECT, NONG SAENG ',
'ESCRAVOS GAS TO LIQUIDS EGTL CHEVRON ',
'PAUL WURTH ANNUAL PROGRAMME ',
'POLYCRYSTALLINE SILICON PLANT AT SIMILAJ',
'TROITSKAYA GRES COAL FIRED POWER PLANT ',
'CEPSA PHENOL AND CUMENE CHEMICAL PLANT ',
'LUKOIL PERMNEFTEORSINTEZ PERM REFINERY ',
'KIRINSKYI GCF SAKHALIN ',
'GIZA NORTH & BANHA COMBINED CYCLE POWER ',
'REHABILITATION OF CAMBAMBE DAM ENSA ANGO',
'ATLAS POWER GENERATION COMPLEX ',
'DROR INTERCHANGE ',
'KUWAIT POLICE COLLEGE ',
'2 TOWERS AND PLAZA ',
'LUSAIL DEVELOPMENT PROJECT ',
'KING SAUD BIN ABDULAZIZ UNIV. GIRLS ',
'SOUTHERN BORDER SECURITY ',
'WELL 1 WATER MANAGEMENT SYSTEM MAKKAH ',
'R881/3B-COMPREHENSIVE IMPROVEMENT OF THE',
'HABTOOR PALACE HOTELS COMPLEX ',
'RELIANCE PETROLEUM ',
'ERECTION OF YUNUS EMRE COAL FIRED POWER ',
'PALM CANAL TOWER, DUBAI WATERFRONT PHASE',
'RAMU NICKEL MINE AND REFINERY PROJECT ',
'BRUNEI & MALAYSIA LNG FACILITIES ',
'LNG STORAGE &RE-GASSIFICATION FACILITY ',
'DONG NAI 5 HPP 150MW ')
AND T1.C_DEPT = '301000'
and t2.d_appym = t5.d_appym
and t1.c_cur = t5.c_cur
and t2.c_zone = t4.c_zone
사용계기 - ROWNUMBER() OVER() 를 사용하려고 했던 계기는 다른 값들 상관어벖이 동일한 계약명중 한건의 데이터만 가져오고 싶어서 사용해보았다.
그림 1)
그림1) 은 ROWNUMBER() OVER() 를 사용하지 않았을때.
그림2)
그림2) 은 ROWNUMBER() OVER() 를 사용했을때 계약명 앞에 동일한 계약명이 있을경우 1 , 2 순서가 매겨진다.
그래서 조건절에 number = 1 인 데이터만 추출하겠다는 조건을 주었을때 아래 그림3) 과 같이 1만 추출이되고 , 중복된 데이터가 나오지 않게 된다.
그림3)
