SQL & PL/SQL :: Multiple Filter Option

Apr 2, 2013

in many shopping cart applications they are providing multiple filter option.

suppose filter by brand,fitler by price ranges,filter by color,filter by shape etc..

so how they are achieving the performance.because lot of filter if applied then it should get slow execution.

how to achieve this.I tried as follows.

alter session set nls_date_format = 'dd-MON-yy';

CREATE OR replace PROCEDURE Multiple_filter (p_empno VARCHAR2,
p_ename VARCHAR2,
p_from_hiredate DATE,
p_to_hiredate DATE)

[Code]....

View 18 Replies


ADVERTISEMENT

Export/Import/SQL Loader :: Expdp / Query Option For Exporting From Multiple Tables With Same Condition?

Sep 3, 2012

export a subset of the data only from one database to another. Both on AIX.

Source/testdatabase 11.2.0.3 (non-partitioned tables)
Target productiion database 11.2.0.3 (partioned tables)

Tables same column names but diffrenet index structures and traget one to be partitioned hence only want to import the content Each table on source datbaase hascolumn seq number and only want to extract the last few months of data.

TABLES:table1,table2...
DUMPFILE=dump_dir
CONTENT=data_only
QUERY= table1:"WHERE seq_num >100 "want to use expdp but not sure about how to ensure all tables have the WHERE seq_num >100 condition, if leave table1: out and just have
QUERY= "WHERE seq_num >100 " will this condition be applied to all tables which is what we want.

I'm assuming also can use impdp CONTENT=data_only?

View 3 Replies View Related

SQL & PL/SQL :: Filter Data In One Row?

Jul 27, 2012

I've got one Table content

DataID...............Name
123...................test.doc
345...................test1.doc
678...................test2.doc
979...................test1.pdf

What I try is the following....

I go though the table and search for each *.doc the correct *.pdf.

the result should be this:

DataIDDoc...............NameDoc......DataIDPDF...............NameDocPDF......

123...................test.doc........979...................test1.pdf

Entries should never be twice in the table..

View 6 Replies View Related

Data Filter Within Table?

Sep 5, 2013

I am not sure if Oracle has a simple solution for a problem that I have in retrieving data?

Conditions:

As per rule we have to hold data from 7 - 10 years so the data can be searched at any given time.Don't want to purge data and keep the historic data separate so the request can be redirected based on the year.

I am looking for a solution similar to windows .ocx search. When a application is requesting an ocx (active X control) the search happens in C;\windows\system32 and if the ocx doesn't exists then the search is done on the entire system. Similarly can a table be partitioned (just the term I am using not discussing Oracle partition here) so I can partition data from 2011,2012 and 2013 to search first and if the searched data doesn't exists then search the other partition (data from 2003-2010) ?

View 2 Replies View Related

SQL & PL/SQL :: Filter Special Characters?

Jan 8, 2013

I have one column name party_name containing Korean Characters and English characters.Some of the English characters have different symbols.My requirement is to get the data and exclude those symbols but not Korean characters.

Already I used a function to replace special symbols with space.The function contains code based on ASCII values it works good but it filters Korean characters too.the attachment of the screenshot, When I double click the name it shows with some question mark.

View 9 Replies View Related

Forms :: 10g And Filter Data With SSO ID?

Oct 14, 2010

I'm having difficulty trying to show a list of forms that I would like to present the user after they log on with SSO.

The funny thing is that I can show the user in a message box. However, I cannot filter records that the user should have on an "Application" menu. The application menu is designed to allow the user to launch the form by double clicking on the menu item. That works fine. It is just that I cannot seem to filter. It shows no records.

Here is my WNFI

DECLARE
sso_user varchar2(40);
nn number;
v_vis VARCHAR2(20);

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Filter In Xplan Not In Query

Feb 9, 2011

This is probably a priv/similar issue but I've not seen it before.

select * from ccs.paymentmethod;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 2424632210
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19791 | 2589K| 39 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| PAYMENTMETHOD | 19791 | 2589K| 39 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(1=2)

I get no rows - why? Obviously the predicate 1=2. Except, equally obviously, I don't have that in the query. On checking the objects views I see that ccs.payment method is indeed a table within that schema not a view or anything like that and when checking through another user I can see it has data within.

The "problem" user can see the table exists, just cant ever query rows back. Another user can see the table exists and can get data back.

