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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
flow01Connect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
anumosesAuthor Commented:
When I put messages, all the variables are being populated. But in the end data does not get populated.
0
 
Mark GeerlingsConnect With a Mentor Database 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
 
flow01Connect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.