Solved

default_where clause in oracle forms

Posted on 2014-09-22
11
952 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 13

Expert Comment

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

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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 20

Accepted Solution

by:
flow01 earned 333 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

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

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 167 total points
Comment Utility
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 20

Assisted Solution

by:flow01
flow01 earned 333 total points
Comment Utility
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
Comment Utility
Did not really help. But have to close this question.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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 20

Expert Comment

by:flow01
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to tune this sql query 61 100
Add 0 to end of Number 21 71
oracle query 15 62
Dataware house query tuning 9 29
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now