Like I say, likely a priv or similar issue but I'm damned if I've seen this before, I'd not expect a priv issue to cause a 1=2 filter appear in a plan and its not a view that I can tell.

I'm still digging at my end and this may be irrelevant but when I browse to the table in Sql Developer I can see the columns tab ok, on clicking the "data" tab it throws "ORA-00904: "ORA_ROWSCN": invalid identifier" out. Maybe nothing, maybe related.

Google results suggest that error in SQL Dev is due to implementations of policies/VPD presence on the table.

View 2 Replies View Related

PL/SQL :: Filter Values From String

Oct 13, 2013

I have data something like this:

Sample DateWITH DATA AS          (          SELECT 'AAAXXXX IO BLUEEXPRESS' LIST FROM dual          UNION ALL          SELECT 'BLUEEXPRESS AAAXXXX IO BLUEEXPRESS'  FROM dual          UNION ALL          SELECT 'DDDDD BLUEEXPRESS AAAXXXX'  FROM dual          UNION ALL          SELECT 'DDDDD DDDDD AAAXXXX'  FROM dual          UNION ALL          SELECT 'DDDDD BLUEEXPRESS AAAXXXX NO CARBON'  FROM dual          UNION ALL          SELECT 'NO CARBON
[code]....

The above result depends on the following rules:         

- Replace BLUEEXPRESS into BEXPRESS         
- Remove the term NO CARBON (See row no 6)         
- Reduce all multiple space into single space (see last record).

So far I create separated queries for replacing BLUEEXPRESS into BEXPRESS and replace NOCARBON term but I don't know how to do it in a single shot as well as stuck on scenario to remove multiple spaces  and put single space. 

View 5 Replies View Related

SQL & PL/SQL :: Filter The System Grants?

Jun 3, 2010

As per my req, i need to get the system grants of one user (GRANTEE) using DBMS_OUTPUT API. The requirement get completed using 'SYSTEM_GRANT' as parameter for OPEN function in the metadata api. Please look into part of code which works.

v_meta_handle := DBMS_METADATA.OPEN('SYSTEM_GRANT');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'GRANTEE','SCOTT');

When using above piece, i get sys grants granted to SCOTT user. But i need to use 'DATABASE_EXPORT' as a parameter to my 'OPEN' function.

v_meta_handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'INCLUDE_PATH_EXPR','IN''SYSTEM_GRANT''');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'NAME_EXPR','IN''SCOTT''','GRANTEE_EXPR');

The second set filter doesn't work to get SYS GRANTS of one user. It does not throw any error. Simply the filter doesn't work. 'GRANTEE_EXPR' is not a correct value in the 2nd set filter. What parameter need to pass in object_path_expr ('GRANTEE_EXPR')?

View 4 Replies View Related

INSO Filter Language Support?

May 4, 2011

Is it possible to extract kannada text from a pdf document using INSO filter ?

Or Does it support only English Language ?

View 5 Replies View Related

Forms :: Filter The Records Displayed

May 11, 2010

I have a block in the form which is based of a table as data source and so when i query on the form using a execute_query inbuilt statement is fired and all the records in the table behind is retrived and displayed. Also if i need to update any record i can do in on the screen and use commit_form so that all the changes go into the underlying table. Now my problem is when i retrieving my records i want to filter those records based on some conditions to be displayed in the form and not all records to be retrieved. Is it possible to do it if I am using the execute_query inbuilt and my block is based of a table?

View 8 Replies View Related

PL/SQL :: Filter Ticket Prices According To Class?

Sep 24, 2013

I need to filter ticket prices according to the class; i.e. 

CASE WHEN booking_class = 'Economy'           THEN economy_saver ELSE 0 END AS ECONOMY_SAVER           THEN economy_basic ELSE 0 END AS ECONOMY_BASIC           THEN economy_basic_plus ELSE 0 END AS ECONOMY_BASIC_PLUSEND, CASE WHEN booking_class = 'Business'           THEN business ELSE 0 END AS BUSINESS           THEN business_flexible ELSE 0 END AS BUSINESS_FLEXIBLEEND.

..The objective is to show only the prices that belong to the selected class. I am not sure if my sql is correct at this point. 

View 7 Replies View Related

SQL & PL/SQL :: Query Based On Date Filter Is Not Working

Aug 14, 2011

is the definition of my table :

CREATE TABLE DATEFETC
(
ID VARCHAR2(10 BYTE),
DT DATE
)

And these are the data that are available,(select * from DATEFETC)

IDDT

00108-09-2011
00208-10-2011
00308-11-2011

That's fine.
Now i am executing this query ,but this is returning no rows.Why ?

select * from datefetc where dt between to_date('08-08-2011','mm-dd-yyyy') and to_date('08-12-2011','mm-dd-yyyy')

View 1 Replies View Related

SQL & PL/SQL :: Filter Data Using CASE Statement In WHERE Clause?

May 29, 2013

Want to filter a data using CASE statement in WHERE clause for the following scenario.

Need to Filter tb1.fallback_keyword if the fallback_flag is "Y' or 'N' and pg_number is null.Else no partial search of keyword.

where CASE WHEN (fallback_flg = 'Y' OR fallback_flg = 'N') and (pg_number is NULL )
THEN tb1.fallback_keyword = SUBSTR(key_word,1, INSTR(key_word,'#',-2))
ELSE (tb1.keyword = key_word ) AND (tb1.keyword like regexp_replace(key_word, '[*]+', '%'))
END

View 3 Replies View Related

SQL & PL/SQL :: Filter Each Row In A Select Statement Based Upon A List?

May 18, 2011

Is it possible to filter each row in a select statement based upon a list that you loop through. For instance in a where clause having an in statement but rather than selecting any value in the in statement looping the main select through each value in the "in" filter.

Eg:

CREATE TABLE TRANSAC(
Item_id number ,
Transaction_Date DATE,
Category_id number
)

You could then write

SELECT MAX(Transaction_Date)
from TRANSAC
where category_id = '141'

and then where category_id = '142' then '143' etc.

I understand i could use an inline view instead of a where clause but the issue is that i already have the list of values I want to compare against.

View 5 Replies View Related

Query To Exclude All Rows Based On Single Row Filter?

Jun 10, 2011

I'm using Oracle 10g.

Question: How can I write query to return just ID only if all the codes for that ID end in 6.

If I use
SQLselect id from table_a where code like '%6'
then I also get ID=1, which I don't want.

TABLE_A
ID code
=======
1 100
1 106
2 206
3 306
3 336
4 400

Desired Result
ID
==
2
3

View 1 Replies View Related

Application Express :: Filter Shuttle Using Dynamic Actions

Sep 7, 2012

I am using Apex 4.1 and builder theme.I came across this thread to filter the shuttle values based on javascript in dynamic action.Auto filter Shuttle selection list

I created a shuttle and a textbox as described but the dynamic action is not clear. And It is not working for me. The steps are not so clear and i guess the javascript is not complete.

View 2 Replies View Related

Application Express :: Filter SQL Statement With Value From Session State

Feb 27, 2013

I need to filter a SQL statement with an application item value retrieved from session state. I am assuming I could do this using a Dynamic Action on a Page Load event but I'm not sure where to go from here. How could I append the filter to an existing report page SQL statement?

View 7 Replies View Related

Application Express :: Filter In Interactive Report Is Not Working

Dec 18, 2012

I am facing very strange situation. In one of my Interactive report, Filter option is not working (Processing symbol is appearing on top of the page, but nothing happens.). Is there any setting or something like that?

View 1 Replies View Related

Forms :: How To Make A Date Field Filter In Descending Order

Aug 5, 2011

I have a question regarding a Date field on one of my form.

How do I make a Date field filter in a descending order when the form is opened? Also, can I add a 'when-mouse-doubleclick' trigger to sort the date field in ascending and descending order (this is a client requirement)?

View 2 Replies View Related

Semantic Technologies :: User-defined Function In FILTER Clause?

Apr 28, 2013

can i create the user-defined functions and use them in the FILTER clause in the sem_match function? there are some built-in functions for the FILTER clasue. however, only one function (DATATYPE(literal)) support for date/time in the built-in functions. i want to implement some user-defined funcitons in the FILTER clause which can check time intervals in ontology. there are some functions about valid time in the WorkSpace Manager such as WM_OVERLAPS, WM_CONTAINS,WM_MEETS, etc. so, can i write some functions using the these valid time functions in WM and use them in the FILTER clause?

View 2 Replies View Related

Forms :: Filter Records In A Data Block Based On Yes Or No Criteria

Aug 13, 2010

