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


ADVERTISEMENT

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

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 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 :: 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 :: 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 :: How To Select Only Records That Have Multiple Values For A Column

Nov 21, 2011

I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:

idCoupon Type
123Amount
123Percent
456Amount
789Percent

I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:

idCoupon Type
123Amount
123Percent

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

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

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

Like Statement With Multiple Values

Dec 1, 2009

I have a field that may look like this:

AS01 AB CD EF

I want to check to see if that string contains any of the values in:

select code from codes_table;

For example, if

select code from codes_table;

returns:
code
-----
AB
LM
NO
PQ

then the query should return 'True'. Using the string above it would return 'True' because 'AB' from the string exists in the table codes_table. Pseudocode would look something like this I guess:

if the input string
contains any of the codes
in the field 'code' from table 'codes_table'
then
'OK'
otherwise
'No good!'

View 3 Replies View Related

SQL & PL/SQL :: Multiple Values In Constant?

Jun 10, 2010

I am trying to do something like this..

declare
a constant variable (1,2,3);
b number;
begin
select x
into b
from y

if b in a then...

What is the correct way of declaring multiple values in a constant and then using the IN clause.

View 9 Replies View Related

SQL & PL/SQL :: Decode Using Multiple Values

Jan 23, 2013

i am using the following within queries:

decode(qt_flag,'KT','Y',decode(qt_flag,'IT','Y','N'))

It works fine but when i use in ETL tool it is not working. Is there a way to use decode just once with multiple values like above.

View 4 Replies View Related

SQL & PL/SQL :: One Record With Multiple Values

Jun 13, 2011

I have used sql for my school projects and work projects. not too extensively though. I came across this issue and read a lot of blogs but still not luck.

hopefully my issue can be solved here.

Data:
Sub_grp_nbr Prodt_ctgy_cd Extra_column
ABC123 05 1
ABC123 02 2
ABC012 05 3
ABC456 02 4
ABC456 05 5
ABC789 05 6

I need to obtain all the sub_grp_nbr's that have a prodt_ctgy_cd of 05 but not 02. so according to the data above, i should only get the results of where extra_column = 3 and 6

View 7 Replies View Related

SQL & PL/SQL :: Like Operation For Multiple Values

Jun 12, 2013

I have a source view where I have some invalid records and those should be found based on codes present in another table.

For eg. from source the records come like

****************SIINNSFDFD****FDFDF2******8
**********TABLE****************FDFSFSSFASFAS********

and if my reference table has values

SIINNSFDFD
TABLE

then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.

I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.

Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.

View 2 Replies View Related

PL/SQL :: Multiple Values In Condition

Aug 30, 2012

I am trying to do something like this

SELECT clave_grupo FROM SS_TTABLAS
WHERE CLAVE_GRUPO IN
(
CASE WHEN (condition) THEN 'v1'
ELSE ('v1','v2',v3')
end
)

This is the error msg:
ORA-00907: missing right parenthesis

just before ,'v2',v3')

View 2 Replies View Related

SQL & PL/SQL :: Multiple Report Parameter Values

Oct 21, 2012

For the below query:

SELECT id, pob,exp
FROM emp
where exp= (:exp)or @exp is null
O/P:
id pob exp
_____________________
1 CT 2
2 NJ 3
3 NY 2

It takes only one value of :exp but, I would like to give multiple values separated by ','. My problem is the parameter can be either single value or Blank or multiple values. My code do work for single value and Blank, Now how to implement multiple values .

View 3 Replies View Related

SQL & PL/SQL :: How To Return Multiple Values From A Function

Aug 28, 2010

I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.

SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /

Table created.

SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);

1 row created.

SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);

1 row created.

SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);

1 row created.

SQL> COMMIT;

Commit complete.
[code]...

Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.

View 6 Replies View Related

SQL & PL/SQL :: Separated Result With Multiple Values

Aug 24, 2010

with t as
( select 1 id, 101 book_id, 'MICROBIOLOGY' book_type, 1 category, 'sCIENCE AND TECH' category_name
from dual
union all
select 1 , 101 , 'MICROBIOLOGY', 2 , 'HEALTHCARE' from dual
union all

[Code]....

id book_id BOOK_TYPE category category_name

1 101 MICROBIOLOGY 1 SCIENCE AND TECH
1 101 MICROBIOLOGY 2 HEALTHCARE
1 102 CHEMISTRY 5 CHEMICAL ENGINEERING
2 105 COMP SC 1 SCIENCE AND TECH

The above is the output for a query after joining multiple tables. I have just put here the output I am getting after joining the tables.Now I want to achieve the below result.

Expected output: it should be | delimited

1|101|MICROBIOLOGY|102|CHEMISTRY|1|sCIENCE AND TECH|2|HEALTHCARE
2|105|COMP SC|1|SCIENCE AND TECH

Is there any alternative way other than SYS_CONNECT_BY_PATH? I also tried to use CONCAT_ALL but its not working.

View 13 Replies View Related

SQL & PL/SQL :: How To Use Case When Return Multiple Values

Mar 13, 2012

I have a table second_table which has a username and code field. A username (not the primary key) may be entered multiple times with different codes, or a single code 'ALL', in which case, the codes have to be fetched from 'third_table'. I am unable to form a 'case' clause to handle the fact that a list has to returned when the 'code' field from the second_table returns 'ALL'.

e.g.

Second_table
username code
A ALL
B 23
B 56
B 33

Third_Table
code
67
78

So, when the user asks the codes for user A, he should get 67 and 78 and when he asks for the user B, he should get 23,56 and 33

View 13 Replies View Related

SQL & PL/SQL :: How To Hold Multiple Rows Values Using Array

Jun 1, 2010

How to hold the multiple rows values using array? And I have to pass this values to some other procedure.

Ex: SQL> select ename from emp;

ENAME
----------------------
Vetrivel
Dr.Venkat
Vinoth
Sudhakar
Sivaganesh
Senthil

View 7 Replies View Related

Client Tools :: Having Multiple Input Values

Apr 6, 2011

Using pl/sql block , i tried to have a certain input values from Sql prompt but it doesn't work when i invoke it.

Here the simplest

declare
a number(4);
begin
for i in 1..10 loop
a := &a;
end loop;
end;
/

View 9 Replies View Related

SQL & PL/SQL :: Passing Multiple Values To IN Clause In Function?

Jun 22, 2010

I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :

where account_code = P_CODE.

Eg. The value of :P_CODE is 'CS'.

I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.

I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0

View 3 Replies View Related

Forms :: Assign Multiple Values To Variables?

Mar 31, 2012

assigning values to a particular variable that i need for my button trigger. I Understand that you can assign multiple values to a variable that has a varchar or char data type....is there a way to assign multiple values to a variable that has a 'number' data type?? I need this for my 'where' clause

declare
usergrade varchar(4) := 'pass';
user_unitcode number(6) := ;--needs three unit codes to equal pass

View 1 Replies View Related

SQL & PL/SQL :: Get Single Row From Multiple Duplicate Values Of One Field?

Sep 10, 2012

CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));

INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;

I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.

desired output sample:

Quote:1, aad
2, bb

I used:
select distinct(id),description from prim_tbl;

but it did not give the required result.How can I get it??

View 6 Replies View Related







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