Re: Insert Statement

artmerar_at_gmail.com wrote:

: Can you use a CASE statement in an INSERT clause with Boolean
: expressions? Something like

: DECLARE
: b boolean := TRUE;
: BEGIN
: INSERT INTO a
: VALUES (
: CASE
: WHEN b = TRUE THEN 'AB'
: WHEN b = FALSE THEN 'CD'
: END);
: END;
: /

ALSO, if that was boolean then simply

	when b then 'AB
	when not b then 'CD'

and in fact in this case it could be

	when b then 'AB'
	else 'CD'

though as pointed out elsewhere, booleans cant be used in SQL so neither would actually work. You might think to try to sneak em in, so something like WHEN BOOL_TO_YN(b)='Y' THEN 'AB' , but that wont work either cause the b is still seen at the level of the SQL.

Why not just set a bind variable that can be used in sql?

DECLARE
   b boolean := TRUE;
   /* private */
   function YN(b bool) return varchar2 is begin if b then return 'Y' etc... BEGIN    DECLARE TEMP := yn(b);
   BEGIN
      INSERT INTO A values ( case when TEMP='Y' then etc...    END;
END; or turn it on its head and use an SQL compatible variable instead of a boolean, and create a YN_TO_BOOL function if you really want to use a boolean in pl/sql

DECLARE
   YN varchar2(1) := 'Y';
   /* private */
   function maybe(YN varchar2)
   return boolean is begin if YN='Y' then return true etc...

BEGIN

• in PL/SQL code, if you don't want to test YN='Y', then use if maybe(YN) then do_something; else ... etc ...
• in SQL use it directly INSERT INTO A values ( case when YN='Y' then etc... END;