Query To Split Records Based On Values From Another Table?

Feb 20, 2012

I have a table with following values in a column

Table A

col1
10
35
20
25

I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.

Table B- 90 records (10 + 35 + 20 + 25)

Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,

with first group having start value -1 and end value -10

second group - start value -11 and end value-45

third group - start value -46 and end value -65

fourth group - start value - 66 and end value-90

one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.

note that the values in Table A is dynamically changing, so not able to hard code values.

View 2 Replies


ADVERTISEMENT

PL/SQL :: Select Records Based On First N Distinct Values Of Column

Sep 25, 2012

I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.

eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10

query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10

query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..

How to write a query for this inside a loop.

View 5 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

SQL & PL/SQL :: Update Based On Other Table Records?

Nov 4, 2009

CREATE TABLE "SCOTT"."SEATALLOTMENT"
("YEAR" NUMBER(4,0),
"COLLEGECODE" CHAR(4 BYTE),
"COURSECODE" CHAR(3 BYTE),

[Code].....

Now i want to UPDATE reducing the AVAILABLE column by 1 in COURSESEATS table based on common columns collegecode,coursecode for a ROW inserted into SEATALLOTMENT table ,i am confused to what approach i have to follow whether its a procedure or a trigger

CASE:

Here in this case as i insert a row with krcl,cse as college code and course code respectively into seatallotment table the available column in courseseat table for the respective row with mentioned common column must become 59 from 60

View 5 Replies View Related

SQL & PL/SQL :: Partition Creation Based On Other Table Values

Feb 12, 2013

i am trying to create Procedure which will create the partitions based on the other table date values one per each day.

CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
AS
V_PART_NM VARCHAR2(20);
V_PART_CNT NUMBER;
V_DATE DATE;
V_SCHEMA_NAME VARCHAR(15);

[Code]..

It is not creating the partitions and even not giving any errors.

View 4 Replies View Related

SQL & PL/SQL :: View With Column Based On Row Values Of Another Table

Apr 13, 2011

I have table with values :

PROV_IDMEASURE_IDPERCENTAGE
Z0000221P114 45
Z0000135P115 68

For the column all possible values are ( P102,P101,P103 etc toP124). I want to create a view ( if possible ) from the above with data output as :

PROV_ID P101 P102 P103 ............................P124
z000234 23 45 60 72

basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.

View 2 Replies View Related

PL/SQL :: Updating Count Data Based On Records In Another Table

Nov 6, 2013

I have 2 tables

Table 1Name Item   DateJon  Apples  06/11/2013 00:30:00 hrsSam  OrangesNish Apples  
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon  Apples 8 

I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar

View 2 Replies View Related

PL/SQL :: Update Vs Merge - Updating One Table Based On Values In Another

Jun 26, 2013

I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data 

MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN  UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT; 

I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?

View 4 Replies View Related

PL/SQL :: Updating CSV Values In Oracle Table Based On Condition

Aug 27, 2013

I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.

View 1 Replies View Related

SQL & PL/SQL :: Filtering Dataset Based On Records Returned By Table Join

Nov 10, 2010

I need to work on this requirement.

There are FOUR tables ( T1 , T11 & T2, T22) ALL store order information.

One of four conditions are possible for each Supply Reorder Number:

•Both table queries return no records

oPopulate all the output fields with nulls

•T1 returns a record, but T2 returns no records

oPopulate output fields with values from the join of T1 and T11.

•T1 returns no records, but T2 returns one record

oPopulate output fields with values from the join of T2 and T22.

•T1 returns a record, and T2 returns a record

oIf the latest order is in T1, then populate output fields with values from the join of T1 and T11.

oIf order dates are equal from both join results, then populate output fields with values from the join of T1 and T11 .

oIf the latest order is in T2, then populate output fields with values from the join of T2 and T22.

How do we filter the dataset based on result of table join ?

View 1 Replies View Related

PL/SQL :: How To Split Column Data Based On Identifier

Oct 3, 2012

I am using oracle database.

I am having data in this format in my column 1234~2345~3456~4567.

I need a query to split the data in the column based on the identifier '~',so that i can pick out the value after the second occurrence of the identifier.

View 3 Replies View Related

SQL & PL/SQL :: String Split / Replace Based On Character Length

Jun 19, 2013

notes column having 2000 characters max, i want my string output based on 35 characters, ya i need to replace tag after 30 characters in the string.. I need out put as "hi hello how are you doing out there, similar i need to calculate the sting length and have to split it 35+35+35..

This i tried

select substr(note,1,(instr(note, ' ',35)))||'
'||substr(note,instr(note, ' ',35),(instr(note, ' ',35)))notes from test

View 7 Replies View Related

Application Express :: How To Create Radio Groups Dynamically Based On Records In A Table

Nov 29, 2012

below requirement..

We have certain records like SQL, PL/SQL, Reports, Forms, OAF etc in a table. We wanted to capture rating for each of these criteria. So we want a form to be displayed dynamically..

SQL 1 2 3 4 (1,2,3,4 represent radio buttons)
PL/SQL 1 2 3 4
Reports 1 2 3 4
Forms 1 2 3 4
OAF 1 2 3 4

If we add another row, XMLP in that table additional radio button should be displayed automatically..

View 4 Replies View Related

SQL & PL/SQL :: Split Csv To Multiple Records

Jun 18, 2013

I have a small requirement...

Create table temp_a (source_code varchar2(100), target_code varchar2(1000));

Insert into temp_a values ('1','002.0 AND 002.9');
Insert into temp_a values ('2','729.90 AND 079.99 AND 002.9');

Output :

1 002.0
1 002.9
2 729.90
2 079.99
2 002.9

So, once we get the output, it needs to be joined to another table. I did Google search, but most of them are retuning collections / arrays as output. Not sure how I join the collection with the table.

create or replace function splits
(
p_list varchar2,
p_del varchar2
) return split_tbl pipelined
is
l_idx pls_integer;
[code].......

View 3 Replies View Related

SQL & PL/SQL :: How To Split Values From A Column

Jul 14, 2010

How to split the values from a column?

For example: i had table T1 with below structue

Table T1

userid
-------
sandy1234
raj6785
Andrew12367
Michael56098

i need output like below structure

Nameid
---- ----
Sandy1234
Raj6785
Andrew12367
Michael56098

View 5 Replies View Related

PL/SQL :: Split String Values

Feb 28, 2013

I have a column in a table that contains a string seperated by .

e.g.

IT.HARDWARE
IT.APPS
IT.SOFTWARE

I would like to split the two values out on two columns e.g.

Column1 - IT
Column2 - Hardware
etc.

View 3 Replies View Related

PL/SQL :: Split Column Values

Mar 11, 2013

I have a requirement in which the amount need to be split in to multiple period.

For example if there amount of 3000 and start and end date is 01-jan-2013 and 31-mar-2013 then the output of the query should be

Name Start Period End Period Jan-13 Feb-13 Mar-13
------------------------------------------------------------------------------
XXX 01-JAN-13 31-MAR-13 1000 1000 1000

How to achieve this.

View 2 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

SQL & PL/SQL :: Split String Function For Large Number Of Records?

Aug 6, 2010

I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function

Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)

CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED

[code]....

But here I am facing 2 problems.

1. The function is not accepting a large string & I am getting the error

ORA-01704: string literal too long

2. The function is going for an infinite loop.

View 10 Replies View Related

SQL & PL/SQL :: Query To Find Values Are Not Present In Table

Nov 28, 2010

I have table called test script for table is given below

create table TEST
(
col1 Number
);

[Code]...

For these values Query is not returning values(3 and 4). So i want generic query to get this result. I am working on it but not able to generate proper query.

View 2 Replies View Related

PL/SQL :: Create Query To Compare Values From Same Table

Jul 10, 2012

Suppose you have the below table, same ID's occur for same month as well as different month

ID Month Value
--------------------------------------------------------------
226220      201203     100
1660      201204     200
26739      201204     1010
7750     201205     31.1

I need a query to determine the below laid result

ID Month Prior_month_value Prior_Month Value
----------------------------------------------------------------------------
1234 201203 10 201201 100
3456 201206 56.1 201204 78

View 10 Replies View Related

PL/SQL :: Query To Split Field Value

Jul 31, 2013

In employees table I am having employee_name column data as follows 

Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean  Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->

How to achieve this by SQL query.

View 3 Replies View Related

SQL & PL/SQL :: Insert Records From A Select Query Into Temporary Table?

