SQL & PL/SQL :: Classification Based On Age And Nationality

Oct 7, 2012

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.

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select Whole Row Based On Max Value?

May 10, 2013

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]....

View 3 Replies View Related

Max Row Based On Value In Column?

Mar 3, 2011

How to get the max row based on a value in a column

View 1 Replies View Related

Sessions Based On SQL ID

Aug 31, 2012

One 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 Related

PL/SQL :: Sum Based On Group 2

Nov 26, 2012

I 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

View 2 Replies View Related

Decode Based On Different Column

Jul 24, 2007

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"

View 5 Replies View Related

SQL & PL/SQL :: Field Name Based On Calculation

Apr 19, 2010

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?

View 19 Replies View Related

SQL & PL/SQL :: Partition Name Based On Data?

Oct 9, 2013

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.

View 11 Replies View Related

SQL & PL/SQL :: Using Function-based Indexes In SE?

Mar 29, 2012

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]....

View 11 Replies View Related

SQL & PL/SQL :: Different Where Clause Based On In Parameter

Aug 30, 2011

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 Related

SQL & PL/SQL :: Out Ref Cursor Based On Record Set

Sep 24, 2010

I 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 ?

View 4 Replies View Related

SQL & PL/SQL :: Identifying Column Name Based On A Value

Apr 11, 2011

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.

View 7 Replies View Related

SQL & PL/SQL :: Top Row Based On Multiple Columns?

Dec 1, 2011

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 Related

SQL & PL/SQL :: Grouping Based On Condition

Aug 4, 2013

i 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

View 4 Replies View Related

SQL & PL/SQL :: Rows Based On Column

Jun 25, 2012

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..

View 1 Replies View Related

SQL & PL/SQL :: Show End Date Based On Next?

May 19, 2010

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

View 10 Replies View Related

SQL & PL/SQL :: How To Get Records Based On Max Date

May 18, 2011

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].........

View 10 Replies View Related

SQL & PL/SQL :: Update Table Based On Another?

Oct 20, 2010

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..

View 5 Replies View Related

SQL & PL/SQL :: Ranking Based On A Group?

Jun 29, 2011

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.

View 3 Replies View Related

PL/SQL :: Sort Column Based On Value

Oct 17, 2012

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.

View 9 Replies View Related

SQL & PL/SQL :: Time Based Select

May 24, 2010

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?

View 9 Replies View Related

SQL & PL/SQL :: Search Based On Column

Nov 18, 2012

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]....

View 3 Replies View Related

XML DB :: Create XML Based On Xsd And Then Validate?

Jul 31, 2013

 I want to create an XML based on xsd and then  i want to validate the same xml with xsd ,

View 1 Replies View Related

PL/SQL :: Display Value Based On Condition

Dec 13, 2012

I 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

View 4 Replies View Related

SQL & PL/SQL :: Pick A Value Based On Range

Mar 10, 2012

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.

View 2 Replies View Related

Unique Function Based Index

Jun 27, 2011

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?

View 1 Replies View Related

How To Generate Email Based Trigger

May 9, 2011

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.

View 1 Replies View Related

Splitting A String Based On Delimiter?

Feb 19, 2008

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.

View 1 Replies View Related

Partitioned Table Based On Field

Nov 27, 2012

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.

View 1 Replies View Related

Updating Field Based On Two Tables

Mar 19, 2007

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]......

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved