SQL & PL/SQL :: Values Which Are Not Present In Column

Jun 2, 2011

I am trying to get only those values which are not present in the column. I have tried but i think that i am wrong in logic.

SELECT dummy
FROM DUAL
WHERE dummy IN
('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12',
'13')
AND NOT EXISTS (
SELECT pay_list_serial
FROM per_requests_master
WHERE request_status IN ('4', '5')
AND pay_list_no = :list_no)

View 12 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: Mask A Column Value Present In Production

Nov 27, 2012

How we can mask a column value present in the production which is very sesitive like bank a/c no,Credit card nos etc while replicating to the test/development envirnoments in PL/SQL.Things that need to be considered is referential integrity,data types,length of the column and performace while execuing the code.

View 2 Replies View Related

SQL & PL/SQL :: Check If Element Present In Defined Column Of Cursor?

Apr 29, 2010

i want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)

for example

create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');

then i got my function

create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop

[code]....

now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;

when i try t compile i got:

Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope

View 3 Replies View Related

JDeveloper, Java & XML :: How To Add Some Value In Already Present Node In Clob Column

Jan 31, 2012

adding some value in xml type clob column, for ex: xml_tab table has more than 100 rows, below i have created some rows

Quote:create table xml_tab(id number, ex_xml clob);

select * from xml_tab;

1 <CData CI="1111" <Dist> <value desc="25">25 </value> <value desc="30">30 </value> <value desc="35">35 </value> <value desc="40">40 </value> </Dist> <Year IsReadOnly="Y"> <value descr="Old"><1995 </value> <value>1995 </value> <value>1996 </value> <value>1997 </value> <value>1998 </value> <value>1999 </value> <value>2000 </value> <value>2001 </value> <value>2002 </value> <value>2003 </value> <value>2004 </value> <value>2005 </value> <value>2006 </value> <value>2007 </value> <value>2008 </value> <value>2009 </value> <value>2010 </value> </Year> </CData>

[Code]....

View 5 Replies View Related

Reports & Discoverer :: How To Present Report In XLS Format With Proper Column Headings

Dec 12, 2011

I have a report and require the output in xls/xlsx (Excel) format. Currently following properties under System Parameters in Report Builder 6i have been made

Desname = The path for e.g: \C:sz-serverc$ihelp_workingxls
pt_sample_report.xls
Desformat = Delimited
Destype = File

The rest parameters has default settings I have not changed any of the rest User parameter.The report comes up in excel format but totally in distorted manner. i.e; All the column names of report are displayed first in vertical format followed by actual rows for the report.

The requirement is like this

[Select all] [Show/ hide]
Student ID Student Name Fathers Name Guardian Name
1 sample1 sample2 sample3
2 sample4 sample5 sample6

And it is displaying like this

Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3
Student ID Student Name Fathers Name Guardian Name 2 sample4 sample5 sample6

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

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 :: Column Values To Column Names

May 20, 2011

I had a table( T1). Five columns exists in the table. The table data is like below

C1C2C3 C4C5
--- ----- ---- ----- ----
JAN FEB MAR APR MAY
One Two Three Four Five

I need the output like below one

JANFEBMARAPRMAY
---- ----- ----- ---- ----
OneTwoThreeFourFive

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

SQL & PL/SQL :: Max Row Values Into Column

Oct 3, 2011

I have table output as shown below.

AID UCD U_TXT UDATE PID
116 1 Req Documents 01-OCT-2011 100
116 2 AGG APPR 01-OCT-2011 101
116 3 Docs received 02-oct-2011 102
116 1 Tmp received 02-oct-2011 103
117 2 Notice sent 03-oct-2011 104

UCD - We have total 19 codes (1 to 19), each can have multiple rows for one AID.. like 1 repeated twice for AID 116.
PID - Primary id (Primary key column)

Output I am looking
--------------------
AID COL1 COL1_TXT COL1_DATE COL2 COL2_TXT COL2_DATE..ETC
116 1 'Tmp received' 02-oct-2011 2 AGG APPR 01-OCT-2011
117 2 Notice sent 03-oct-2011

If the same UCD repeated multiple times then we should get the max(PID) record for that UCD and for that AID

I tried with group by AID,PID. but couldn't bring the rows to columns. I have attached the script with the post

View 2 Replies View Related

SQL & PL/SQL :: Column Values In Row

May 10, 2011

i've follow problem:

select x1
from table

and i get
a
b
c

What i want is
a,b,c

The main problem here is, that it can be
a
b

