Hi..
I am using an function and in that I am using dynamic sql. Currently I am handling not null values but now I need to handle null values for all rec.FB_LVL as well. Can anyone please tell me how I can use it in my below function.
FUNCTION F_adac_qry_ps(p_row_num NUMBER,
p_data_source VARCHAR2,
p_excl_incl_flag CHAR)
RETURN CLOB
IS
v_qry_str CLOB;
v_qry CLOB;
BEGIN
FOR lv_rec IN (SELECT *
FROM lr_config
WHERE row_num = p_row_num
AND data_source = p_data_source
AND excl_incl_flag = p_excl_incl_flag
AND is_active = 'Y') LOOP
v_qry := ' OR (1=1';
IF lv_rec.fb_lvl2_cd IS NOT NULL THEN
IF lv_rec.fb_lvl2_flag = v_incl_flag THEN
v_qry := v_qry ||' AND FB_LVL2_CD IN (' ||lv_rec.fb_lvl2_cd ||')';
ELSE
v_qry := v_qry ||' AND FB_LVL2_CD NOT IN (' ||lv_rec.fb_lvl2_cd ||')';
END IF;
END IF;
IF lv_rec.lv_prod_type_0_code IS NOT NULL THEN
v_qry := v_qry ||' AND PROD_TYPE_0_CODE IN (' ||lv_rec.lv_prod_type_0_code ||')';
END IF;
IF lv_rec.lv_prod_type_2_code IS NOT NULL THEN
v_qry := v_qry ||' AND PROD_TYPE_2_CODE IN (' ||lv_rec.lv_prod_type_2_code ||')';
END IF;
v_qry := v_qry ||' )';
v_qry_str := v_qry_str ||v_qry;
END LOOP;
RETURN v_qry_str;
END f_adac_qry_ps;