There is a requirement at work that requires a lot of parameters, such as the following SQL,
Copy codeThe Code is as follows:
Select
Ff. fundsc | '-' | ff. fundtzfs | '-' | ff. fundcjfl | '-' | ff. fundonefl | '-' | ff. fundtowfl catagory,
Sf. scode, replace (sf. fund5, 'securities investment fund ', '') fund5, sf. fund4, sf. fund10,
(Select to_date (tradedate, 'yyyy-MM-dd') from sdc_fundnetassetvalue where scode = sf. scode and tradedate in
(Select max (tradedate) from sdc_fundnetassetvalue where scode = sf. scode and to_date (tradedate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and tradedate <= 20120801) and rownum <2) trdatedate,
(Select unitvalue from sdc_fundnetassetvalue where scode = sf. scode and tradedate in
(Select max (tradedate) from sdc_fundnetassetvalue where scode = sf. scode and to_date (tradedate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and tradedate <= 20120801) and rownum <2) fejz,
(Select accumulatedunitvalue from sdc_fundnetassetvalue where scode = sf. scode and tradedate in
(Select max (tradedate) from sdc_fundnetassetvalue where scode = sf. scode and to_date (tradedate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and tradedate <= 20120801) and rownum <2) feljjz,
(Select f30004_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2) gqyzjzzzl,
(Select f30006_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2) gqyyjzzzl,
Decode (select f30007_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30007_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') gqsgyjzzzl,
Row_number () over (partition by ff. fundsc | '-' | ff. fundtzfs | '-' | ff. fundcjfl | '-' | ff. fundonefl | '-' | ff. fundtowfl
Order by decode (select f30007_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30007_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') desc) sgy,
Decode (select f30008_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30008_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') gqbnjzzzl,
Row_number () over (partition by ff. fundsc | '-' | ff. fundtzfs | '-' | ff. fundcjfl | '-' | ff. fundonefl | '-' | ff. fundtowfl
Order by decode (select f30008_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30008_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') desc) bn,
Decode (select F30017_30003 from si_fund_30003 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30003 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select F30017_30003 from si_fund_30003 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30003 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') jinnian,
Row_number () over (partition by ff. fundsc | '-' | ff. fundtzfs | '-' | ff. fundcjfl | '-' | ff. fundonefl | '-' | ff. fundtowfl
Order by decode (select F30017_30003 from si_fund_30003 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30003 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select F30017_30003 from si_fund_30003 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30003 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') desc) jn,
Decode (select f30009_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30009_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') gqynjzzzl,
Row_number () over (partition by ff. fundsc | '-' | ff. fundtzfs | '-' | ff. fundcjfl | '-' | ff. fundonefl | '-' | ff. fundtowfl
Order by decode (select f30009_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30009_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') desc) gqyn,
Decode (select f30010_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30010_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') gqlnjzzzl,
Row_number () over (partition by ff. fundsc | '-' | ff. fundtzfs | '-' | ff. fundcjfl | '-' | ff. fundonefl | '-' | ff. fundtowfl
Order by decode (select f30010_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30010_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') desc) gqln,
Decode (select f30011_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2 ),
Null, '-', to_char (select f30011_30002 from si_fund_30002 where f_fundcode = sf. scode and f_type = 0 and f_tradingdate in
(Select max (f_tradingdate) from si_fund_30002 where f_fundcode = sf. scode and to_date (f_tradingdate, 'yyyymmdd')> = (to_date (20120801, 'yyyymmdd')-10) and f_tradingdate <= 20120801) and rownum <2), 'fm99999999990. 9999 ') gqsnjzzzl
From
(Select
B. scode, B. fund5, B. fund4, B. fund10
From
Sdc_fundbase B left join sdc_security a on B. scode = a. scode and B. mktcode = a. mktcode and B. fund49 = a. stype
Where
B. scode in (select t. fundcode from fund_fundcategoryone t where (t. fundtzfs = 'open 'or t. fundtzfs = 'closed') and t. fundonefl! = 'Money market funds' and t. fundonefl! = 'Transaction class ')
And (. enddate> = to_date (20120801, 'yyyy-MM-dd') or. enddate is null or. enddate = to_date (19000101, 'yyyy-MM-dd '))
And a. stype in (6, 7) order by scode
) Sf
Left join fund_fundcategoryone ff on sf. scode = ff. fundcode and ff. fundonefl! = 'Transaction class'
It is very complicated and I will not elaborate on it. At this time, I need to input all the 20120801 parameters, and I will be exhausted by writing only the parameters. The following method can save a lot of work, modify select in the most internal function
B. scode, B. fund5, B. fund4, B. fund10, 20120801 tdate
From
Replace 20120801 with the tdate variable, so that the input parameter is represented by tdate.