SQL & PL/SQL :: Determine Number Of Times A Value Appears
Nov 11, 2012
I am trying to determine the number of times a value appears and display the count. However the value can only be counted once per 'trip' even though it may appear several times per trip.
for e.g.
Quote:trip table
-------
trip_id start_date end_date duration
445 01-jan-12 03-jan-12 3
Quote:pickup table
--------
pickup_id trip_id company
1 445 randomname
2 445 randomname
3 445 google.inc
4 878 randomname
with the above data the expected value would be two because the trip id appears twice so it was just the one trip - given a count of one. I am not sure how create a query to check this.
View 2 Replies
ADVERTISEMENT
Mar 24, 2013
How to calculate the number of TPS supported by any solaris server for example one server with below configuration .
Sun Blade X6270 with two 4-core processors
2 x 300 GB internal disk drives
View 1 Replies
View Related
Oct 25, 2011
how many number of times the sql's had been executed/run in an oracle session. Executions in V$sql, is it the right place to look at? Will it give the number of times the sql has been executed in a particular session?
View 3 Replies
View Related
Aug 2, 2013
Get the following error from Oracle ORA-24333: zero iteration count Cause: An iteration count of zero was specified for the statementAction: Specify the number of times this statement must be executed on whether is this a Oracle bug or if not what should be set to avoid this failure.
View 2 Replies
View Related
Mar 28, 2013
SELECT * from TABLE_1 where a=b;
Is it possible to make the SELECT (PL/SQL is OK ) wait/block in case there are no rows found until another session inserts rows that match the condition?
Something like this
cursor c is select * from TABLE_1 where a=b order by column_a for update skip locked;
row c%rowtype;
begin
open c;
[Code].....
Or the INSERTing session should send some kind of notification (semaphore?) to the reading session?
View 29 Replies
View Related
Jan 5, 2011
i'm facing a problem when i run form om forms 11g
that error appears :
frm-10142 HTTP listener not running on weblogic on port 80
and when i ran it on weblogic server after compiling it on internet explorer a blank window appear and written DONE
View 1 Replies
View Related
Oct 14, 2011
I have an Oracle IMP that seems to complete, but there are two things that I don't understand:
1) The tables seem to load successfully in about 4 hours. After the tables are loaded, the IMP procedure appears to hang for10+ hours. I can query the tables at this time, so I'm not sure what it's doing. Is it maybe applying constraints?Is it normal for applying constraints to take 3-4X as long as importing the data?
2) I see a few of these messages in the alert log during the import.Thread 1 cannot allocate new log, sequence 2754 I don't see errors in the IMP log so are these messages informational or warnings, or will there be something wrong with my imported data?
View 1 Replies
View Related
Jan 22, 2013
I am working on Oracle apps. I have developed a custom screen. On the screen, if the user press 'F4' on final step, it will ask the dialogue..Do You want to save changes?
Yes No Cancel.
Even the user press the "Yes" , I want to check my own validation and commit when my validation get perfect.
View 1 Replies
View Related
Feb 8, 2012
When I transfer the data from Oracle to MS SQL I have one column in a table which is of Arabic Language.The data is fine in Oracle but when I transfer it to MS SQL Server I see that the text in MS SQL server appears as junk.
View 1 Replies
View Related
Mar 18, 2011
How to hide oracle in-built message which appears at the bottom of the window(just above the task-bar) to the left side.
View 2 Replies
View Related
Jul 24, 2013
Apex 4.2.0.00.27Firefox 22.0, IE 8 Just started using the Enkitec Navbar, and we have noticed that when a page is rendered a vertical list always appears momentarily before being replaced with the Navbar list. It happens on all applications we have installed it on, and every time a page is refreshed.
View 13 Replies
View Related
Sep 12, 2012
I have a table that holds the definition of schedules, a schedule defines when a document should be submitted to a specific party. The schedule definition contains a start date, and end date, a recurrence type (is this submitted one time, or on a recurring schedule) and the frequency at which the document should be submitted. The second table provides a history of the submissions, this stores when it was due and when it was received. At the beginning of the month we pre-populate the submittal table with a list of records that will be due for the month. For instance, on September 1 we look through all of the schedules and determine which ones would have a record due at some point in 9/12 and then create a record in the submittal table.
I am having issues getting the calculated list of submittal records to work properly.
The DDL and DML will be in a follow-up post
Here is the query that I am currently using and fails to work properly.
with schedules as (
-- generate a list of valid permit schedules
select s.schedule_id,s.submittal_frequency_months,s.recurrence_type,
s.first_due_date,s.requires_approval,
round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) recurrence_number
[Code]....
-- create a list of all potential due dates for these schedules
select submittal_id_seq.nextval,schedule_Id,8,requires_approval,
case
when recurrence_type='One Time'
then first_due_date
when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')
[Code]....
-- exclude those that already have a submittal record ;
Basically I found all possible records in the schedule table that could have a record due in September, then generate a result for all possible instances and then look at only those whose calculated due date is 09/01/2012. I've determined that the root problem I have right now is this line:
(select level iteration from dual connect by level <= (select max(schedules.recurrence_number) from schedules)) d
Schedule ID 469907 has a start date of 05/15/1992 and a frequency of every 2 months. I calculate what I call the recurrence number, which is the number of times the schedule has happened since its start date to now. I use that to do an add_months calculation from the start date and then eventually compare these calculted start dates with my target month (09/12). In this one records case the calculated recurrence number is 122. So when I generate the connect by level is does 122 records for every schedule, so I end up with duplicate records in the submittal table for many of the schedules. This current query could probably work if I could figure out a way to make the level be schedule ID specific, but I've failed at that thus far.
Aside from the fact that this is returning the wrong results, I am thinking there must be a better more efficient method to determine which records are due for a given month. I was thinking there is probably some cool way to use the model clause here, but I haven't got a grasp on that one yet.
If you run the following insert statement you'll see that it inserts over 2400 records:
insert into submittal (submittal_id,schedule_id,submittal_status_type_id,requires_approval,due_date,created_by,created_date,modified_by,modified_Date)
with schedules as (
-- generate a list of valid permit schedules
select s.schedule_id,s.submittal_frequency_months,s.recurrence_type,
s.first_due_date,s.requires_approval,
[Code]....
-- only submittals whose last due date has not passed, null last date included
and trunc(s.first_due_date,'mm') <= to_date('09/01/2012','mm/dd/yyyy') -- only valid start dates
--and round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) >0
)
-- create a list of all potential due dates for these schedules
select submittal_id_seq.nextval,schedule_Id,8,requires_approval,
case
when recurrence_type='One Time'
then first_due_date
when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')
[Code]...
You can see the problem after words:
select schedule_id,count(0)
from submittal
where trunc(due_date,'mm')=to_date('09/01/2012','mm/dd/yyyy')
and submittal_status_type_id=8
having count(0) >1
group by schedule_id;Tony
View 14 Replies
View Related
Mar 18, 2013
I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdMy problem is, There is data for IN time and OUT time data type is DATE. I need to calculate OT hour from this two field and store into a third one column. But i'm confuse what should be the data type of the third column. Is it number or else ?
I know difference of two date column is number. So, first i need to know that data type i should use ?
Secondly, how i get the sum of the all stored time ? for example data are
1.30 -- One hour and thirty min.
2.45
3.50
View 20 Replies
View Related
Apr 3, 2012
How to find out who has dropped and when was the index created in database?
View 39 Replies
View Related
Oct 2, 2010
I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.
The data table is: ITEM_INVENTORY
The master table is: MASTER_SOURCE_SYSTEM
UPDATE ITEM_INVENTORY I
SET I.SOURCE_SYSTEM_ID =
(SELECT NVL(M.SRC_SYS_ID,-100)
FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
WHERE ORG_CODE = 'TNXC'
AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
The situation here is that:
1. There are about 15000 rows that match ORG_CODE = 'TNXC'.
2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.
My question is: Do both the inner select statements execute 15000 times?
The statement executes within a second and updates 15000 rows. How is this made possible?
View 10 Replies
View Related
Oct 19, 2012
i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00
so in this it should return 360 minutes.
View 5 Replies
View Related
Sep 15, 2010
Code to determine the data type of a variable in oracle 10g.
View 5 Replies
View Related
Aug 9, 2012
We are investigating performance of SQL executions on a database server and we suspect I/O on the server is an issue
For example one particular statement accesses one row during execution (index access) and still takes 2.4 seconds out of which it does I/O for 1.9 seconds
which of the following sections in the AWR will give us the correct information about the I/O, it is slow or not?
1)
Load Profile
Logical reads per second
Physical reads per second
2)
Top 5 Timed Foreground Events
waits / time(s) for events like "db file sequential/scattered read"
average wait(ms) for events like "db file sequential/scattered read"
3)
Foreground Wait Events
db file sequential read
db file scattered read
4)
Wait Event Histogram
%of waits <1ms <2ms
Disk file operations I/O
db file sequential read
db file scattered read
5)
Wait Event Histogram Detail (64 msec to 2 sec)
Wait Event Histogram Detail (4 sec to 2 min)
6)
IOStat by Function summary
Buffer Cache Readsreads per sec
7)
File IO Stats
View 5 Replies
View Related
Mar 28, 2012
How to find the tables starting with smallest size and vice versa in schema level and database level?
View 4 Replies
View Related
Jul 9, 2010
Ok assume there is a table (TableA) in this format
col1col2 col3col4col5col6
--------------------------------------------------------------
R1route1route1Description1AABBCC
R1route1route1Description1AACC
R1route1route1Description1CCBB
R2route2route1Description2GGKKLL
R2route2route1Description2GGLL
R2route2route1Description2LLKK
[Code]..
The data in the table was imported from a csv file and there is a relationship between the rows. Each combination of col1, col2 and col3 describes a full route of a journey. The row with an entry in col6 describes the full route and the other rows describes each leg in the route.
For example, for R1, the route is AA to BB via CC.
Another example for R4 the route is FF to SS via XX, PP, and OO.
What i would like to do is missing a route. For example the route for R3 is DD to EE via FF. There is an entry for DD to FF but is missing an entry for FF to EE.
The results should return the following rows which are incomplete
R3route3route1Description3DDEEFF
R3route3route1Description3DDFF
R5route5route5Description5RRTTUU|VV
What is the best way to do this?
Here is what i have come up with but it doesnt quite returned the correct result.
select * from tableA a
Where not exists(
select 1 from tableA b
where instr(col6,col4,1)>0 and instr(col6,col1,1)>0)
And a.col1=b.col1
And a.col2=b.col2
And a.col3=b.col3
)
Is there an easier way to achieve this?
View 5 Replies
View Related
Feb 18, 2013
The db is 11.2.0.3 on a linux machine.I would like to know the "fetch size" of an application, but I was not able to find any related meteris in v$statname.
The application configruation is invisible to me.Do I need to do some calculations based on statistic metrics from v$statname?
If so, what meteris should be considered for the assumption for "fetch size" ?
The following is from manual, but the application configuration is invisible to me.
[URL]
Setting the Fetch Size
The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:
•void setFetchSize(int rows) throws SQLException
•int getFetchSize() throws SQLException
View 4 Replies
View Related
Mar 9, 2010
How to get rows N times in a result set?
For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.
But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".
View 2 Replies
View Related
Oct 8, 2011
I am dealing with a bunch of tables containing sales information for an New Zealand organisation. The sale datetime has been recorded as UTC.
New Zealand operates Daylight Savings, so twice a year it changes its clocks.
When New Zealand is on standard time it is UTC+12.
When New Zealand is on daylight savings time it is UTC+13.
Thus an event which actually occurred when New Zealand was on standard time at 2011-08-31 15:20:52 local time, is recorded in the database as having occurred at 2011-08-31 03:20:52. However, an event that actually occurred when New Zealand was on daylight savings time at 2011-10-06 15:20:52 local time, is recorded in the database as having occurred at 2011-10-06 02:20:52.
I want to be able to read the sales dates from my table and convert them to the actual time in New Zealand when the event occurred. The table will contain data for sales that occurred in both standard and daylight savings times.
I do not think that the data has been stored with time zone information, simply that the application writing the data to the Oracle database, calculated the event time as UTC when it occurred and wrote that time to the table.
Does Oracle only know about what UTC-offset is in force right now or is it capable of determining what offset from UTC is required for any given historical date ?
View 6 Replies
View Related
May 6, 2011
I'm trying to determine if/when a possible Hierarchical circular reference will occur in my data
Sample Hierarchical structure that I have
Emp -> Supv
A
BA
CB
DC
EC
[Code]....
Finally, to my question. It seems that I can detect the problem After it happens but do I need a trigger on the update statement to detect if/when a possible circular reference will occur?? or can I run a sql statement prior to update to detect possible circular reference?
View 5 Replies
View Related
Mar 27, 2013
Is this the right query to determine index fragmentation ?
SELECT AVG (tfrag)
FROM (SELECT /*+ ORDERED USE_NL(i) INDEX(i DR$TEXT_IDX$X) */
i.token_text,
(1
[code]...
The reason I am asking that before index rebuild it returned 86% but after rebuild (ALTER INDEX .. REBUILD) it returned
96% which does not make sense.
I did try ctx_report.index_stats but it takes more time to run.
View 5 Replies
View Related
Jan 30, 2013
Is it possible to create a .sql script that, when executed, will determine which OS (i.e. Windows or Linux) Oracle is being hosted on? At the moment, all our scripts are written for Windows and I believe that for Linux the slashes must point the other way in order for the script to run.
Or, would the easiest thing be to create two copies of the script - one for Windows and one for Linux? :)
View 1 Replies
View Related
Jun 28, 2013
I have code that creates a sequence and a select statement that generates the next number in a sequence. It starts the sequence at 5 and every subsequent number is the previous number minus 3. The minimum value it can go to is 0. All I want is for my select statement to execute three times in a row. Is there any way I can do that?
create sequence MY_FIRST_SEQ
increment by -3
maxvalue 5
start with 5
minvalue 0
nocycle;
select MY_FIRST_SEQ.NEXTVAL from dual;
View 5 Replies
View Related
Jan 8, 2013
I would the query to do the following:
1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname
2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration
3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1
4. Generate the last seven days batch run times
5. Generate a report into .csv format and email out
6. I have access to sqlplus and plsql developer
select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...
View 3 Replies
View Related
Mar 22, 2011
I am trying to query O/P from two tables.Hence, populate same column(Level_Name) two time on report based on its Level
Table =Level_Mst
Codeno Level_Name Parent_level
1 IT Dir 0
2 HR Dir 0
3 Assets Section 1
4 Payroll Section 2
Table=Users
User_id Name Top_level Bottom_Level
1 John 1 3
2 Smith 2 4
Desired O/P
Name Top_lvl Bottom_lvl Top_lvl_Name Bottom_Lvl_Name
John 1 3 IT Dir Assets_Section
Right now im getting name from level_mst either for top_level or for bottom_level .I want to show both names in one row.
View 6 Replies
View Related
Nov 25, 2012
I need to open an explicit cursor for making a total: after I have to use the same information of that explicit cursor for dividing a column of the cursor by that total. It is not enough to open close, reopen and reclose because I just obtain one register at the same time and it is the same register two times consecutively.
I don't want to use auxiliary structures cause there are 18000 columns for 10200 rows.
FOR i IN 300..300 --18000
LOOP
y:=ymax-ysize*(i+0.5);
[Code]......
View 6 Replies
View Related
