SQL & PL/SQL :: Groups With Multiple Columns And Links

Nov 29, 2010

the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:

--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));

[Code]...

--expected output

group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E

My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links

Jan 2, 2013

how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?

View 4 Replies View Related

Data Guard :: Using HAIP To Bond multiple Links?

Oct 1, 2012

Is it possible to use the Oracle HAIP feature to bond multiple network links between Active and Standby database ?

Does this require a seperate license ?

Basically, I was looking for a solution for bonding the muliple WAN links that we have between the active and Standby database.

View 0 Replies View Related

Retrieve Info From Multiple Databases / Insert Into Central Database Via Database LINKS

Jun 10, 2013

I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.

However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:

DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;
[code]....

View 1 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

SQL & PL/SQL :: How To Update Multiple Columns

Dec 12, 2011

how to over come this error, because i need to update only 3 columns in the table and iam getting error when iam updating like this

update country1 set cname='japan','usa'
where cid=100,101

ERROR:ORA-01747,INVALID USER.TABLE.COLUMN,TABLE.COLUMN,OR COLUMN
SPECIFICATION

View 6 Replies View Related

SQL & PL/SQL :: Sort On Multiple Columns

Jun 16, 2010

how does sorting on multiple columns work

suppose my query is

select * from person order by first_name desc
and sys_person_id asc

this query works , but is this write way to sort on multiple column ?

View 12 Replies View Related

SQL & PL/SQL :: Order By Multiple Columns

Oct 9, 2013

I have a query to pull the first contact of students.

The table has all contacts like parent/guardian, friends family, emergency contact etc.

I would like to the first primary contact in this order,
1. initial contact, 2. same as student address and also have to be parents,
3. live with and also a parent, 4. parents 5. friends.

I don't know how to pull 2 and 3 . because it looks like it needs to concatenate the columns.

here is my initial query

Select Min(U2.Id) Keep (Dense_Rank First Order By U2.Initial_Contact Desc, U2.Same_As_Students_Address Desc,u2.lives_with DESC,U2.Guardian Desc)
From Contacts

how to achieve 2 and 3?

The table script is attached. All the above columns are 1 or 0.

View 3 Replies View Related

SQL & PL/SQL :: Multiple Queries Into Different Columns?

Jan 31, 2013

I am trying to validate a monthly report so was trying to write queries to get different criteria into one table. So my first query returns all the product,second query returns all the enrolled customers, 3rd query returns all the cancelled customers and 4th query returns all the newly enrolled for a month. Is there a way I can pass the first query results into 1st column, 2 query results into 2nd column, 3 query results into 3rd column and so on.

I tired writing the SQL several different ways and have spent a day on it and still cannot figure it out. I am using SQL Developer.

View 9 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

PL/SQL :: Multiple Columns In IN Clause?

Jun 19, 2013

select * from nrc_trans_descr where type_id_nrc=60013   -- it has 18 columns and i have hard coded 60013 for simplification here.60013 is derived from 3 other table Output is ( it can have many rows too.typically for each type_id_nrc there is one row ).
 
TYPE_ID_NRC  TRIGGER_STATUS  INSTALLMENT_TYPE_ID_NRC
---------------------------------------------------------------------
      60013              0                 61013                  
i have to pass TYPE_ID_NRC  and  INSTALLMENT_TYPE_ID_NRC to restriction_id column in a different table. currently i am doing like this 
select * FROM DISCOUNT_RESTRICTIONS WHERE discount_id in (12085,12086)
and (restricted_id in (  select type_id_nrc from nrc_trans_descr where type_id_nrc=60013)
or restricted_id in (  select installment_type_id_nrc from nrc_trans_descr where type_id_nrc=60013));

am using ORACLE 10GR2(solution for 11gr2 is welcome too) 

View 16 Replies View Related

SQL & PL/SQL :: Query With Multiple Columns

Dec 17, 2012

Table-Name

ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9

select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));

Result should be " Frog and Elephant and tiger"

Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )

Note:

tracking IDs may change up on error

View 2 Replies View Related

PL/SQL :: Multiple Columns Using Group By

May 20, 2013

Can i select multiple columns while using group by single column?

View 5 Replies View Related

SELECT DISTINCT On Multiple Columns

Sep 17, 2010

I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:

select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'

Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.

View 3 Replies View Related

Convert Rows To Multiple Columns

Aug 30, 2004

I have a table called N1

N1_no Srvarea_type_cd
1 P
1 P
2 C
2 C
2 C
3 I

Another table N2

N1_no srvarea_txt
1 ABCD
2 DEFG
3 XYZA

Can i get a query so that the data can be displayed in the following way ..

P C I
ABCD DEFG XYZA

View 3 Replies View Related

Multiple Columns Right To Left Orientation?

Sep 15, 2009

i have downloaded ireports latest version (3.6.0) for working with jasper reports (3.5.2) and creating reports;How can I change the orientation of a 4 columns report in order to generate the columns starting from the right side.I need to generate a multiple columns report in arabic and it should be done from right to left.

View 2 Replies View Related

Sub Partition By List On Multiple Columns

May 16, 2011

Can i partition by list on multiple columns? i am trying the following code, and it is returning an error. Is there a way round this?

CREATE TABLE
(...)
PARTITION BY LIST (col1)
SUBPARTITION BY LIST (col2)
SUBPARTITION TEMPLATE (
SUBPARTITION DETAIL VALUES ('DETAIL')
SUBPARTITION ROLLUP VALUES ('ROLLUP'))
(
PARTITION RT VALUES('RT')
)