Mar 21, 2013

We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?

It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.

Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.

View 16 Replies View Related

SQL & PL/SQL :: Query To Store Results - Update Values In Another Table?

Feb 22, 2010

I have a query like this -

SELECT
FIELD_A,
FN_FUNCTION(CARVE_ID, 1) FIELD_B,
FN_FUNCTION(CARVE_ID, 2) FIELD_C,
FN_FUNCTION(CARVE_ID, 3) FIELD_D,
FN_FUNCTION(CARVE_ID, 4) FIELD_E,
FN_FUNCTION(CARVE_ID, 5) FIELD_F,
FN_FUNCTION(CARVE_ID, 6) FIELD_G
FROM TB_CARVE;

When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.

Tried using Materialized view, its again the same the refresh takes 2 hours.Basically here, what I am trying to do is the data from the above query is used to update the values in another table.What ever the procedure I am trying it takes 2 hours.

View 6 Replies View Related

Multiple Table Query Returning Duplicates With Wrong Values

Oct 20, 2012

These are the tables I'm working with:

SQL> desc custinfo;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID VARCHAR2(4)
CUSTNAME VARCHAR2(18)
CUSTADR VARCHAR2(12)
CUSTCITY VARCHAR2(10)
CUSTSTATE CHAR(2)
CUSTZIP VARCHAR2(5)
CAPACITY NUMBER(3)
HOUSECODE VARCHAR2(2)
[code]...

I don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.

For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.

View 4 Replies View Related

SQL & PL/SQL :: Query To Split Character String Using XQUERY

Aug 5, 2010

I am using this query to split numeric values i.e ('1,2,3,4,5,6')but when i am trying with char value i.e ('a,b,c,d'), its not working.

select id
FROM employee e
WHERE e.id IN
( SELECT TO_NUMBER(xt.column_value)
FROM XMLTABLE('1,2,3,4,5,6') xt );

View 6 Replies View Related

SQL & PL/SQL :: Query To Split Period Into Date Ranges?

Apr 10, 2012

I have TableA which has data like below.

ID NAME CRT_DTE
1 AB 03/05/1992
2 EF 15/04/1995
3 CD 20/08/1995
4 GH 01/01/1999
5 UV 08/07/2001

[code]....

I want a query which splits the total time period (from min crt_dte to max crt_dte) into year ranges.For eg, lets say a range of 5 years then I need to get results like below.

start_dte end_dte
---------- ----------
03/05/1992 03/05/1997
03/05/1997 03/05/2002
03/05/2002 03/05/2007
03/05/2007 09/03/2012

how to write this query.

View 6 Replies View Related

SQL & PL/SQL :: External Table Query (compare Number Records In File With External Table)

Jan 23, 2013

I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.

I have attached the code that creates the external table and populates it.

View 5 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 :: Fetch Records Based On 2 Status

Jan 25, 2013

create table test_exp (oid number, ioid number, status varchar2(20));

Below are the insert statements.

Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 100, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 101, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 102, 'CANCELLED');
Insert into TEST_EXP (OID, IOID, STATUS) Values (2, 103, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (3, 104, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 105, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 106, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 107, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 108, 'CANCELLED');
[code]...

Now my problem is we should fetch the data based on the below rules

If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records
If an OID has only 1 of these status, then ignore the same
If an OID has none of the 2 status, then ignore the same.

The expected output will be like below:

OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW

View 4 Replies View Related







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