Solved

Setting up a Query in Forms

Posted on 2004-07-30
11
2,040 Views
Last Modified: 2010-05-18
I have a fairly large select statement that I need to run after users have selected options from the pop-up lists (later I will make these LOVs).  When the user clicks on a "Search" button I want to run this query and show the results in a different tab.

My initial problem is that when you create a new datablock you can only base it on one table.  I need to base it on a couple of tables.  I guess I am just looking for some advice on how to start this.  Here is the SQL that I need to run.

SELECT DISTINCT A.ITEM_ID, B.ITEM_NBR,
B.DESCR, B.WEIGHT, B.LIST_PRICE,
B.UOM, B.PACK_QTY, B.BULK_QTY
FROM inv_master_attribute A
LEFT OUTER JOIN inv_master B ON
A.ITEM_ID = B.ITEM_ID  

WHERE A.ITEM_ID IN
(SELECT A.ITEM_ID FROM inv_master_attribute A WHERE
A.ATTRIBUTE_CAT_ID  = 1 AND A.ATTRIBUTE_VAL = '025')

AND A.ITEM_ID IN
(SELECT A.ITEM_ID FROM inv_master_attribute A WHERE
A.ATTRIBUTE_CAT_ID  = 4 AND A.ATTRIBUTE_VAL = '0100')

These last 2 can get added onto by all of the 12 pop-lists that users can use.  This shows a query that would get generated by the user picking 2 options.  First of all can I add something this long into the Default_Where clause, and second How should I go about creating a datablock - or something to hold and execute this?
0
Comment
Question by:Intern
11 Comments
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 11681289
*DO NOT* just toss in the keyword "distinct" in Oracle Queries!  I've observed that this seems to be a habit with people who have SQL Server experience, but in Oracle that keyword can cause many more problems than it solves.
1. It always forces asort operation that may not be needed - the performance penalty off this sort can be significant.
2. Oracle's understanding of "distinct" is usually different from mine, especially when multiple columns are being selected.  You may not get the results you expect (unless you've tested thoroughly) when you use the "distinct" operator in Oracle.

Do you really need the outer join? I confess that I'm not used to the SQL Server-style syntax you use (even though Oracle9 supports that now) but it looks to me like you could simply select from inv_master since all of the columns except one are from that table, then use a nested select to get the value from item_master_attribute if it exists, or a null if not, like this:
SELECT (select A.ITEM_ID from inv_master_attribute A where A.ITEM_ID = B.ITEM_ID),
B.ITEM_NBR, B.DESCR, B.WEIGHT, B.LIST_PRICE,
B.UOM, B.PACK_QTY, B.BULK_QTY
FROM inv_master B;

Usually I use views in Oracle to add extra filtering criteria or complexity, then base the forms blocks on the view.  If that doesn't do the job (because views don't accept parameters), it is possible these days to base a form block on a procedure that returns a ref cursof.  Either of those may be better options than to try to put lots of complexity into a default where clause.
0
 
LVL 2

Expert Comment

by:bcarlis
ID: 11681655
Intern, Are you still looking for the answer to your 1st and 2nd posting regarding the record_group and the PopupList - better known to me as a DDLB - Drop Down List Box???

I've got it if you are... I can't find the link..

Bill
0
 
LVL 1

Author Comment

by:Intern
ID: 11681881
I am still looking for the best way to create and run the sql statement and then display them in a block.  Here is a revised query, but I would like to know how to write this better to run faster on the database.  Right now it takes about 10 seconds in SQL Plus and that is not acceptable.  I think it is because I am using the "IN" keyword, and it is searching the table (8000 records) 3 times.  Can you help me to revise this and make it better?  Don't worry about getting skimped on points for helping me out.  I will post as many 500 point questions as I think are deserved for helping me.

SELECT ITEM_ID, ITEM_NBR
FROM inv_master
where ITEM_ID IN
(SELECT ITEM_ID FROM inv_master_attribute WHERE
ATTRIBUTE_CAT_ID  = 1 AND ATTRIBUTE_VAL = '025')
AND ITEM_ID IN
(SELECT ITEM_ID FROM inv_master_attribute WHERE
ATTRIBUTE_CAT_ID  = 4 AND ATTRIBUTE_VAL = '0100')
AND ITEM_ID IN
(SELECT ITEM_ID FROM inv_master_attribute WHERE
ATTRIBUTE_CAT_ID  = 7 AND ATTRIBUTE_VAL = 'Z')

0
 
LVL 2

Expert Comment

by:bcarlis
ID: 11682290
SELECT ITEM_ID, ITEM_NBR
FROM inv_master
where ITEM_ID IN
(SELECT ITEM_ID FROM inv_master_attribute
WHERE
(ATTRIBUTE_CAT_ID  = 4 AND ATTRIBUTE_VAL = '0100') OR
(ATTRIBUTE_CAT_ID  = 1 AND ATTRIBUTE_VAL = '025') OR
(ATTRIBUTE_CAT_ID  = 7 AND ATTRIBUTE_VAL = 'Z'));


Here is one try.....
0
 
LVL 2

Expert Comment

by:bcarlis
ID: 11682297
Here is another one...


