OraFAQ Forum: SQL & PL/SQL » How to handle null values in function having dynamic sql

Home » SQL & PL/SQL » SQL & PL/SQL » How to handle null values in function having dynamic sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to handle null values in function having dynamic sql [message #613770]

Mon, 12 May 2014 04:02

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

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 test_sample(
p_num NUMBER,

) RETURN CLOB IS

query_str CLOB;
qry CLOB;

BEGIN

FOR rec IN (SELECT * FROM item_details WHERE data_source ='INERTNAL' AND is_active = 'Y'
)
)
LOOP
qry :='
OR (1=1';

IF rec.FB_LVL2 IS NOT NULL THEN
IF rec.FB_LVL2_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL2 IN ('||rec.FB_LVL2||')';
ELSE
qry := qry||'
AND FB_LVL2 NOT IN ('||rec.FB_LVL2||')';
END IF;
END IF;

IF rec.FB_LVL3 IS NOT NULL THEN
IF rec.FB_LVL3_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL3 IN ('||rec.FB_LVL3||')';
ELSE
qry := qry||'
AND FB_LVL3 NOT IN ('||rec.FB_LVL3||')';
END IF;
END IF;

IF rec.FB_LVL4 IS NOT NULL THEN
IF rec.FB_LVL4_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL4 IN ('||rec.FB_LVL4||')';
ELSE
qry := qry||'
AND FB_LVL4 NOT IN ('||rec.FB_LVL4||')';
END IF;
END IF;

IF rec.FB_LVL5 IS NOT NULL THEN
IF rec.FB_LVL5_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL5 IN ('||rec.FB_LVL5||')';
ELSE
qry := qry||'
AND FB_LVL5 NOT IN ('||rec.FB_LVL5||')';
END IF;
END IF;

IF rec.FB_LVL6 IS NOT NULL THEN
IF rec.FB_LVL6_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL6 IN ('||rec.FB_LVL6||')';
ELSE
qry := qry||'
AND FB_LVL6 NOT IN ('||rec.FB_LVL6||')';
END IF;
END IF;

IF rec.FB_LVL7 IS NOT NULL THEN
IF rec.FB_LVL7_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL7 IN ('||rec.FB_LVL7||')';
ELSE
qry := qry||'
AND FB_LVL7 NOT IN ('||rec.FB_LVL7||')';
END IF;
END IF;

IF rec.FB_LVL8 IS NOT NULL THEN
IF rec.FB_LVL8_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL8 IN ('||rec.FB_LVL8||')';
ELSE
qry := qry||'
AND FB_LVL8 NOT IN ('||rec.FB_LVL8||')';
END IF;
END IF;

IF rec.FB_LVL9 IS NOT NULL THEN
IF rec.FB_LVL9_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL9 IN ('||rec.FB_LVL9||')';
ELSE
qry := qry||'
AND FB_LVL9 NOT IN ('||rec.FB_LVL9||')';
END IF;
END IF;

IF rec.FB_LVL10 IS NOT NULL THEN
IF rec.FB_LVL10_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL10 IN ('||rec.FB_LVL10||')';
ELSE
qry := qry||'
AND FB_LVL10 NOT IN ('||rec.FB_LVL10||')';
END IF;
END IF;

IF rec.LV_PROD_TYPE_0_CODE IS NOT NULL THEN
qry := qry||'
AND PROD_TYPE_0_CODE IN ('||rec.LV_PROD_TYPE_0_CODE||')';
END IF;

IF rec.LV_PROD_TYPE_2_CODE IS NOT NULL THEN
qry := qry||'
AND PROD_TYPE_2_CODE IN ('||rec.LV_PROD_TYPE_2_CODE||')';
END IF;

qry:= qry||'
)';
query_str := query_str||qry;

END LOOP;

RETURN query_str;

END test_sample;

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613771 is a reply to message #613770]

