I have an Oracle query that I have built based on user input... user enters a description (multiple words) and I search for existing descriptions like it. For instance I may have a record like "My name is matt". Right now the query looks like
select * from table where
(field1 like '%My%' or
field1 like '%name%' or
field1 like '%is%' or
field1 like '%matt%');
There may be tons of records that have at least ONE of these, but I want to order by relevance, so a description "my name is bob" (3 words in common) will appear before "his name is not mine" (just 2 words in common).I have seen the SCORE() and CONTAINS() functions but not sure about multiple search criteria.
Been cruising all sorts of ways to order by. My last ditch effort my be to use the WITH clause, but I don't want to go there just yet...
Using Oracle 10G against a database that uses a COTS CHAR datatype schema against an in-house varchar2 datatype schema (hence all the trims you will see in there so I can get proper joins)
Here is my query in question. Beware it's somewhat eye-bleeding!
i create a form for ordering>>>that the person can enter his/her information and isbn book to make the order,but the order id i make it sequentially! well first of all i create a data-block and i insert only the oid in trigger when-new-item-instance..i had create the sequence:
declare create sequence seq1 start with 90 increment by 10; end;
and i make a view form such of fields that user can enter a details o what he/she want to order!!
1- is that enough?
2- it seems there is an error in creating the sequence?? wrong way to create the sequence!!
I need to return an ordered list of documents. The documents may belong to a set id (optional) and if so, are either a "master" or a "duplicate" type. For each set there can be only one master but many duplicates. My goal is to group all the sets together such that each master is proceeded by its duplicates.
There's also a documents table containing the documentid and among other things a page_count. In the following example I want to sort the documents first by page count but preserving the master/dupe grouping. Any documents which don't belong to a set or are just a duplicate without a master i want at the end of my set but also ordered by page count.
Here's an example set that I would want to order by:
As you can see I have a little bit of everything here. Docs 2001 and 2002 are the typical set of 1 master and its duplicate. Docs 2010, 2011, and 2012 is the same just a set of 3. Doc 2004 is a master but without any duplicates. Docs 2003 and 2014 are duplicates without a master (these docs have a master in the table but that doc isn't in the set i need to order by). Docs 2008 and 2009 do not belong to a set and as such do not have a master/dupe type.
The result i'm looking to achieve will be ordered as follows:
As I said above I first want to get the groupings of master/dupes and order ascending on the masters page count. For each duplicate of a master I then want to order the duplicates by page count. After I finished ordering all the master/dupe groups I then want to move on to the rest of the documents which will contain documents that don't belong to a set along with documents which are duplicates but have no master in my set. However, documents which are masters but without duplicates should have been ordered along with the other master/dupes groupings.
With this all in mind I have just been completely overwhelmed as to where to even start. Am I using analytic functions? Hierarchical stuff?
I need to order the result set with different data types based on the input parameter.
select * from scott.emp sc order by decode('&input_parameter',1,sc.empno,2, sc.ename);
If the input_parameter is equals to 1 then,ordering should be based on EMPNO which is Number data type. If the input_parameter is equals to 2 then,ordering should be based on ENAME which is Character Data type.
Above query was failed for input_parameter 2,as we know that decode should return same data type.
I am having a matrix report, which has its column cells from table A and row cells from table B when I run the report the columns and rows are not ordered I wrote the order by statement in the report query but there was no defference.
I also wrot a subquery like
select column1, column2, (select column3 from table1 order by code) from .... but it seems I can not order in a subquery
I have two Oracle instances that are setup identically.When I run a query on one of them, it takes around 3 seconds, on the other it takes around 200 seconds.
I have looked at the explain plans, and it has shown me what I think is the problem. On one instance, it does a join on two tables, then runs the other filter/access predicates. On the other instance it runs the filter/access predicated first, then does the expensice join. The one that does the join first is the one that takes around 200 seconds. How to tell Oracle to make this join after runnning the other predicates?
Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.
The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select 'date_on' to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal 023 | John | 6000 143 | Tom | 9000 876 | Chi | 4000 987 | Alen | 7800
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select x,y,z from mytable@dblink
returns 788,324 rows
while select x,y,z from mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?
Background:
We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.
The legacy HR system allows multiple phone numbers to be stored in a simple table structure:
SELECT emp_id, phone_type, phone_number FROM employee_phones
The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)
From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.
That can be represented as:
SELECT * FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')
SELECT * FROM (SELECT * FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1
SELECT phone_number FROM (SELECT phone_number FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1
phone_number ------------------- 1111111111
However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.
(SELECT phone_number FROM (SELECT phone_number FROM employee_people_phones WHERE emp_id = export.emp_id ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1)
1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?
Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.
I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.
Query 1 :- select a.col1,a.col2,b.col3,sum(b.col4) from tab a, tab b where a.key1=b.key1 and a.key2=b.key2 group by a.col1,a.col2,b.col3
Query 2 :- select a.col1,a.col2,b.col3,sum(b.col6) from tab a, tab b where a.key1=b.key1 and a.key2=b.key2 group by a.col1,a.col2,b.col3,b.col5
I am facing problem with a select query in oracle 10g database from vb.net.It was working for oracle 9. The select statement I have written is as follows
I have one table in database that contains 3 foreign keys to another tables(this three tables name are: manager,worker and employee). in each row only one foreign key is filled.I need to write one query that with attention which column of fk is filled in where clause specified condition is performed. I write simple query in jpa but doesn't work properly
select b from allEmployees b where b.manager.name= :name OR b.worker.name = :name OR b.employee.name= :name
I am looking to get the maximum value for every 24 hour period for a month. So for example my date range can be defined by...
select to_date('&date','mm yyyy')-1 + level as DateRange from dual connect by level <= '&days'
...where I can provide the first date of the month and number of days in the month or a lesser value if less time is required. So, the results of the above query plus 24 for the range. I thought a some Googling would provide me what I needed, but my search came up empty.
I was hoping to do something like this...
select utctime, max(value) from table where utctime between.
SELECT so.* FROM shipping_order so WHERE (so.submitter = 20) OR (so.requestor_id IN (SELECT poc.objid FROM point_of_contact poc WHERE poc.ain = 20)) OR so.objid IN (SELECT ats.shipping_order_id FROM ac_to_so ats WHERE (ats.access_control_id IN (selectac. objid FROM access_control ac WHERE ac.ain = 20 OR ac.group IN ('buyers', 'managers'))))
rewrite this query to use joins. That would greatly simplify my sql query building code. The ids, objids, submitter, ain are numeric and group is a varchar.
"Determine what departments are in each of the 4 regions. That is, what are the names of the departments that reside in each of the regions." So basically it wants me to list the departments in each region.
This is what the tables look like that it could possibly be drawn from...
