SQL & PL/SQL :: How To Select Only Records That Have Multiple Values For A Column

SQL & PL/SQL :: Select Multiple Values Into Single Column

Oct 5, 2011

I have following tables with data as under:

table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9

I need to generate output as :

column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null

Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?

View 5 Replies View Related

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 :: Displaying Multiple Row Values As Multiple Column And Row Values

May 4, 2010

find the Test Case below.

--Creation of Table

create table tb1
(ID number(4),
event varchar2(20),
vdate date);

--Inserting Values into the Table.

INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')

[Code]...

--Selecting data from Table.

SELECT * FROM TB1;

ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10

10 rows selected.

how can i display the data as below format using Oracle 9i SQL.

IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10

View 4 Replies View Related

Forms :: How To Select Multiple Records

Feb 23, 2012

I'd want to select multiple records in my multi-record block. I'd also want to do that with checkboxes. When the user clicks on a particular checkbox, that should be selected and whatever record the user wants to check it should be added to selected records.

View 39 Replies View Related

Select Records With Same Values Of Field?

Oct 9, 2007

there are some data in the table que_history (seqnbr is the key), e.g.

SEQNBR DN SL_TIME
20070927003668 (024)2272 AD182040 2007-9-27 15:15:00
20070928001343 (024)2272 AD182040 2007-9-28 9:55:14
20070928001624 (024)2272 AD182040 2007-9-28 10:30:06
20070928000910 (024)25672 AD000002 2007-9-28 9:06:59
20070928001288 (024)25672 AD000002 2007-9-28 9:49:13
20070923003834 (024)2585 AD210076 2007-9-23 17:15:13
20070923003890 (024)2585 AD210076 2007-9-23 17:23:54
20071001001593 (024)2589 AD000018 2007-10-1 11:54:39
20071003002814 (024)2589 AD000018 2007-10-3 16:53:52
20070923003320 (024)8831 AD000110 2007-9-23 15:24:39

I wanted to use this SQL to get the records ( dn is the same and the sl_time's interval is 600minutes) .

select A.* from que_history A,que_history B
where A.dn=B.dn and A.seqnbr<>B.seqnbr
and (A.sl_time-B.sl_time)*24*60 between -600 and 600
order by A.dn;

but the result is not the right.

View 3 Replies View Related

SQL & PL/SQL :: Inserting Multiple Records With CLOB Using Select

Oct 11, 2013

I'm updating a large piece of legacy code that does the following type of insert:

INSERT INTO foo_temp
(id, varchar2_column)
SELECT id, varchar2_column
FROM foo;

We're changing varchar2_column to clob_column to accommodate text entries > 4000 characters. So I want to change the insert statement to something like:

INSERT INTO foo_temp
(id, clob_column)
SELECT id, clob_column
FROM foo;

This doesn't work, since clob_column stores the location of each text entry, rather than the actual content. But is there some way that I can achieve the insert with one call to a select statement, or do I need to select each individual record in foo, open the clob_column value, read it into a local variable and then write the content to the matching record in foo_temp?

View 2 Replies View Related

SQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column

Jul 19, 2013

CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......

actual output of the below query, but i want to display in different way

select * from type;

C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E

The expected output should be like this below, how to write a query or which built in function used to get the below result,

Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M

View 4 Replies View Related

SQL & PL/SQL :: Multiple Values Of RULES Tables To Find Records Containing A String Format

Sep 4, 2013

Insert into PROFILE
(INSTANCE, PROFILENAME, USER_DATA, UPDATE_DATE)
Values
(138, 'Test A', 'SRC!-1,ARCHIVE_OPT!-1,DATE_FIELD!155,DATE_RULE!1,DISTINCT!1', TO_DATE('01/20/2005 13:35:33', 'MM/DD/YYYY HH24:MI:SS'));
/
Insert into RULES
(ID, NAME)
Values
(155, 'DATE_TEST');

I want a code something of this sort:

select profilename from PROFILE where user_data like '%DATE_RULE!115%';

Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?

View 5 Replies View Related

SQL & PL/SQL :: Multiple Values In One Column?

Nov 20, 2010

i'm trying to put more than one value in one column (by putting value '26','63' in column), so i have next problem:

1. Query select cr_pjid from acc_users where username='ACCBTPS121' give result
CR_PJID
---------
'26','63'

2. Query select * from acc_accbtp_nova_view where ed_id=2 and to_char(pj_id) in ('26','63'); return 186 rows.

3. Query: select * from acc_accbtp_nova_view where ed_id=2 and
to_char(pj_id) in (select cr_pjid
from acc_users
where username='ACCBTPS121'); doesn't return any row...

View 8 Replies View Related

SQL & PL/SQL :: Multiple Values In Column

Jan 10, 2013

Most of the code is working properly except when it come to a person with more than one email type in the table:

Below is the

select emal_pidm, emal_email_address, emal_emal_code,
case
when emal_emal_code = 'PER'
and lag(emal_emal_code,1,'?') over (partition by emal_pidm order by emal_email_code) = 'EMPL'
then emal_email_address
[code]......

Below is Sample data for testing

INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1024069, 'emmaus.ferdinand@xxxx.edu','EMPL');
INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1024069, 'emfer1@xxxx.edu','PER');
[code].......

The attachment is what the output should look like.

View 5 Replies View Related

SQL & PL/SQL :: How To Get Multiple Column Values In A Row

Oct 30, 2012

Here is my requirement..... I am pretty new to PL/SQL.

This is the procedure

CREATE OR REPLACE PROCEDURE ABCPROC.SP_ABC
(
XML IN CLOB,
P_refcursor OUT SYS_REFCURSOR
)
AS
BEGIN
[code]........