Mon, 12 May 2014 04:08

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You didn't feedback in your previous topics.
You ask, ask, ask and never thank people.
In addition, you refuse to follow the guide.
This is your choice.
Mine is to no more help you.

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613772 is a reply to message #613771]

Mon, 12 May 2014 04:10

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Hi Michel..

I apologies for my behaviour. Please help me on my issue I need to fix this urgently.Please help.

Thanks in advanced.

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613774 is a reply to message #613770]

Mon, 12 May 2014 04:26

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

The code is unreadable. Also, it has syntax errors. That function won't even compile.

na.dharma@gmail.com wrote on Mon, 12 May 2014 14:32

Currently I am handling not null values but now I need to handle null values for all rec.FB_LVL as well.

IF you know how to handle NOT NULL values, then what is stopping you from handling NULL values? At first glance it looks like you just need a simple IF-ELSE construct. What exactly is the issue?

[Updated on: Mon, 12 May 2014 04:28]

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613780 is a reply to message #613774]

Mon, 12 May 2014 05:03

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Hi Lalit..

IF rec.FB_LVL2 IS NOT NULL THEN
IF rec.FB_LVL2_FLAG = 'I' THEN
qry := qry||'
AND FB_LVL2 IN ('||rec.FB_LVL2||')';
ELSE
qry := qry||'
AND FB_LVL2 NOT IN ('||rec.FB_LVL2||')';
END IF;
END IF;

In above code I have handled not null values..now can you please tell how I use rec.FB_LVL2 IS NULL condition in the same block.

thanks in advanced

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613781 is a reply to message #613780]

Mon, 12 May 2014 05:14

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

1. Why don't you use code tags?
2. What are the rules for the IS NULL condition? How would I or anyone know about the logic which you are keeping secret with yourself?

   IF REC.FB_LVL2 IS NOT NULL 
   THEN
      IF REC.FB_LVL2_FLAG = 'I' 
      THEN
         QRY := QRY || '
                        AND FB_LVL2 IN (' || REC.FB_LVL2 || ')';
      ELSE
         QRY := QRY || '
                        AND FB_LVL2 NOT IN (' || REC.FB_LVL2 || ')';
      END IF; -- nested scope ends here
      
   << Your IS NULL condition goes here >> -- this is with the main scope
      
   END IF; -- this ends the main scope to check NULL values

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613783 is a reply to message #613780]

Mon, 12 May 2014 05:15

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I know you know how to use code tags, so stop being lazy and use them.
As for your question - add an else to the outer if.

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613784 is a reply to message #613781]

Mon, 12 May 2014 05:19

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Hi Lalit.

PFB my correct function code..

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.FB_LVL3_CD IS NOT NULL THEN
IF lv_rec.FB_LVL3_FLAG = v_incl_flag THEN
v_qry := v_qry||'
AND FB_LVL3_CD IN ('||lv_rec.FB_LVL3_CD||')';
ELSE
v_qry := v_qry||'
AND FB_LVL3_CD NOT IN ('||lv_rec.FB_LVL3_CD||')';
END IF;
END IF;

IF lv_rec.FB_LVL4_CD IS NOT NULL THEN
IF lv_rec.FB_LVL4_FLAG = v_incl_flag THEN
v_qry := v_qry||'
AND FB_LVL4_CD IN ('||lv_rec.FB_LVL4_CD||')';
ELSE
v_qry := v_qry||'
AND FB_LVL4_CD NOT IN ('||lv_rec.FB_LVL4_CD||')';
END IF;
END IF;

IF lv_rec.FB_LVL5_CD IS NOT NULL THEN
IF lv_rec.FB_LVL5_FLAG = v_incl_flag THEN
v_qry := v_qry||'
AND FB_LVL5_CD IN ('||lv_rec.FB_LVL5_CD||')';
ELSE
v_qry := v_qry||'
AND FB_LVL5_CD NOT IN ('||lv_rec.FB_LVL5_CD||')';
END IF;
END IF;