as such as
a
b
c
d
e
f

in detail, i don't know prior how many i will get. With declare a cursor is it easy, but i try to find one solution only with sql.

View 6 Replies View Related

Get Values From Table Column Using PL/SQL Or Just Using SQL

Dec 3, 2008

I'm having a table which has a column which has values given inside square brackets.

[[[123412]]] ,[[[[werer34]]]],'[[qw2_w3rt]]

Now, the requirement is to get the values which are inside the innermost square brackets.Such as in

First case : 123412
Second one: werer34
Third : qw2_w3rt

Only the values inside are needed. Is there any way to achieve it using Pl-Sql or just using Sql ?

View 3 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 :: Update Same Column With Different Values?

Dec 6, 2012

updating the column values.

Test data:

CREATE TABLE test_at(nr_a NUMBER, nr_id NUMBER) TABLESPACE ECLP_DATA01
INSERT INTO test_att VALUES(90270091,NULL);
INSERT INTO test_att VALUES(90270091,NULL);

[Code].....

I need to update "nr_id" column of test_at table with the values from test_d table. They can be udpated in any order.

I tried to update using rownum but it failed with "single row subquery returns more than 1 row...."

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

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 :: Return Column Name Corresponding To Values

Apr 28, 2011

I want to search a some values in oracle table and then return the corresponding column names.

View 1 Replies View Related

SQL & PL/SQL :: How To Get Column Values In Procedure

Apr 5, 2010

My task is I want to retrieve all the Insert Statements of the rows that are present in each table of a particular Schema.

When I give input as Schema name(Scott) in a Procedure, Then the procedure retrieves all the Insert statements of rows of all tables that are present in scott user.

For this I started like this..

CREATE OR REPLACE PROCEDURE MYPROC(UID DBA_OBJECTS.OWNER%TYPE)
AS
CURSOR MYtab
IS
select o.owner, o.object_name
from dba_objects o where O.OWNER=UID

[code]....

When I execute this procedure I got the result as follows.

EXEC MYPROC('SCOTT');
insert into DEPT(DEPTNO,DNAME,LOC) values (
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (
insert into BONUS(ENAME,JOB,SAL,COMM) values (
insert into SALGRADE(GRADE,LOSAL,HISAL) values (
insert into PROJECT(PNO,PNAME,PMGR,PSTART,PEND,BUDGET) values (

[code]....

As you see that I have executed successfully till each table columns.But How to retrieve the values in this?

View 2 Replies View Related

SQL & PL/SQL :: Distinct Values In A Column?

Jul 18, 2013

I have a table with two columns, like:

123 xxx
456 xxx
789 yyy
987 yyy

And in the output I would like to have:

123 xxx
789 yyy

I tried with distinct and unique on the second column, but it doesn't work.

View 12 Replies View Related

SQL & PL/SQL :: Product Of Values In The Column?

Jul 29, 2010

how to find the product of the values in the column.. assume that the column has number(2) as its data type..

as we use sum(colname) for the sum of the values in the column there s no function to find the product.

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Column And Values?

May 30, 2012

I have two string one is comma separated values as a column name and another string which is having the comma separated value corresponding to the column name.

Example:
string1:='col1,col2,col3'
string2:='1,aaple,'2-jul-75'

now i have to create and run a query which will be like that

select * from emp where col1=1 and col2='apple' and col3='2-jul-75'

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

SQL & PL/SQL :: Concat Column Values

Sep 22, 2011

Table creation

SQL> create table concat_test (
2 empno varchar(10),
3 empname varchar (10),
4 description varchar(50) )
5 /

Inserts are

insert all
into concat_test (empno,empname,description) values ('1','pratik','want')
into concat_test (empno,empname,description) values ('1','pratik','to')
into concat_test (empno,empname,description) values ('1','pratik','concat')
select * from dual;

I want the o/p as

Empno empname description
1 Pratik want to concat

View 5 Replies View Related

PL/SQL :: Unique Values Of Each Column

Aug 21, 2013

I have a problem .I have a oracle table its column count more then 500 . I want unique values of each column and insert one table.

View 44 Replies View Related

PL/SQL :: Extract Values From A Column

Aug 26, 2013

I need to extract the values and store it in seperate column. write the function to extract the data from column.I am having column DOC_NO from which i have to extract dob, gender, nationality Doc_no:  

9005070288080 DOB : 900507- yyddmm  Gender : 0288- if less than 5000 then female else male Nationality: 080-  0 means India 1 means foreigner. 

View 4 Replies View Related







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