For each account number (I_AC), we have multiple rows in ABC_SVC table. I want to return these values in the refcursor. The issue with above SQL program, the row is returning only when the account(I_AC) have values for all the products 100,101,102. If the row does not exist for one account, then the account row is returned with other products.

I want my output should be in the below format

ACCOUNT_NUMBER COMMISSION CONSUL CONTRA
1YYN
2NN
3N

View 4 Replies View Related

SQL & PL/SQL :: Select Records By One Column?

May 7, 2012

By select I got records (see table below) and I need to make select where result will be just one row with all values by payer_flag=Y.

There is a table:

CREATE TABLE XXX_MAPE_CC
(
CK_CUSTOMER_CODE VARCHAR2(24 BYTE),

[Code].....

And result should look like:

--------------------------------------------------------------------------------------------
ck_customer_code | invoice_media | personal_no | cnt_active | cnt_deactive | payer_flag
--------------------------------------------------------------------------------------------
6.107441.10 | electronic | 0 | 663 | 128 | Y
---------------------------------------------------------------------------------------------

View 6 Replies View Related

SQL & PL/SQL :: Check If Multiple Column Values Are Same?

Sep 16, 2011

I have a table as follows

create table teststr (indname varchar2(20),
counter1 number,counter2 number,counter3 number,counter4 number);
insert into teststr values('a',10,20,30,30);
insert into teststr values('b',10,20,5,3);
insert into teststr values('c',2,4,5,2);
insert into teststr values('d',1,2,3,4);
insert into teststr values('e',4,5,4,4);

Now i need the output if any of the column values are same.

output should be

select indname from teststr where counter1=counter2
or counter1=counter3 or counter1=counter4
or counter2=counter3 or counter2=counter4
or counter3=counter4

a
c
e

Is ther any other way to write the query instead of the numerous or conditions if i want to compare the column values in a table.

View 6 Replies View Related

SQL & PL/SQL :: Multiple Unique Values In One Column?

Aug 26, 2011

I have a problem with some tables in database.

Table has three columns: userid, pname, pvalue.

Userid has unique values, for example: 234, 123, 587, etc.

In the field pname, there is three possible values: MAC, IP, S/N.

So if I go like this:

select pvalue
from table
where pname = 'MAC';

i get the values of MAC.

If I go like this:

select pvalue
from table
where pname = 'IP';

i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.

View 11 Replies View Related

SQL & PL/SQL :: Column Values Concatenation From Multiple Rows

May 30, 2013

I am currently doing column values concatenation from multiple rows and then removing duplicates as in the following example:

SQL> select pid
2 , regexp_replace(ltrim(sentence), '([A-Za-z0-9]+,)1+', '1')
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
[code]....

but for some reason regexp_replace does not seem to work with clob and I get:

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"

Is it possible to eliminate duplicates in the model before the concatenation?

View 6 Replies View Related

SQL & PL/SQL :: Select Column Values Into Array

Sep 10, 2013

Is there any way in PL/SQL to select the values from all columns of a table record into an array?

For example:

C1|C2|C3
0 |1 |2

v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2

or

v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2

But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;

View 10 Replies View Related

SQL & PL/SQL :: Select Only Rows Where Certain Column Repeating Values

Mar 6, 2012

I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.

with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all

[Code]...

NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas

The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.

I need the sql syntax that would return this result set.

View 6 Replies View Related

SQL & PL/SQL :: Select Numeric Values From A Varchar Column

Feb 5, 2011

select numeric values from a varchar column

For Example:

select * from t1 ;

ID
----------
00300
ABCXY
04230
xyzab

i need to fetch only numeric values from column id

My output should be

00300
04230

View 8 Replies View Related

SQL & PL/SQL :: Use Records Returned By Query As Column Names In A Select?

Jul 3, 2011

is it possible to use the records returned by a query as column names in a select query.

select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%')
from v$database;
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

View 3 Replies View Related

SQL & PL/SQL :: Function To Select Column Values Separated By Comma?

May 23, 2012

I need to write a function which will take table name as input and should return all the columns separated by coma (,).

For example I have a table product as

PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001

My function should return the output as

100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001

Is there any inbuilt function available?

View 10 Replies View Related

SQL & PL/SQL :: Entering Values As Not Null Constraint To Column In Oracle Without Disturbing Old Records

Jul 1, 2013

previously i set null constraint to the column and creating some rows and need to change new entering values as not null constraint to the column in oracle without disturbing the old records. how can I do that.

View 5 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: Select First 40 Columns Without Giving All Column Names In Select Clause?

Mar 3, 2011

I have a table with around 80 columns. All i need is to select first 40 columns.

Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.

View 2 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column

Jul 25, 2013

From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?

View 2 Replies View Related

Application Express :: Hyperlink Column With Link Parameters As Region Column Values

Apr 10, 2013

On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.

I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.

So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:

a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?

The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.

---

"Application Express 4.2.1.00.08"

View 2 Replies View Related

Application Express :: Can Pass More Than 3 Column Values With A Column Link

Jun 19, 2012

I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?

View 3 Replies View Related

PL/SQL :: Multiple Rows To Multiple Column

May 6, 2013

I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name

TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31

I need a query which provides the below output

Desired Output:
======
First Second Third
11 21 31

I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));

This gives me the output

First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31

Is there any way to get the output in single line.

View 3 Replies View Related

Single To Multiple Records

Sep 18, 2013

I have a table as col_1 number,col_2 varchar2(10)

col_1 col_2
1 abcdefghijkl

what i want is i've to split

col_1 col_2
1 abcdefghij
1 kl

View 4 Replies View Related