CODEWITHSUNDEEP
sqloracle-database
SASPYTHON
SASPYTHON

Reputation: 1621

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

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

Upvotes: 1

Views: 61

Answers (2)

Aleksej
Aleksej

Reputation: 22959

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...

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You can use ||CHR(39)

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

Upvotes: 0

Related Questions