IF lv_rec.FB_LVL6_CD IS NOT NULL THEN
IF lv_rec.FB_LVL6_FLAG = v_incl_flag THEN
v_qry := v_qry||'
AND FB_LVL6_CD IN ('||lv_rec.FB_LVL6_CD||')';
ELSE
v_qry := v_qry||'
AND FB_LVL6_CD NOT IN ('||lv_rec.FB_LVL6_CD||')';
END IF;
END IF;

IF lv_rec.FB_LVL7_CD IS NOT NULL THEN
IF lv_rec.FB_LVL7_FLAG = v_incl_flag THEN
v_qry := v_qry||'
AND FB_LVL7_CD IN ('||lv_rec.FB_LVL7_CD||')';
ELSE
v_qry := v_qry||'
AND FB_LVL7_CD NOT IN ('||lv_rec.FB_LVL7_CD||')';
END IF;
END IF;

IF lv_rec.FB_LVL8_CD IS NOT NULL THEN
IF lv_rec.FB_LVL8_FLAG = v_incl_flag THEN
v_qry := v_qry||'
AND FB_LVL8_CD IN ('||lv_rec.FB_LVL8_CD||')';
ELSE
v_qry := v_qry||'
AND FB_LVL8_CD NOT IN ('||lv_rec.FB_LVL8_CD||')';
END IF;
END IF;

IF lv_rec.FB_LVL9_CD IS NOT NULL THEN
IF lv_rec.FB_LVL9_FLAG = v_incl_flag THEN
v_qry := v_qry||'
AND FB_LVL9_CD IN ('||lv_rec.FB_LVL9_CD||')';
ELSE
v_qry := v_qry||'
AND FB_LVL9_CD NOT IN ('||lv_rec.FB_LVL9_CD||')';
END IF;
END IF;