I need to filter records in a datablock based on Yes or No criteria.So i created a listitem(INCLUDE_ZERO_QTY_ITEMS) for that and i am using following code in WHEN-LIST-CHANGED trigger.

BEGIN
IF :XGEC_SW_ITEM_LOAD_ITEM_DETS_V.INCLUDE_ZERO_QTY_ITEMS = 'Y'
THEN
GO_BLOCK ('XGEC_SW_ITEM_LOAD_ITEM_DETS_V');
SET_BLOCK_PROPERTY ('XGEC_SW_ITEM_LOAD_ITEM_DETS_V',
default_where,
'1=1');
[code]....

I am able to filter the records but the list item is not displaying initial value and the value that i am selecting from poplist.

View 1 Replies View Related

Application Express :: How Does Filter Operator Contains Work On Interactive Reports

Jul 30, 2012

I'm creating Tool Tip definitions for the Operators in the Filter on the Interactive Reports. I was looking for a definition for the 'Contains' operator and from what I've found this operator is used to do a text search and it returns a relevance score for every row selected.

I've also read that in order for that score to be determined that the column(s) need to be indexed with a CONTEXT index. Non of the columns in the tables are indexed with a CONTEXT index, however, when I put a value in the Expression box for a column I get a record returned.

If I run the same query in PL/SQL Developer like:
SELECT <column>
FROM <table>
WHERE contains(<column>,<search text>,1) > 0;

I get an error that the column is not indexed, so how does it work in APEX?

View 2 Replies View Related

Forms :: Dynamic Record Group To Filter Selected Values In A Multirecord Block From A LOV

Jul 9, 2013

I am working on Oracle forms 10g (Windows 7 OS).I have got one requirement to show one LOV for COLOR table. Say i have three colors BLACK, RED, BLUE in color table. Suppose in a multi record block if a user selects BLACK from a lov for one record then for the next record lov it should show only RED and BLUE. How to filter the COLOR from a LOV record group query which are already selected?

View 1 Replies View Related

Server Utilities :: DBMS-DATAPUMP.metadata-filter And Long Table List?

Jun 9, 2010

I have a problem with DBMS_DATAPUMP.metadata_filter.Let's suppose that I need to export a huge list of tables (a,b,c,d,e,f,g,h,i...). Let's suppose that the list is dynamic do NOT want to use

DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (''a'', ''b'', ...)',
object_type => NULL);

In my_export_table there is the list:

CREATE TABLE my_export_table
(
EXPORT_OBJECT_NAME VARCHAR2(50 BYTE)
)

Now I'm trying to use this form:

DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (SELECT a.export_object_name FROM my_export_table a, user_objects b WHERE a.export_object_name = b.object_name AND b.object_type = ''TABLE'')',
object_type => NULL
);

but it results in error.

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00942: table or view does not exist

[code]...

View 1 Replies View Related

Application Express :: Version 4.1 Interactive Report Filter On Date Column Has No (=) Operator

Aug 17, 2012

if on an interactive report, you try and filter on a date column, then the "=" operator disappears.You get other (not so useful) operators like "in the next", "not in the next" ">", ">=" etc. - but no "=" to be found.

View 6 Replies View Related

Performance Tuning :: Where Filter Result Rows Save Before Join And Group By Operation

Jul 7, 2012

Where filter middle_rows save before join and grop by operation?

It is save rows in PGA Private SQL Area or save blocks in SGA databuffer?

View 11 Replies View Related

Forms :: How To Get Browse Option

Nov 1, 2013

I want browse option in oracle forms.

View 3 Replies View Related

SQL & PL/SQL :: Option For Temp Table?

Feb 18, 2010

What is the Best option for SQL/sybase server Temp table to use/for data manipulation (Insert/update/delete) inside the procedure in oracle Other then global temporary table.

Since we are porting from SQL/Sybase to Oracle we don't want to Create too many global temporary table.

View 3 Replies View Related

SQL & PL/SQL :: Returning Values With MORE Option

Jan 19, 2012

Imagine I have the following scenario:

CREATE TABLE AAA_PLACE
(COD_PLACE NUMBER,
PLACE_NAME VARCHAR2(50 BYTE)
)
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (1, 'Munich');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (2, 'Lisbon');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (3, 'Monaco');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (4, 'Madrid');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (5, 'Milan');

Imagine I want to retrieve those records in only 3 distinct rows, like this:

Munich
Madrid
Others

Is there any way I can do this with an simple Statement?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved