Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

default_where clause in oracle forms

Posted on 2014-09-22
11
Medium Priority
?
2,366 Views
Last Modified: 2014-10-07
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
0
Comment
Question by:anumoses
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40336649
Sure, why not?! Where's the point/your problem (if any at all)?!
0
 
LVL 6

Author Comment

by:anumoses
ID: 40336664
I am not getting any data when I execute this. I should get 2 line with procedure id 1104 and 1127.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40336666
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 6

Author Comment

by:anumoses
ID: 40336833
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
 
LVL 21

Accepted Solution

by:
flow01 earned 999 total points
ID: 40337111
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
 
LVL 6

Author Comment

by:anumoses
ID: 40337252
When I put messages, all the variables are being populated. But in the end data does not get populated.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 501 total points
ID: 40337745
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
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 999 total points
ID: 40337793
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40366979
Did not really help. But have to close this question.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40367135
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
 
LVL 21

Expert Comment

by:flow01
ID: 40367215
Unless you don't want put more effort in finding a solution yourself, why closing the question if it does not help ?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question