Dynamic SQL, how to code ' ' to dynamic SQL?

Question

Good Morning

select CASE t.gift_club_start_date
       WHEN ' '  THEN ' '
       ELSE SUBSTR(t.gift_club_start_date,5,2)|| '/' ||SUBSTR(t.gift_club_start_date,7,2)|| '/' ||SUBSTR(t.gift_club_start_date,1,4) 
       END
from GIFT t

This shows if rows is empty, it shows empty.

I try to do this code with Dynamic SQL

'select CASE t.gift_club_start_date
       WHEN '' ''  THEN '' ''
       ELSE SUBSTR(t.gift_club_start_date,5,2)|| ''/'' ||SUBSTR(t.gift_club_start_date,7,2)|| ''/'' ||SUBSTR(t.gift_club_start_date,1,4) 
       END
from GIFT t'

BUT output shows this // ,when row is empty.

So how can I code correctly to show empty row? This line WHEN '' '' THEN '' ''

Thank you so mcuh

Aleksej · Accepted Answer · 2016-10-18 14:11:11Z

2

Basically you can use double ' to solve:

SQL> declare
  2      vSQL varchar2(100);
  3      vVar varchar2(100);
  4  begin
  5      vSQL := 'select '' '' from dual';
  6      execute immediate vSQL into vVar;
  7      dbms_output.put_line('result:<' || vVar || '>');
  8  end;
  9  /
result:< >

Another way could be the following:

SQL> declare
  2      vSQL varchar2(100);
  3      vVar varchar2(100);
  4  begin
  5      vSQL := q'[select ' ' from dual]';
  6      execute immediate vSQL into vVar;
  7      dbms_output.put_line('result:<' || vVar || '>');
  8  end;
  9  /
result:< >

For your situation, notice that "empty" is different from ' '; if you need to check for null values, you should use something like

WHEN t.gift_club_start_date is NULL THEN...

answered Oct 18, 2016 at 14:11

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2016-10-18 14:10:59Z

0

You can use ||CHR(39)

'WHEN ' || CHR(39) || CHR(39) || ' THEN ' || CHR(39) || ' ' ||CHR(39)

answered Oct 18, 2016 at 14:10

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL, how to code ' ' to dynamic SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related