I have a classification based on age and nationality to calculate if the people will reach legal age after one month . I have the peoples age , the legal age of people is 18 years .
I need to know how many people will reach this age after 1 month.
Suppose I have the following, what is the best way to select the whole row that has the highest (IDX+LEN desc, LEN desc)
IDIDXLEN
5109153
7273118
9165356
I currently have made the following two method, which work but I don't particularly care for.
WITH bob AS
(SELECT 9165 id, 35 idx, 6 len FROM DUAL
UNION
SELECT 5109 id, 15 idx, 3 len FROM DUAL
UNION
SELECT 7273 id, 11 idx, 8 len FROM DUAL)
[code]....
How to get the max row based on a value in a column
View 1 Replies View RelatedOne of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?
View 9 Replies View RelatedI have the following table and data , I have six employees and carton named A,B,C,D,E and F.
Drop table a ;
Create table a (id number(9), emp_id number(9), cartoon varchar2(20), no_cartton number(9));
Insert into a values(1,1,’A’,10);
Insert into a values(2,1,’B’,20);
Insert into a values(3,1,’D’,25);
Insert into a values(4,1,’E’,15);
[code].......
I have also Third Query: the first and second query already solved in this link . Sum based on group
it is multiple value of carton A of emp_id 1 with 2 + multiple value of B of emp_id 1 and 2 and so on
More detalies : Multiple value of A for Empid 1 and 2 then add it to multiple value of multiple value of b for empid 1 and 2 then add it to multiple value of c of empid 1 and 2
After finished all carton for 1 and 2 then go to 1 and 3 after finished go to 1 and 4 then 2 and 3 then 2 and 4 and so on
Output will be like this
Empid Total
1 with 2 2050
1 with 3 200
1 with 4 500
1 with 5 2250
1 with 6 700
2 with 3 0
2 with 4 700
2 with 5 4300
2 with 6 1700
3 with 4 0
3 with 5 400
3 with 6 0
4 with 5 400
4 with 6 200
5 with 6 1900
Is it possible to decode based off a different column? I have a status column that i want to change the value of with a decode, but only if my date column has been populated.
So if status has a value of "New" and my date column is null, then i want it to stay new. If it is populated, i want my status to change to "released"
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 a range date partition table month wise which i want to truncate the partition and load for each month data i get from source.
How can i get the partion name based on data value.
e.g
source data
SALES_IDSALES_DT
101/02/2008
101/03/2008
101/04/2008
101/05/2008
target table has same structure as source but has more data but i want to delete only those which is coming from source if it is there in target and then load. so i want to get partition name so that i can truncate and load them.
I have noticed a very questionable phrase on an article updated in 2011: "Oracle SE may allow you to create a function-based index, but you must pay for an EE license to use FBI's." [URL] Is this true? I have tested a FBI on my SE and works just fine.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> create table t ( col varchar2(10));
Table created.
SQL> create index t_idx on t(upper(col));
Index created.
SQL> insert into t values('a');
[code]....
how to use different where caluse based on different in parameter. say I pass 1,2 as parameter if it is 1 then the where condition > 10 else if it is 2 then the where condition <5 the where conditions are placed in cursor for loops.
View 2 Replies View RelatedI have this record set coming from two tables.
cursor c1 is select cust_id from tab1 where login=in_login
union
select cust_id from tab2 where login_id = in_login;
in second step; I need to process and filter these ids based on some conditions.The final filtered record set I need to return as Out cursor;
How can I use the processed data to built the output ref cursor ?
I would like to know, how to:
1) search for a given value in a row, and
2)in that row, identify the name of the column that contains the value
Search scope is only a single row.
I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.
View 8 Replies View Relatedi have a table where there are codes of length 6 or length 12 some times, i need to add the summary of amount based on two different types of codes, one is adding the distinct codes which are of 6 char long and other sum will be based on from substr(7) till last.
create table strings ( strings_var varchar2(12),strings_amt number);
insert into strings (strings_var,strings_amt) values ('02.01',10 );
insert into strings (strings_var,strings_amt) values ('02.01_A11111',15);
insert into strings (strings_var,strings_amt) values ('02.02_A11111',15);
insert into strings (strings_var,strings_amt) values ('03.01_B11111',15);
insert into strings (strings_var,strings_amt) values ('03.02_B11111',15);
the output which i want is as below.
string value
'02.01' 10
'A11111' 30
'B11111' 30
I have a table with below structure
Id - Desc -repeat_times
1a - andy - 3
2b - santa - 5
I want to write a sql query which outputs two columnns like below. the particular row to be repeated as per the repeat_times column.
1a-1 andy
1a-2 andy
1a-3 andy
2b-1 santa
2b-2 santa
2b-3 santa
2b-4 santa
2b-5 santa
I have tried but am failing..
How to write a single query for the below scenario?I got only start date column, but I need show the end date based on next (start date-1). For the last row, need to show the same date in the end date column.
For Example:I have the data as shown below,
Start date End date
1-Jan-10
3-Mar-10
3-May-10
5-Jul-10
1-Aug-10
I wanted to output as shown below
Start date End date
1-Jan-10 2-Mar-10
3-Mar-10 2-May-10
3-May-10 4-Jul-10
5-Jul-10 31-Jul-10
1-Aug-10 1-Aug-10
I am trying to get records from a table based on date column. I should retrieve records if the date column is NULL
and if it is not null ,i shud get the records of MAX date.
How would i do this. Below is my query,by which i can get records based on either maxdate or Null date
select c.id, c.status,c.u_gr_code,
from
(
select id, status,u_gr_code,date_col,
max(record_valid_from) over (partition by entity_user_gr_id, status_id) max_date
[code].........
Actually I need update statement some thing like below scenario...
I explained my problem cleary in attached PDF since i cannot format text well here..
I have a table t
CREATE TABLE T
(ID NUMBER(4),
NAME VARCHAR2(40))
INSERT INTO T VALUES (1,'JAMES');
INSERT INTO T VALUES (1,'DOLLY');
INSERT INTO T VALUES (2,'MICHEAL');
INSERT INTO T VALUES (2,'FLASH');
INSERT INTO T VALUES (3,'JAMES');
INSERT INTO T VALUES (3,'MARY');
INSERT INTO T VALUES (4,'JAMES');
INSERT INTO T VALUES (4,'DOLLY');
INSERT INTO T VALUES (5,'JAMES');
INSERT INTO T VALUES (5,'DOLLY');
INSERT INTO T VALUES (6,'JAMES');
INSERT INTO T VALUES (6,'MARY');
SELECT * FROM T ORDER BY 1
ID NAME
1 JAMES
1 DOLLY
2 MICHEAL
2 FLASH
3 JAMES
3 MARY
4 JAMES
4 DOLLY
5 JAMES
5 DOLLY
6 JAMES
6 MARY
each 'ID' has two values always. I want to rank the data based on same pair 'name' in an 'ID'
for example, my desired output is:
ID NAME RANK
1 JAMES 1
1 DOLLY 1
2 MICHEAL 1
2 FLASH 1
3 JAMES 1
3 MARY 1
4 JAMES 2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND DOLLY ARE IN THE SAME 'ID'
4 DOLLY 2 -----> SAME AS ABOVE
5 JAMES 3 ---> THAT IS RANK 2 BECAUSE THIS IS THE 3RD TIME JAMES AND DOLLY ARE IN THE SAME 'ID'
5 DOLLY 3 -----> SAME AS ABOVE
6 JAMES 2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND MARY ARE IN THE SAME 'ID'
6 MARY 2 -----> SAME AS ABOVE
I want the output in exactly above format.
Query result gives out put like in the following order
CITY NAME
CHENNAI
DELHI
LONDON
RIO DE GENARO
How to get output in the following sort order
LONDON
CHENNAI
DELHI
RIO DE GENARO
i dont want to hardcode the column values ..it will be great if i get option to generate dynamically.
The following query gives error.
select count(*)
from invoice
where inv_id in (SELECT inv_id FROM invoice_hist WHERE to_char(timestamp) between TO_char('05/12/2010 18:22:00', 'MM/DD/YYYY HH24:MM:SS') and
TO_char('05/21/2010 18:22:00', 'MM/DD/YYYY HH24:MM:SS'));.
Here timestamp is of type DATE in the invlice_hist table.
What change is needed?
can we make a search based on column , actually i have one requirement where the data is stored in column as comma seperated values like below
arif,123455,123456,77777
sait,123455,123456,66666
tomm,666666,123455,123456
I want to filter only 123455 and 123456 from this list, is it possible to get a range as i want to use it as report parameters , i can do it by using like operator as below
create table prd_mast ( prd_cd varchar2(12), prd_desc varchar2(3000));
insert into prd_mast values ('1','arif,123455,123456,77777');
insert into prd_mast values ('2','sait,123455,123456,66666');
[Code]....
I want to create an XML based on xsd and then i want to validate the same xml with xsd ,
View 1 Replies View RelatedI have wells that can have multiple statuses (one record for each status). I need to create a column to display a Y or N based on whether or not a given well has only certain types statuses. If all records for a given well are Susp and/or Abd, then I want to display a Y. If the well records include Susp or Abd, but also have other statuses (or do not even have a status of susp or abd) then I want to display a N.
So:
Well Status ident
12345 SUSP Y
12345 SUSP Y
12345 ABD Y
98765 SUSP N
98765 PROD N
98765 ABD N
45678 SUSP Y
45678 SUSP Y
ASDFG ABD Y
ASDEG ABD Y
TTTTT PROD N
TTTTT TEMP
I need to pick a value based on range like if the range is as below
[code]
if value =2000 then its 2000
elsif value >=2001 and value <=2499 it should be 2000
elsif value =2500 then 2500
elsif value >=2501 and value <=2999 it should be 2500
elsif value = 3000 then it should be 3000
[code]
Like this i need to pick a value by hardcoding this range and this look cumbersome in my program , is there a simple way to substitute this entire thing by passing one single value and getting one single value using a command or builtin.
I have a table which sees a lot of use for queries
CREATE TABLE CASE_STAGE
(
ID NUMBER(9) NOT NULL,
STAGE_ID NUMBER(9) NOT NULL,
CASE_PHASE_ID NUMBER(9) NOT NULL,
"CURRENT" NUMBER(1) NOT NULL,
--and other columns
)
ID is a primary key
CASE_PHASE_ID is a foreign key
"CURRENT" should only ever have values of 0 or 1. When it has a value of 1 it is unique for that CASE_PHASE_ID
What I have tried that doesn't work is
create unique index case_stage_F_IDX1 on case_stage("CURRENT", case_Phase_id) which gives me
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
What is the correct syntax, something like ("CURRENT"=1,case_phase_id) seems right but fails with an error about a missing bracket. Do I need to use a CASE statement here?
I have a table in some db called retailer. I want to generate an email based trigger. What i want to achieve is as follows
I want to generate an email based trigger. What i want to do is that i should store the current retailer id which is until this point and then on each further insertions in retailer id i want that some trigger or script is running in background which is storing the retailer id. When the retailer id advances by 45 then a trigger should be generated saying that retailer id has advanced to 45 numbers from this date.
I was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:
HOST
-----
emerald.test.com
ruby.test.com
diamond.test.com
I would like to only return ('emerald', 'ruby', 'diamond') by getting all data leading up to the first '.' character.
I have partitioned the table based on field.But when I am selecting by Partition or by the field I am getting Explain plan as Table Access full.I am pasting the sql and Explain Plan here. The table has two partition by BOOKING_DT_WID. One less than 20100801 and other less than 99991231.
CODESELECT * FROM WC_BOOKING_SALESREP_F WHERE BOOKING_DT_WID >= 20100801;
SELECT * FROM WC_BOOKING_SALESREP_F PARTITION(SALESREP_LESS1_99991231);
Here is the Explain Plan for the same.
CODESELECT STATEMENT ALL_ROWSCost: 1,501 Bytes: 293,923,641 Cardinality: 809,707
4 PX COORDINATOR
[code]....
How do I know if the sql is doing partition prune.
I am attempting to update a single field in one table based on a select from two tables. However, I am receiving the following error.
ORA-00933: SQL command not properly ended
The sql I am using is as follows:
update PS_TRNS_CRSE_DTL
set RQMNT_DESIGNTN = 'TRN'
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
where (SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.MODEL_NBR, A.ARTICULATION_TERM, A.TRNSFR_EQVLNCY_GRP, A.TRNSFR_EQVLNCY_SEQ, A.TRNSFR_STAT, A.GRADING_BASIS, A.RQMNT_DESIGNTN, B.STUDY_AGREEMENT
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
[code]......