default_where clause in oracle forms

Can I use this concept to set the block property?

I am using debug message but not able to execute. No data is being retrieved. Jus a confirmation.

SET_BLOCK_PROPERTY('qc_poch_again',default_where,
                  'term_date is null and EQUIP_TYPE_ID = v_equip_type_id
                     and qc_type_id = :qc_daily.qc_type_id
                     AND qc_subtype_id = :qc_daily.qc_subtype_id
                     and procedure_id in( select procedure_id
                                      from qc_procedures
                                     where equip_type_id = 5015
                                       and qc_subtype_id = v_qc_subtype_id
                                     minus  
                                    select distinct procedure_id
                                      from qc_procedure_results
                                     where equip_type_id = 5015
                                       and qc_subtype_id = v_qc_subtype_id
                                       and drive_id =  v_drive_id');

select procedure_id
from qc_procedures
where equip_type_id = 5015
and qc_subtype_id = 1006
minus  
select distinct procedure_id
from qc_procedure_results
where equip_type_id = 5015
and qc_subtype_id = 1006
and drive_id =  'DRV5541380'

PROCEDURE_ID
1104
1127
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
Sure, why not?! Where's the point/your problem (if any at all)?!
0
anumosesAuthor Commented:
I am not getting any data when I execute this. I should get 2 line with procedure id 1104 and 1127.
0
anumosesAuthor Commented:
Sample Data

 

 

CREATE TABLE TAB1

(

  PROCEDURE_ID      NUMBER(10),

  PROCEDURE_DESC    VARCHAR2(80 BYTE),

  TERM_DATE         DATE,

  ASSOC_WITH_EQUIP  VARCHAR2(1 BYTE),

  OPTIONAL          VARCHAR2(1 BYTE),

  FREQUENCY         NUMBER,

  EQUIP_TYPE_ID     NUMBER(10),

  QC_TYPE_ID        NUMBER(10),

  QC_SUBTYPE_ID     NUMBER(10)

)

 

 

 

Insert into TAB1

   (PROCEDURE_ID, PROCEDURE_DESC, TERM_DATE, ASSOC_WITH_EQUIP, OPTIONAL,

    FREQUENCY, EQUIP_TYPE_ID, QC_TYPE_ID, QC_SUBTYPE_ID)

Values

   (1127, 'Daily Start Up LOW Controls', NULL, 'Y', 'N',

    1, 5015, 1011, 1006);

Insert into TAB1

   (PROCEDURE_ID, PROCEDURE_DESC, TERM_DATE, ASSOC_WITH_EQUIP, OPTIONAL,

    FREQUENCY, EQUIP_TYPE_ID, QC_TYPE_ID, QC_SUBTYPE_ID)

Values

   (1103, 'Daily Startup Background Counts', NULL, 'Y', 'N',

    1, 5015, 1011, 1006);

Insert into TAB1

   (PROCEDURE_ID, PROCEDURE_DESC, TERM_DATE, ASSOC_WITH_EQUIP, OPTIONAL,

    FREQUENCY, EQUIP_TYPE_ID, QC_TYPE_ID, QC_SUBTYPE_ID)

Values

   (1104, 'Daily Start Up HI Control', NULL, 'Y', 'N',

    1, 5015, 1011, 1006);

COMMIT;

 

----------------------------

 

 

CREATE TABLE TAB2

(

  DEPT_ID        NUMBER(10),

  QC_TYPE_ID     NUMBER(10),

  QC_SUBTYPE_ID  NUMBER(10),

  DRIVE_ID       VARCHAR2(10 BYTE),

  PROCEDURE_ID   NUMBER(10),

  EQUIP_CODE     VARCHAR2(10 BYTE),

  EQUIP_TYPE_ID  NUMBER(10),

  FREQUENCY      NUMBER

)

 

 

 

Insert into TAB2

   (DEPT_ID, QC_TYPE_ID, QC_SUBTYPE_ID, DRIVE_ID, PROCEDURE_ID,

    EQUIP_CODE, EQUIP_TYPE_ID, FREQUENCY)

Values

   (10, 1011, 1006, 'DRV5541380', 1103,

    'CELLPOC018', 5015, 1);

COMMIT;

 

 

select PROCEDURE_ID

from tab1

where equip_type_id = 5015

and qc_subtype_id = 1006

minus 

select distinct PROCEDURE_ID

from tab2

where equip_type_id = 5015

and qc_subtype_id = 1006

and drive_id =  'DRV5541380'

 

Is there a possibility to use the default_where as below. If not what is the best way to use it.

 

SET_BLOCK_PROPERTY('qc_poch_again',default_where,

                'term_date is null and EQUIP_TYPE_ID = 5015

                   and qc_type_id = 1011

                   AND qc_subtype_id = 1006

                   and procedure_id in( select procedure_id

                                      from qc_procedures

                                     where equip_type_id = 5015

                                       and qc_subtype_id = 1006

                                     minus  

                                    select distinct procedure_id

                                      from qc_procedure_results

                                     where equip_type_id = 5015

                                       and qc_subtype_id = 1006

                                       and drive_id =  'DRV5541380' ');

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

anumosesAuthor Commented:
Based on the example I even tried this but does not work. Any help is appreciated.

http://talk2gerd.blogspot.com/2007/07/concatenate-big-default-where-clauses.html

Declare

V_Default_Where VARCHAR2 (4000);
v_poch_procedure_id number;

Cursor C1 is
select procedure_id
from qc_procedures
where equip_type_id = 5015
and qc_subtype_id = :qc_daily.qc_subtype_id
minus  
select distinct procedure_id
from qc_procedure_results
where equip_type_id = 5015
and qc_subtype_id = :qc_daily.qc_subtype_id
and drive_id =  :qc_daily.drive_id;

Begin

Open C1;
Fetch C1 into v_poch_procedure_id;
Close C1;

V_Default_Where := 'procedure_id in (v_poch_procedure_id)';
                        
 V_Default_Where := V_Default_Where ||
                         ' and term_date is null
                           and EQUIP_TYPE_ID = v_equip_type_id
                               and qc_type_id = :qc_daily.qc_type_id
                               AND qc_subtype_id = :qc_daily.qc_subtype_id';                         
 
           SET_BLOCK_PROPERTY('qc_poch_again',default_where,V_Default_Where);

End;
0
flow01Commented:
Are  v_equip_type_id ,  v_qc_subtype_id and  v_drive_id   variables of the procedure where the SET_BLOCK_PROPERTY is in ?
then at execution time the where clause has no access to that variables
You can either resolve them
 EQUIP_TYPE_ID = v_equip_type_id  >   EQUIP_TYPE_ID = ' || v_equip_type_id || '
or (better) turn them to (bind)variables that can be resolved at execution time
either by defining a controlblock with the variables as items
:control.equip_type_id :=  v_equip_type_id;
SET_BLOCK_PROPERTY(...
EQUIP_TYPE_ID = :control.equip_type_id
... etc
or by defining the variables as globals
:global. equip_type_id :=  v_equip_type_id;
SET_BLOCK_PROPERTY(...
EQUIP_TYPE_ID = :global.equip_type_id
... etc
ERASE('global.equip_type_id');
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
When I put messages, all the variables are being populated. But in the end data does not get populated.
0
Mark GeerlingsDatabase AdministratorCommented:
I think the problem is your use of variables, for example in this line: 'term_date is null and EQUIP_TYPE_ID = v_equip_type_id

That last element: "v_equip_type_id" looks like a problem to me.  How will Oracle Forms know how to resolve that variable?
0
flow01Commented:
You did not answer my question:
Are  v_equip_type_id ,  v_qc_subtype_id and  v_drive_id   variables of the procedure where the SET_BLOCK_PROPERTY is in ?
And a second one
Are the data not getting populated because the query can't execute or are there no records found.
One way of checking is to do a count_query in stead of an execute query.
If the query executes you will get 0 records founds, if it doesn't you won't get that either.


Here is a pl/sql example of an analog  situation  I assume is in your form.
declare
  v1 number ;
  v2 number ;
begin
  v1 := 3;
  dbms_output.put_line(v1);
  begin
  execute immediate 'select 1 from dual where v1 = 3' into v2;
  exception when others then
    null;
  end;
  dbms_output.put_line(v2 || ' records found');
end;

and this is the example that works
declare
  v1 number ;
  v2 number ;
begin
  v1 := 3;
  dbms_output.put_line(v1);
  begin
  execute immediate 'select 1 from dual where :p1 = 3' into v2 using v1 ;
  exception when others then
    null;
  end;
  dbms_output.put_line(v2 || ' records found');
end;
/
0
anumosesAuthor Commented:
Did not really help. But have to close this question.
0
Mark GeerlingsDatabase AdministratorCommented:
Did you try setting a static "where_clause" property for a test to confirm that the block can retrieve the records that should be retrieved that way?
0
flow01Commented:
Unless you don't want put more effort in finding a solution yourself, why closing the question if it does not help ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.