[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2110
  • Last Modified:

Setting up a Query in Forms

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
Intern
Asked:
Intern
2 Solutions
 
Mark GeerlingsDatabase AdministratorCommented:
*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
 
bcarlisCommented:
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
 
InternAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bcarlisCommented:
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
 
bcarlisCommented:
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
 
bcarlisCommented:
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
 
earth man2Commented:
use union all if you are looking to improve performance
0
 
earth man2Commented:
consider making inv_master_attribute an index organised table.
0
 
sapnamCommented:
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
 
riazpkCommented:
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
 
gmyersCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now