SELECT M.ITEM_ID, M.ITEM_NBR
FROM inv_master M,inv_master_attribute MA
where M.ITEM_ID = MA.ITEM AND
((ATTRIBUTE_CAT_ID  = 4 AND ATTRIBUTE_VAL = '0100') OR
(ATTRIBUTE_CAT_ID  = 1 AND ATTRIBUTE_VAL = '025') OR
(ATTRIBUTE_CAT_ID  = 7 AND ATTRIBUTE_VAL = 'Z'));
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 2

Accepted Solution

by:
bcarlis earned 400 total points
ID: 11682316
One more....
SELECT ITEM_ID, ITEM_NBR
FROM inv_master
where ITEM_ID IN
((SELECT ITEM_ID FROM inv_master_attribute WHERE
ATTRIBUTE_CAT_ID  = 1 AND ATTRIBUTE_VAL = '025')
Union
(SELECT ITEM_ID FROM inv_master_attribute WHERE
ATTRIBUTE_CAT_ID  = 4 AND ATTRIBUTE_VAL = '0100')
Union
(SELECT ITEM_ID FROM inv_master_attribute WHERE
ATTRIBUTE_CAT_ID  = 7 AND ATTRIBUTE_VAL = 'Z') );

Do a :
select to_char(sysdate,'hh:mi:ss') from dual;

before and after each one to time them...


NEXT STEP would be to find each of the two tables INDEX and have the first column in the WHERE statement accessing that INDEX column IF POSSIBLE....??????


Bill :>)
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11683537
use union all if you are looking to improve performance
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11683551
consider making inv_master_attribute an index organised table.
0
 
LVL 8

Expert Comment

by:sapnam
ID: 11683577
All above posts will help you in the query front.  After the query is done, you have to display the results in an Oracle Form.  For that you will need a data block and display the results to the user.

Your original post had this line -- My initial problem is that when you create a new datablock you can only base it on one table.  I need to base it on a couple of tables.  I guess I am just looking for some advice on how to start this.

Well, you cannot have more than one base table for a block.  But then you need not have a block with an underling base table to display results unless you want to save the displayed results in another table.

If you just want to display results of the query to the users, create a block without a base table underlying.  This can be a multi-record display block.  
Then after the user selects his options and you want to display the results, code something like this

DECLARE cursor c1 is  -- your query like
SELECT DISTINCT A.ITEM_ID, B.ITEM_NBR,
B.DESCR, B.WEIGHT, B.LIST_PRICE,
B.UOM, B.PACK_QTY, B.BULK_QTY
FROM inv_master_attribute A
LEFT OUTER JOIN inv_master B ON
A.ITEM_ID = B.ITEM_ID  ........;
BEGIN
   GO_BLOCK('your block name');
   CLEAR_BLOCK(NO_VALIDATE);
   FOR rec in c1
      LOOP
         :item_id := rec.item_id;
         -- similarly for all other fields
         CREATE_RECORD;
     END LOOP;
   DELETE_RECORD; -- to delete the last extra record
END;
/

This will display the results in that block.


0
 
LVL 13

Expert Comment

by:riazpk
ID: 11683613
OR

create view vw_test
as
SELECT DISTINCT A.ITEM_ID, B.ITEM_NBR,
B.DESCR, B.WEIGHT, B.LIST_PRICE,
B.UOM, B.PACK_QTY, B.BULK_QTY
FROM inv_master_attribute A
LEFT OUTER JOIN inv_master B ON
A.ITEM_ID = B.ITEM_ID  
/

Build a block based on this view. And then depending upon the choices made by user, do:

set_block_property('BlockName',default_where,'WHERE ITEM_ID IN (SELECT ITEM_ID FROM inv_master_attribute A WHERE ATTRIBUTE_CAT_ID  = 1 AND ATTRIBUTE_VAL = 25) );
and so on ....
0
 
LVL 5

Expert Comment

by:gmyers
ID: 11690146
If you are not doing inserts/updates, you can base a block on a from clause, using the QUERY DATA SOURCE TYPE
That can contain a select statement referencing multiple tables
EG
select emp.*, dept.* from emp, dept where emp.dept_no = dept.dept_no

On Performance, the design strategy you've chosen for the tables is not very helpful.
With a query like
"select * from item where colour='red' and weight = 50 and size = 100"
Oracle can use column statistics to say whether using an index on size, weight and/or colour would be useful. If one in a thousand had a size of 100 and one in twenty had a weight of 50, it would use the size index first to get the most cut-down result list to apply the other criteria to.
Your approach of ATTRIBUTE_CAT_ID and ATTRIBUTE_VAL hides useful information from Oracle so it doesn't know which is the most selective part of the query to perform first.
If you HAVE to go that route, one way around that may be the use of a function based index to combine the CAT_ID and VAL into a single value.
Something like CREATE INDEX x ON inv_master_attribute (lpad(to_char(attribute_cat_id),'0',3)||lpad(attribute_val,'0',10))
Then, after collecting histograms on the indexed columns, you would query
AND A.ITEM_ID IN
(SELECT A.ITEM_ID FROM inv_master_attribute A WHERE
(lpad(to_char(attribute_cat_id),'0',3)||lpad(attribute_val,'0',10)) = (lpad(to_char(4),'0',3)||lpad('0100','0',10)))

With histograms, Oracle MAY then be able to choose the most selective criteria to apply first. I emphasise MAY, because we then get into the area of cursor sharing.
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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 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