SQL & PL/SQL :: Repetitive Calculation - Track Number 10th Record?
Sep 11, 2012I am writing a program in that i have to do a calculation in for loop when every 10 records processed. How can i track the number 10th record .
View 4 Replies
ADVERTISEMENT
SQL & PL/SQL :: Number Of Years Calculation
Mar 16, 2012for making the query for following data,when we give start and end date then query need to calculate number of years.
if less than one year then return 0
if exact one year then return 1
if exact 1.5 years (18 months) then return 2
if exact 2 years (24 months) then also return 2
if exact 2.5 years (30 months) then return 3
if exact 3 years (36 months) then also return 3
if exact 3.5 years (42 months) then return 4
if exact 4 years (48 months) then also return 4
and so.
also we need to add leap year 1 day if exist in start and end date.
YearSalary
11
1.52
22
2.53
33
3.54
44
4.55
55
[code]....
SQL & PL/SQL :: Give Access Only On 10th Row For Schema2
Mar 26, 2010I have two schemas.In schema1 i hava a table with 20 records,i want to give access only on 10 th row for schema2.
View 6 Replies View RelatedMaximum Of VALUE Field Of Each Record With Different POINT-NUMBER?
Oct 21, 2011 I need to get the maximum value of the VALUE field of each record with different POINTNUMBER, then do an update on the 2nd table.
table 1
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ
table 2 (idem structure)
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ
Where POINTNUMBER is six thousand different values My query only returns me the maximum of a single record:
SELECT * FROM table1 WHERE value = (SELECT MAX (value) FROM table2);
And the update:
UPDATE table2 SET to a.value = (SELECT MAX (b.value) FROM table2 b);
so does on a single record.That needed to maximize each pointnumber different? I can use a cursor to do this easier?
Insertion should first make a one-time, and then updates every 1 hour
SQL & PL/SQL :: Query To Find Number Of Nulls For Each Record
Aug 18, 2013We have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.
provide a simple query using case in ORDER BY clause.
ID CLASS NAME DIST_ID DIST_NAME
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL NULL NULL NULL NULL
0 C123 RAJESH 0 AP
NULL C123 RAVI NULL AP
We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as
1 2 3 4 5
ID CLASS NAME DIST_ID DIST_NAME
for the following three records number of NULL values are same. but positions are different.
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
Based on the column positions the ranks as
2*2+3*3=13
1*1+3*3=10
2*2+3*3=13
1*1+4*4=17
Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as
ID CLASS NAME DIST_ID DIST_NAME
0 C123 RAJESH 0 AP
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
NULL NULL NULL NULL NULL
SQL & PL/SQL :: Count Number Of Record Based On Some Condition
Jun 28, 2013i have two tables one as test_master and other one limit master.
test master table structure
ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure
limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --
ct_id and limit_id is match column used for mapping.
now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.
how to achieve the same using sql/oracle query.
Forms :: How To Get Total Number Of Record In Form
May 20, 2011how to get total number of record in form
View 3 Replies View RelatedSQL & PL/SQL :: Finding Record In Table Having Value For Maximum Number Of Columns
Nov 25, 2011if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns
(c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3)
and
r1 has values only for c1,c2
r2 has values only for c1,c2,c3,c4
r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
JDeveloper, Java & XML :: How To Manage Large Number Of Record In ArrayList
Feb 5, 2013how to manage large number of record in ArrayList.i.e abc table has 20 million record and jvm size is 2 GB.
View 1 Replies View RelatedForms :: Limit Number Of Detail Records For Every Parent Record To 8?
Nov 10, 20101. My detail form has part number as one of the fields and I want to make sure the user can enter a part number only once. How can I make sure user cannot enter it more than once?
2. I want to limit number of detail records for every parent record to 8. How can I accomplish this?
3. I have approval flags at both master and detail. If the user tries to approve the master with some/all detail records unapproved, system should display error message and make the user approve the detail before approving the master.
Reports & Discoverer :: Multiple Record Printing On Same Line Number In Oracle 10g?
Jun 28, 2013I want to print two record of emp table in same line number in tabular format report.
For example if i have emp table and I want to print empno,ename,sal columm only,In tabular format report, Two record should print per line. so If emp table have 14 record report should be printed on 7 rows.
so the output in print preview should be like this
EMPNO ENAME SAL EMPNO ENAME SAL
------ ---------- ----------------------------------------------
7369 SMITH 800 7499 ALLEN 1600
7521 WARD 1250 7566 JONES 2975
7654 MARTIN 1250 7698 BLAKE 2850
.
.
.
.
Forms :: How To Track Radio Button ID Or Name In Oracle
Jun 27, 2010I have a form which contains some block (each block with some text item, two radio groups and a list item). I want to enable/disable and set visible property as true/false for first to last items of each block conditionally. I can do that for all items but radio buttons.
Because to set item or radio button property I need to pass that items ID or Name as parameter. I can get items name/type using get_block_property and next item of that item using get_item_property but unable to get radio button ID/Name using get_radio_button_property or any other built in. To execute get_radio_button_property or set_radio_button_property I need to pass radio button ID/Name. So, how I track radio button id or name in oracle forms? Is it limitations of Oracle forms?
Server Administration :: How To Track The Last Access To A Table
Oct 11, 2011is it possible to track the last access to a table?
View 2 Replies View RelatedSQL & PL/SQL :: Track Department Change - Update A Counter
Jun 22, 2011I am trying to track an employee's department changes by adding a Department change counter which is set to 1 whenever the department changes otherwise it is set to a 0. How can I accomplish this update of the counter? Below is the table creation sql and sample data for insert.
CREATE TABLE EMPL_DEPT
(EMPLID VARCHAR2(11) NOT NULL,
EFFDT DATE,
EFFSEQ SMALLINT NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
DEPT_ID VARCHAR2(12) NOT NULL,
[code]..........
SQL & PL/SQL :: Keep Logging Track Of Each Task Performed In Procedure Packages
Nov 26, 2010we have a database application which is done frequently.in these we load data throught Sql loader, we create an DB instances, we do several DML operation on the database.
now for such task in an application we need to keep an logging track of each task performed in PL/SQl procedure packages.
SQL & PL/SQL :: Adding Audit Column To Track Delete And Update
May 15, 2012we received a design advise to add columns to track the update and delete done on each row in our tables:
- DELETED_DATE_TIME
- DELETED_BY
- UPDATE_DATE_TIME
- UPDATED_BY
In our architecture, the application can only access functions/procedures to access/modify data. Each function logs the action, the executed sql statement, oracle error, user terminal, and the user into a unified log table by using v$ tables to create a general log function that is called after execution or error.
The only advantage is that it will be easier to know the delete and last update information faster versus space and design modification.
Server Administration :: Back Track OS Processes To RDBMS
May 5, 2013I have two Oracle databases (test1- 10gr2,test2 - 11gr2) on rhel 5.5. is it possible to find which particular oracle process belongs to which home from OS level. was trying to find which db processes are consuming more resources on my host.
View 4 Replies View RelatedForms :: Trigger Program To Track User Information
Jun 25, 2013I have a trigger program to track updated information's on perks column. I am failing to finish systime in my trigger program.
SQL> desc user_track;
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NUMBER
ENAME VARCHAR2(15)
OLD_PERKS NUMBER
NEW_PERKS NUMBER
UNAME VARCHAR2(15)
ACTION_DATE DATE
ACTION_TIME DATE
SQL> create or replace trigger tri_track
2 After UPDATE ON EMP
3 FOR EACH ROW
4 BEGIN
5 insert into user_track(EID,ENAME,OLD_PERKS,NEW_PERKS ,UNAME,ACTION_DATE,ACTION_TME) values
6 (:old.eid, :old.ename , :old.perks, :new.perks,user, sysdate, to_char(sysdate , 'HH24:MI:SS')
7 from dual;
8 end;
9 /
Warning: Trigger created with compilation errors.
SQL> show errors;
Errors for TRIGGER TRI_TRACK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
3/95 PL/SQL: ORA-00917: missing comma
Performance Tuning :: How To Track Delay In Displaying Data On Application GUI
Dec 21, 2011What could be the reasons that some queries execute fast when executed on sqlplus on server, whereas the same queries run slower with same input values fed from application screen?
One issue I guess would be bind variable peeking while using application whereas executing from sqlplus is causing hard parsing and thus getting rid of "peeking"
If displaying the data on application screen is taking time after data has been fetched, where I can see this delay?
I understand the elapsed time under 'Fetch' in tkprof will show time taken to fetch from database and not the time taken to be displayed in the application GUI
finally how to set arraysize in jdbc to improve performance by reducing roundtrips?
Forms :: Keep Track Of Item Value Totals In Header From Items Under Each Activity
Nov 24, 2010I have three blocks in my form , header,activity and item.One header can have many activities and one activity can have many items.I need to keep track of item value totals in header from items under each activity.
View 1 Replies View RelatedPL/SQL :: Track Table Data Changes (inserts / Updates / Deletes) Including Os_user
Mar 12, 2013I was given a task by manager to keep track of changes on a given table including os_user who made it.Should I create a trigger on it (on any update, insert, delete etc.) or there is a better way of doing it ?I think there could be some info already in some data dictionary views or something like it.
If I CREATE MATERIALIZED VIEW LOG on that table.
SQL & PL/SQL :: Gratuity Calculation?
Mar 16, 2012i have attached the .csv file for gratuity calculation.
In the given criteria you can see if number of years 1 then he can get 1 salary if years 1.5 then 2 salary and so on also we need to take care about the leap year.
SQL & PL/SQL :: For Ratio Calculation?
Sep 14, 2011,i had given the sample data below
create table ex (sno number,ename varchar2(10),job_code char(4),sal number);
insert into ex values(101,'John','Java',21000,'IT');
insert into ex values(102,'Michel','BI',25000,'IT');
insert into ex values(103,'Johny','Java',30000,'IT');
[code]...
My expected output is attached in a text file
Security :: User Locked / Track User Who Hit Wrong Password
Dec 21, 2010We have the database user called c88888 and is locked yesterday. I came to this with the following query.
select * from dba_users where username like 'C88888';
Due to invalid password the user was locked. Can we track who hit the database with the wrong password?
SQL & PL/SQL :: Field Name Based On Calculation
Apr 19, 2010I want to display Week No as the heading.So, I have these:
define week_no=number
column week_col new_value week_no
select to_number(to_char(sysdate, 'ww')) week_col from dual;
I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;
The output:
Week 16
----------------------------
to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?
SQL & PL/SQL :: Calculation On A Time Field?
Jul 5, 2012I have time fields that have been converted from a 12hr clock to a 24hr clock and these fields hold only the time...no date.
I need to be able to determine the difference between column A and B eg.
Column A: Column B:
11:00 14:00
I can remove the ':' by using the replace command, but as I need to run on a actual 24hr clock I am not sure how to tackle the calculation as it is not the full oracle datetime format.
SQL & PL/SQL :: Cumulative Calculation In Oracle?
Feb 24, 2011Is it possible to calculate cumulatively in oracle sql queries, i.e. using the results of the last row for calculating the values in the current row?For example I want my query to return the following:
Month col1 _ col2 _ col3
jan 1 _ 100 _ 100 * 1
feb 2 _ 200 _ 100 * 2 + 200
mar 3 _ 300 _ 400 * 3 + 300
apr 4 _ 400 _ 1500 * 4 + 400
may 5 _ 500 _ 6400 * 5 + 500
In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200), for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.
i.e. I want to use the previous value of column3 to derive the current value of column3. Like using the LAG function but on the analytically derived column itself.
SQL & PL/SQL :: Time Zone Calculation
May 30, 2010Check the following
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'gmt')
AT TIME ZONE '+05:30' "Time at Time zone"
FROM DUAL;
Time at Time zone
01-DEC-99 04.30.00.000000 PM +05:30
My requirement is that
I want to add 2 hours to DateTime i get here i.e. add two hours in result and display the resultant date and tine in time zone '+05:30'. I also want to check that the resultant time falls in business hours (9 am to 6 pm).
Forms :: Calculation When Add Numbers
Feb 5, 2011I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example
5.24 Plus 5.24 it will give the result 10.48
I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12
Forms :: Time Calculation
Oct 20, 2011Currently I am working on payroll system where I have to calculate employees working hours/late coming hours/early going hours against its roster which is defined in the beginning of every month/week.
In roster form user define shift of every employee like
Code Name Shift
7 Saad Nafees A - 09:00 17:00
492 Muhammad Nasir Shahzad B - 17:00 01:00
243 Muhammad Tahir C - 01:00 09:00
Roster table structure
code varchar2
name varchar2
Shift date
Remarks varchar2
shift table structure
code varchar2
timein date
timeout date
latetime date
Hdaytime date
Oracle stores both date and time information in date data type, suppose today user change shift timings from 17:00 to 17:30 or user change timein/timeout in attendance form then oracle will store current date with user define timings.
Now this is the main problem which I am facing because whenever you calculate difference between timein and timeout or compare with its roster then output comes wrong because oracle returns total no of hours whenever you minus two dates.