I have a SQL link table called student_monitor containing the follwing attributes and e.g. data
student_id, Class_id, Predicted_Grade, Actual_Grade
1 1 A C
2 1 B B
3 1 C B
I need to be able to flag which students are under achieving on their target grades and dont know how to do this or the best way to do this as i cannot calculate the diff between a char?
EMP_ID IN_DATE QTY2
1070410/6/11 6:00 AM
1070410/6/11 3:00 PM
1070410/6/11 9:00 PM
1070410/7/11 6:00 AM
1070410/7/11 3:00 PM
1070410/7/11 9:00 PM
1070510/7/11 6:00 AM
1070510/7/11 3:00 PM
1070510/7/11 9:00 PM
following is the table where every employee have 3 records we need to put the flag datewise and employee id wise minimum in_date always be set to 1 and remaining 2 maximum dates for each employee date wise always set to 2 in qty2 field.
Output requirement is as follows:
EMP_ID IN_DATE QTY2
1070410/6/11 6:00 AM 1
1070410/6/11 3:00 PM 2
1070410/6/11 9:00 PM 2
1070410/7/11 6:00 AM 1
1070410/7/11 3:00 PM 2
1070410/7/11 9:00 PM 2
1070510/7/11 6:00 AM 1
1070510/7/11 3:00 PM 2
1070510/7/11 9:00 PM 2
I want to create a trigger with the requirement of achieving Primary key functionality.
I have a "EMP" table. the table already contains a duplicate data on "EMPNO" column. i want to restrict entering duplicate data further into table for that i want to create a trigger.
where can i find different triggering events of DML(like update, delete etc...)and DDL(database and schema level).
I need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065
here is my query:
=SELECT distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, lunchstatus, course_name, course_number ,termid
from students inner join storedgrades on students.id=storedgrades.studentid
where students.schoolid='0976111'
and (students.grade_level >'9' and students.grade_level <= '12')
and (course_number = all('6710', '6711', '1035')and course_number=any('1031','1032','3061','3065','3062', '3401', '3082'))
order by last_name, first_name, grade_level, course_number
The problem is that I'm not getting any results but I know for a fact that some students took the required courses. On a side note, if I change the ALL to ANY - I do get results - but it is not what I want because it would be hard to keep track of the students that met the requirements.
i have a table students create table students (name varchar2(10),rolno number(10),sub1mark number(10),sub2mark number(10),total number(10),percentage number(10),status varchar2(10))
i am go to create trigger on students after entering the values of name,rolno,sub1mark,sub2mark the values of total & percentage come automatically .trigger is
CREATE OR REPLACE TRIGGER UCSETH.students_comm_trig
BEFORE INSERT ON UCSETH.STUDENTs FOR EACH ROW
BEGIN
set NEW.total = new.sub1mark+new.sub2mark;
set new.percentage=new.total/2;
[code]....
I have been asked to show the names and room number according to students who have 4 classes in a room.
select s.name, r.room_no
from s, r
where r.room_no = u.room_no
and e.unit_code = u.unit_code
GROUP BY s.name, r.room_no
HAVING COUNT(DISTINCT s.stu_no) > 4;
However I get the error 'ERROR at line 4:
ORA-00904: "U"."UNIT_CODE": invalid identifier'
I have a confusion with MEMORY_TARGET and MEMORY_MAX_TARGET parameter. if i set SGA_TARGET, SGA_MAX_SIZE along with MEMORY_TARGET and MEMORY_MAX_TARGET then how oracle will manage the memory? Because as per my understanding if we set MEM
View 3 Replies View RelatedI have to run a resource extensive SQL. It utilizes CPU to a great extent. I've created a new user just to run this particular report. I need to ensure that this particular user doesn't uses more CPU.
Hence, I need to generate an alarm (email) only if a threshold is exceeded for a certain period of time (above x% for y minutes),(example more than 80% for 10 min.), so that I can either kill the process or continue running it depending on CPU usage.
Can I do it using scripts or OEM. I couldn't find any specific metrics to do it in latter. (I'd prefer scripts though. )
My system details are as follows:
1. Database: Oracle 10g R2
2. Unix: Sun OS 5.10
I am not a DBA, we are using Oracle 10g DB for our PLM applictaion Enovia, we are now trying to do 4GT in our windows 2003 server where our DB kept, while reading articles about this. we are informed that /LARGEADDRESSAWARE (IMAGE_FILE_LARGE_ADDRESS_AWARE) flag needed to be set to True in the application (here it is Oracle) to use more than 2GB in the RAM.
So My question is "Whether the corresponding flag is set to TRUE or not in Oracle 10g"? Is there any way to check that?"
I have records like the following
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N
ABCD 2/14/2012 N
ABCD 2/20/2012 Y
ABCD 3/01/2012 N
ABCD 3/10/2012 N
[Code]...
I have to write a select statement to to keep the first record and then pull only the records when the Valid_Flag changed. The result set should be like below.
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N -- I have preserved the first record
ABCD 2/20/2012 Y -- Valid_Flag chages to a Y for teh first time and so on.
ABCD 3/01/2012 N
ABCD 3/14/2012 Y
ABCD 3/25/2012 N
ABCD 4/25/2012 Y
If there is no change in the flag, I do not have to pull that record.
I have a scenario, where I need to find the bucket values with the flag as "Y". The problem is i have 5 buckets
Bkt1 Bkt2 Bkt3 Bkt4 Bkt5 Flag
------------------------------------------
1000 500 230 100 677 Y
789 100 122 666 888 N
783 110 142 166 788 N
781 110 112 616 988 Y
709 130 121 661 878 N
Here I need to write a SQL query in Oracle 10g fetching the respective bucket value(for all the 5 buckets) along with the flag as "Y" and more importantly should have the bucket name as well.This is needed as in the UI we need to display the respective bkt value which is "Y" as bold.
So in the final List I need to have the VO which should contain the respective bucket name, respective value and flag as "Y"
Some thing like this I need to get in my final ArrayList in java
Bkt1 1000 Y
Bkt1 781 Y
Bkt2 500 Y
Bkt2 110 Y
Bkt3 230 Y
Bkt3 112 Y
Bkt4 100 Y
Bkt4 616 Y
Bkt5 677 Y
Bkt5 988 Y
how we can do it using the SQL query.
I am working on an archiving strategy. I want to roll off transactions that are older than seven days, but only if they are flagged as Completed. The numbers of transactions are very large so this is a worthwhile venture.
The only strategy I have been able to come up with so far is to partiton on date. Then when 7 days comes up, sweep the about-to-be archived day for the few remaining not Completed transactions, put those into a new table (a new version of this partiton) and switch partitions. Each day I do this until the older parititions are empty.
I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.
The table has fields like:
category - string,
item - string,
creation_date - string ('YYYYMMDDHHMI'),
complete_flag - boolean,
etc.
Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date. Is there a way to accomplish this?
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;
BEGIN
IF
[code].....
The stand alone stored procedure has 2 parameter, an IN and OUT...
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,
tablename OUT VARCHAR2)
This procedure has multiple inner blocks. Intention is to control execution of each of the inner blocks basing on the value of flag field obtained from a "processlogtable". This processlog table has structure as below.
jobname varchar2(100)
controldate (date)
controlflag varchar2(1)
I have the below code snippet at the beginning of each inner block that checks value of the flag and then proceeds with execution of that block. The intent is to avoid redundant call of a block that has successfully executed first time. That is, once a specific inner block fails for some reason, the re-execution of the stored procedure should AVOID re-executing the PRIOR successed steps.
I've the code set up as below, but the prior successed block(s) code gets re-executed again once the procedure is re-executed after a failure.
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,tablename OUT VARCHAR2)
CURSOR c_missingtablename
IS
SELECT datatablename
FROM (
SELECT UPPER(datatablename ) TABLE
FROM
WHERE datatableName IN ('Aaa','BbB','CcC');
[code]....
I would like to spool a clob column to a flag file, however some of the clob are greater than 32k, and I have to have the same record in a single line in the file. Is there any way to achieve this through spooling?
set heading off
set feedback off
set term off
set long 1000000
set longchunksize 500000
set line 32767
set trimspool on
set pagesize 50000
spool file.txt
@--this is my select statement.
spool off
exit
We are using the 11.1.0.7 database, we implemented the Memory_Max_Target and Memory_target in the database.Here is the value of the memory parameters:
SQL> show parameter memory_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 3G
memory_target big integer 2G
shared_memory_address integer 0
We want to increase the value of the Memory_target=3G, means, I want to increase the value of the memory_target upto Memory_max_target by using below command:alter system set MEMORY_TARGET=3G scope=both SID='OLTP1'; but I am getting below error:
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00846: could not shrink MEMORY_TARGET to specified value
I tried to give the memory_target value less than the memory_max_target value like:alter system set MEMORY_TARGET=2900M scope=both SID='OLTP1'; but get the same error:
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00846: could not shrink MEMORY_TARGET to specified value
I created a sample table named as "Student" with following data. table contains two columns only - stdid & marks.
stdid marks
10 75
20 60
30 60
40 45
50 30
I have to find the rank of students based on their marks in descending order.Is there a way to get rank without using RANK function?
i 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.
,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
I 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?
I 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.
Is 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.
Check 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).
for 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]....
I 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
Currently 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.
I have three table for the stock calculations. The structure are like this
Product_master
product_id number,
product_name varchar2(30),
company_id number(3),
rate_per_unit number(14,4)
Purchase_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Purchase_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
I need to find out the valuation on particular sales date at FIFO method.
T1 = 06-Jun-2011 4:00PM
T2 = 10-Jun-2011 11:AM
Calculation is Required.
If at the end of Day Hours of T1 > 6 hours then it should calculate 1 Day.
same with T2. hours of T2(from begining of Day till end Time (11AM), it should again caculate if >6 hours then 1 Day.
remaining will be date calculation. this is how T2 - T1 should give us 5 Days.
Detail
06-Jun-2011 4:00PM to 06-Jun-2011 12:00AM (8 hours > 6 = 1 Day)
06-Jun-2011 12:00AM to 07-Jun-2011 12:00AM = (1 Day)
07-Jun-2011 12:00AM to 08-Jun-2011 12:00AM = (1 Day)
08-Jun-2011 12:00AM to 09-Jun-2011 12:00AM = (1 Day)
09-Jun-2011 12:00AM to 10-Jun-2011 12:00AM = (1 Day)
10-Jun-2011 12:00AM to 10-Jun-2011 11:00AM (11 hrs > 6 = 1 Day)
this is how 5Days calculation is required.
We want to calculate Annual leave the scenerio is as follows
1. Employee service more the one year
2. if joining date is 07-04-2008 than on 07-04-2009 completed one year and on 07-04-2010 completed two years and so on. day and month of date must be the same.
so we need YEAR*14. e.g. if 1 year completed then 1*14 if two years completed then 2*14 and so on. by this we will get the opening of Annual leaves.
We have another table where we entered the leave day by day in whole year, if he avail 10 leaves in first year then 4 will be remaining and we can say it is the closing of first year and opening of second year. so on second year he will entitled 14 more leaves so 14 + 4 = 18 should be opening of next year.In year Caclulation Day and Month must be the same for example 07 day, 04 month for every year. 14 Annual leaves are fix for each year
following are columns for output.
empid, ename, date of joining, sysdate, leave opening, leave avail, closing balance,
1, jone, 29-05-2009, 29-05-2012
2, herry,29-05-2008, 29-05-2012
3, bell, 29-05-2006, 29-05-2012
between two dates (date of joining and system date)