IF lv_rec.FB_LVL10_CD IS NOT NULL THEN
IF lv_rec.FB_LVL10_FLAG = v_incl_flag THEN
v_qry := v_qry||'
AND FB_LVL10_CD IN ('||lv_rec.FB_LVL10_CD||')';
ELSE
v_qry := v_qry||'
AND FB_LVL10_CD NOT IN ('||lv_rec.FB_LVL10_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;

Apologies for the incovienece as previously I have psted the wrong code..

Thanks..

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613785 is a reply to message #613784]

Mon, 12 May 2014 05:25

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Please use the below formatter
http://www.dpriver.com/pp/sqlformat.htm

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.fb_lvl3_cd IS NOT NULL THEN 
          IF lv_rec.fb_lvl3_flag = v_incl_flag THEN 
            v_qry := v_qry 
                     ||' AND FB_LVL3_CD IN (' 
                     ||lv_rec.fb_lvl3_cd 
                     ||')'; 
          ELSE 
            v_qry := v_qry 
                     ||' AND FB_LVL3_CD NOT IN (' 
                     ||lv_rec.fb_lvl3_cd 
                     ||')'; 
          END IF; 
        END IF; 

        IF lv_rec.fb_lvl4_cd IS NOT NULL THEN 
          IF lv_rec.fb_lvl4_flag = v_incl_flag THEN 
            v_qry := v_qry 
                     ||' AND FB_LVL4_CD IN (' 
                     ||lv_rec.fb_lvl4_cd 
                     ||')'; 
          ELSE 
            v_qry := v_qry 
                     ||' AND FB_LVL4_CD NOT IN (' 
                     ||lv_rec.fb_lvl4_cd 
                     ||')'; 
          END IF; 
        END IF; 

        IF lv_rec.fb_lvl5_cd IS NOT NULL THEN 
          IF lv_rec.fb_lvl5_flag = v_incl_flag THEN 
            v_qry := v_qry 
                     ||' AND FB_LVL5_CD IN (' 
                     ||lv_rec.fb_lvl5_cd 
                     ||')'; 
          ELSE 
            v_qry := v_qry 
                     ||' AND FB_LVL5_CD NOT IN (' 
                     ||lv_rec.fb_lvl5_cd 
                     ||')'; 
          END IF; 
        END IF; 

        IF lv_rec.fb_lvl6_cd IS NOT NULL THEN 
          IF lv_rec.fb_lvl6_flag = v_incl_flag THEN 
            v_qry := v_qry 
                     ||' AND FB_LVL6_CD IN (' 
                     ||lv_rec.fb_lvl6_cd 
                     ||')'; 
          ELSE 
            v_qry := v_qry 
                     ||' AND FB_LVL6_CD NOT IN (' 
                     ||lv_rec.fb_lvl6_cd 
                     ||')'; 
          END IF; 
        END IF; 

        IF lv_rec.fb_lvl7_cd IS NOT NULL THEN 
          IF lv_rec.fb_lvl7_flag = v_incl_flag THEN 
            v_qry := v_qry 
                     ||' AND FB_LVL7_CD IN (' 
                     ||lv_rec.fb_lvl7_cd 
                     ||')'; 
          ELSE 
            v_qry := v_qry 
                     ||' AND FB_LVL7_CD NOT IN (' 
                     ||lv_rec.fb_lvl7_cd 
                     ||')'; 
          END IF; 
        END IF; 

        IF lv_rec.fb_lvl8_cd IS NOT NULL THEN 
          IF lv_rec.fb_lvl8_flag = v_incl_flag THEN 
            v_qry := v_qry 
                     ||' AND FB_LVL8_CD IN (' 
                     ||lv_rec.fb_lvl8_cd 
                     ||')'; 
          ELSE 
            v_qry := v_qry 
                     ||' AND FB_LVL8_CD NOT IN (' 
                     ||lv_rec.fb_lvl8_cd 
                     ||')'; 
          END IF; 
        END IF; 

        IF lv_rec.fb_lvl9_cd IS NOT NULL THEN 
          IF lv_rec.fb_lvl9_flag = v_incl_flag THEN 
            v_qry := v_qry 
                     ||' AND FB_LVL9_CD IN (' 
                     ||lv_rec.fb_lvl9_cd 
                     ||')'; 
          ELSE 
            v_qry := v_qry 
                     ||' AND FB_LVL9_CD NOT IN (' 
                     ||lv_rec.fb_lvl9_cd 
                     ||')'; 
          END IF; 
        END IF; 

        IF lv_rec.fb_lvl10_cd IS NOT NULL THEN 
          IF lv_rec.fb_lvl10_flag = v_incl_flag THEN 
            v_qry := v_qry 
                     ||' AND FB_LVL10_CD IN (' 
                     ||lv_rec.fb_lvl10_cd 
                     ||')'; 
          ELSE 
            v_qry := v_qry 
                     ||' AND FB_LVL10_CD NOT IN (' 
                     ||lv_rec.fb_lvl10_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;

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613787 is a reply to message #613784]

Mon, 12 May 2014 05:28

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

na.dharma@gmail.com wrote on Mon, 12 May 2014 15:49

Apologies for the incovienece as previously I have psted the wrong code..

Do you also see what I see :

Michel Cadot wrote on Mon, 12 May 2014 14:38

you refuse to follow the guide.

Lalit Kumar B wrote on Mon, 12 May 2014 14:56

The code is unreadable.

Lalit Kumar B wrote on Mon, 12 May 2014 15:44

Why don't you use code tags?

cookiemonster wrote on Mon, 12 May 2014 15:45

I know you know how to use code tags, so stop being lazy and use them.

Report message to a moderator

Re: How to handle null values in function having dynamic sql [message #613796 is a reply to message #613770]

Mon, 12 May 2014 06:56

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Now...it depends on what you want to do in case of NULL values in those record variables.

basically you just could "not add" the filter when you have a null value as the comparision you have choosen would not return a row anyway when added something like

AND FB_LVL2_CD IN (NULL)

so you would have to rewrite the logic if you wanted to compare with NULL into

AND FB_LVL2_CD IS NULL

so... you have to give more information to get a decent answer.

Report message to a moderator