61 lines
2.0 KiB
MySQL
61 lines
2.0 KiB
MySQL
|
|
/*
|
|
exec SP_COMMON_SEARCH_CODE2 'STRATEGY_NUM', '', ''
|
|
exec SP_COMMON_SEARCH_CODE2 'INDICATOR', 'trade_status_ind', '10'
|
|
*/
|
|
/*
|
|
-- =============================================
|
|
-- SQLINES LICENSE FOR EVALUATION USE ONLY
|
|
|
|
CREATE OR REPLACE PROCEDURE SP_COMMON_SEARCH_CODE2 (
|
|
-- Add the parameters for the stored procedure here
|
|
P_CODE_GBN NVARCHAR2(20),
|
|
P_SQL_CONDITION1 NVARCHAR(MAX),
|
|
P_SQL_CONDITION2 NVARCHAR(MAX))
|
|
AS
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
|
|
-- Insert statements for procedure here
|
|
|
|
V_SQL NVARCHAR(MAX);
|
|
V_FIRST_SQL NVARCHAR(MAX);
|
|
V_LAST_SQL NVARCHAR(MAX);
|
|
V_WHERE_SQL1 NVARCHAR(MAX) := '';
|
|
V_WHERE_SQL2 NVARCHAR(MAX) := '';
|
|
V_PARAM_DEFIN NVARCHAR2(1000) := '';BEGIN
|
|
|
|
*/
|
|
|
|
---------------------------------------------------------------------------------------------------------------
|
|
|
|
SELECT
|
|
'STRATEGY_NUM' AS CODE_GBN
|
|
, STRATEGY_NUM AS CODE
|
|
, STRATEGY_NAME AS CODE_NM
|
|
, STRATEGY_NAME AS ORDER_COL
|
|
FROM ORG_STRATEGY
|
|
WHERE 1 = 1
|
|
/* #{P_SQL_CONDITION1} NOT NULL 인 경우 */
|
|
AND BOOK_CD IN (
|
|
SELECT REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION1},''),'[^,]+', 1, LEVEL) FROM DUAL
|
|
CONNECT BY REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION1},''), '[^,]+', 1, LEVEL) IS NOT NULL )
|
|
/* #{P_SQL_CONDITION2} NOT NULL 인 경우 */
|
|
AND PORTFOLIO_CD IN (
|
|
SELECT REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION2},''),'[^,]+', 1, LEVEL) FROM DUAL
|
|
CONNECT BY REGEXP_SUBSTR(NVL(#{P_SQL_CONDITION2},''), '[^,]+', 1, LEVEL) IS NOT NULL )
|
|
---------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
SELECT
|
|
'INDICATOR' AS CODE_GBN
|
|
, IND_VALUE_NAME AS VALUE
|
|
FROM CXL_PROD_DB.DBO.GEN_INDICATOR_VALUE
|
|
WHERE 1=1
|
|
/* #{P_SQL_CONDITION1} NOT NULL 인 경우 */
|
|
AND IND_NAME = #{P_SQL_CONDITION1}
|
|
/* #{P_SQL_CONDITION2} NOT NULL 인 경우 */
|
|
AND IND_VALUE = #{P_SQL_CONDITION2}
|
|
|
|
---------------------------------------------------------------------------------------------------------------
|