View 1 Replies View Related

SQL & PL/SQL :: Using Cursor To Update Multiple Columns

Oct 22, 2011

I need to write a script which copies 4 col data from one table to another table. there are three tables

cwat_curr_mst and cwat_assigned_customer and cwat_assignment_mst.
Cwat curr mst has PK curr_id and cwat_assigned_customer has PK assignment_id.
Also cwat_assigned_customer has customer_id.
In cwat_assignment_mst has Curr_id and Assignment_ID.

cwat_curr_mst and cwat_assigned_customer tables has 4 cols in common
they are
ASRT_SNM_NO, SNM_NO, FLORIDA_NO, CBRN_NO.

So from curr_mst all these 4 cols data needs to come/copy into cwat_assigned_customer.

View 20 Replies View Related

SQL & PL/SQL :: Formatting Output Into Multiple Columns?

Apr 16, 2013

as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.

We must show the output as evidence of doing the work.

Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!

BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/

View 5 Replies View Related

SQL & PL/SQL :: UNION ALL Output In Multiple Columns?

Nov 26, 2012

Following is my table structure:

14:54:17 PYMTPRODIAT@UCS43 > select * from DGTEST;

ID AMOUNT DI
---------- ---------- --
1 50 D
2 50 D
3 20 D
4 60 C
2 60 C
3 20 C

now, I want the output in the following format.what could be the easiest way.. I need the output in one query,,

ID D_Net_Amount C_Net_Amount
--- ------------- --------------
1 50
2 50 60
3 20 20
4 60

View 2 Replies View Related

SQL & PL/SQL :: Split Multiple Columns Into Rows

May 2, 2012

I have data like :

ID NAME1 NAME2 NAME3
JJ AD MED
VI TIBO PH TIBO

I want output like

ID NAME
JJ AD
JJ MED
VI TIBO
VI PH
VI TIBO

View 4 Replies View Related

PL/SQL :: Multiple Indexes Using Same Columns On One Table?

Oct 4, 2012

I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.

The back end database is Oracle 10g.

Here's what I have tried so far, needless to say, unsuccessfully.

Alter Table MyTab
Add Constraint c_1 primary key (a, b)
Using Index (Create index mytab_idx on MyTab(a, b))
Using index tablespace results_index

So my question are:

1. is this is possible? if so, what is the correct syntax.
2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.

View 4 Replies View Related

Group By Multiple Columns / Count And Then Find A Max

May 24, 2008

I have three tables,let's say

table stores
sid | store_name
1 | one
2 | two
3 | three

table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree

table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2

and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)

View 5 Replies View Related

Forms :: How To Create A Table With Multiple Columns

Dec 27, 2011

to create a table using forms with multiple columns

View 7 Replies View Related

SQL & PL/SQL :: Adding Multiple Columns To Table Type?

Mar 1, 2013

I have requirement on table type. without using bulk or %rowtype is there any possible to create table type with two or more columns.

I got with single column,but I am unable to create with multiple columns.

DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
T T1;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100 LOOP
CNT:=CNT+1;

[code]....

View 7 Replies View Related

SQL & PL/SQL :: Comparing Multiple Columns According To Rows From Same Table

Aug 25, 2011

I am new to oracle, I have request to build a query,

we have table that generates data from 7am to 20pm for eavery hour it generates 4 rows and has 43 session values as 43 columns.

Now i want to find for every hour which is the hights session value at what time. in one hour it runs four times like 7, 7:15, 7:30 and 7:45 and each row has date, time and 43 session columns in table...

View 12 Replies View Related

Forms :: How To Use Multiple Buttons To Sort Columns

May 6, 2010

I am maintenancing a form which I have to add buttons as headers that will sort each column's data either in desc or asc order when the user click each button. How is this done? I need to know what built-in function that will closely do this or cod.

View 19 Replies View Related

PL/SQL :: Merge / Update With Multiple Tables And Columns

Sep 6, 2013

I am trying to update multiple columns from one table based on the results of another table So I have 3 tables as follows 

HISTORYSUMM_SNAPADM_CHOICE 
My SQL code is loosely SELECT SUM(H.HIS1),
SS.SNAP1,
AC.ADM1FROMHISTORY H,
SUMM_SNAP SS,ADM_CHOICE ACWHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2GROUP BY SS.SNAP1,
AC.ADM1

 This works, and I am able to SUM the column as I need with the right numbers.  I altered the SUMM_SNAP table and now I want this summarized column to be in the table I tried using UPDATE, but there is no FROM clause to let me do the table join/group by 

UPDATE SUMM_SNAPSET SUMM_SNAP.SNAP3=SUM(H.HIS1)FROMHISTORY H,
SUMM_SNAP SS,ADM_CHOICE AC
WHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2
GROUP BY SS.SNAP1, AC.ADM1 

The above is obviously wrong - but just trying to show whatI was thinking What would be the best method to get the numbers from the SUM into a table?

View 5 Replies View Related

PL/SQL :: Delete Dupes Based On Multiple Columns

Dec 28, 2012

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I want to delete dups from a table based on 3 columns

with sample_table as (
select '101' as ID1, '201' as ID2, '4' as weight  from dual union all
select '101' as ID1, '201' as ID2, '5' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
[code].........                     

Desired Output

with sample_table as (
select '101' as ID1, '201' as ID2, '5' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
select '110' as ID1, '215' as ID2, '9' as weight  from dual
)
select * from sample_table

View 5 Replies View Related







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