OraFAQ Forum: Forms » Set_Block

Home » Developer & Programmer » Forms » Set_Block_Property (Oracle Form 10g)

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

Set_Block_Property [message #577790]

Wed, 20 February 2013 11:12

mabblerrs
Messages: 4
Registered: February 2013

Junior Member

Hello all,

Have you ever had to add exists to this property?
Is this possible?

IF (:VRAN.INSERT_DT IS NOT NULL) THEN

my_where := my_where || ' exists in ( select ''X'',';
my_where := my_where || ' from MON_APL a, mon_hist h ';
my_where := my_where || ' where 1=1 ';
my_where := my_where || ' and a.id = h.mon_id ';
my_where := my_where || ' and h.hist_id = (select max(hist_id) ';
my_where := my_where || ' from mon_hist ';
my_where := my_where || ' where mon_id = a.id ) ';
my_where := my_where || ' and ( NULL is NULL or ';
my_where := my_where || ' exists ( select ''x'' ';
my_where := my_where || ' from mon_hist h ';
my_where := my_where || ' where h.mon_id = a.id ';
my_where := my_where || ' and status_cd = ''CE'' ';
my_where := my_where || ' and action_cd in (''EC'',''ER'') ';
my_where := my_where || ' and ( trunc(h.insert_dt) >= to_date('||:VRAN.INSERT_DT||',''DD-MON-RR'') and ';
my_where := my_where || ' trunc( h.insert_dt) < to_date('||:VRAN.INSERT_DT||',''DD-MON-RR'') +1 ))) ';

/* */ 

END IF;
set_block_property (find_block ('VRAN'), DEFAULT_WHERE, my_where);

So far I haven't been able to get any combination of this working other than what was there:

my_where := my_where || ' INSERT_DT between ';
my_where := my_where || 'TO_DATE (''';
my_where := my_where || :VRAN.INSERT_DT;
my_where := my_where || ''', ''MM/DD/YYYY'')';
my_where := my_where || ' and TO_DATE (''' || :VRAN.INSERT_DT || ''', ''MM/DD/YYYY'') + 1';

Thanks,
Mike

[EDITED by LF: applied [code] tags]

[Updated on: Wed, 20 February 2013 12:45] by Moderator

Report message to a moderator

Re: Set_Block_Property [message #577795 is a reply to message #577790]

Wed, 20 February 2013 12:48

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Did you try to display MY_WHERE on the screen? If not, do so. Then copy the output and put it into SQL*Plus SELECT statement and see what happens. If it runs OK, then - I belive - it would work in a form as well. If not, it'll tell you what mistake you made.

For example:

exists in ( select ''X'','

doesn't seem to be correct:

SQL> select * from emp
  2  where exists in (select 'x' from dual);
where exists in (select 'x' from dual)
             *
ERROR at line 2:
ORA-00906: missing left parenthesis

Report message to a moderator

Re: Set_Block_Property [message #577796 is a reply to message #577790]

Wed, 20 February 2013 12:48

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

mabblerrs wrote on Wed, 20 February 2013 12:12

Hello all,

Have you ever had to add exists to this property?

my_where := my_where || ' and ( NULL is NULL or ';

What is the purpose of that line?
Why not make a single concatenated variable rather than 20 or so concatnations?
Why don't you do a DISPLAY to see what the final clause really is?
EXISTS is no different than any other valid SQL syntax.

[Updated on: Wed, 20 February 2013 12:49]

Report message to a moderator

Re: Set_Block_Property [message #577799 is a reply to message #577796]

Wed, 20 February 2013 12:59

mabblerrs
Messages: 4
Registered: February 2013

Junior Member

Thanks,

I saw that after I sent it.

my_where := my_where || ' 1-1 and exists in ( select ''X''';
my_where := my_where || ' from MONUMENT_APL a, monapl_history h ';
my_where := my_where || ' where 1=1 ';
my_where := my_where || ' and a.id = h.monapl_id ';
my_where := my_where || ' and h.hist_id = (select max(hist_id) ';
my_where := my_where || ' from monapl_history ';
my_where := my_where || ' where monapl_id = a.id ) ';
my_where := my_where || ' and '''||:VETERAN.INSERT_DT||''' is NULL or ';
my_where := my_where || ' exists ( select ''x'' ';
my_where := my_where || ' from monapl_history h ';
my_where := my_where || ' where h.monapl_id = a.id ';
my_where := my_where || ' and status_cd = ''CE'' ';
my_where := my_where || ' and action_cd in (''EC'',''ER'') ';
my_where := my_where || ' and ( trunc(h.insert_dt) >= to_date('''||:VETERAN.INSERT_DT||''',''MM/DD/YYYY'') ';
my_where := my_where || ' and trunc( h.insert_dt) < to_date('''||:VETERAN.INSERT_DT||''',''MM/DD/YYYY'') +1 ))) ';

Let me remove the 'IN'

Thanks,
Mike

Report message to a moderator