SQL & PL/SQL :: How To Retrieve CLOB Data Using DISTINCT Keyword For Other Columns
Aug 2, 2012
I have one query regarding how to retrieve the CLOB data.
The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.
Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.
View 15 Replies
ADVERTISEMENT
May 27, 2013
i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work
View 1 Replies
View Related
Mar 8, 2012
can we use distinct keyword with the count and sum analytical functions?
View 5 Replies
View Related
Dec 5, 2012
I have a table created as below
create table tab123( x clob);
Inserted a row as below
insert into t values ('"<ProcessBatchRequest xmlns:inp1="http://services.abc.com/" ServiceVersionNumber="" xmlns="http://services.abc.com/GlobalBatchServiceV1.0">
<inp1:BatchDetail>
<inp1:ApplicationID>test.123</inp1:ApplicationID>
<inp1:ApplicationBatchID>test.123</inp1:ApplicationBatchID>
<inp1:MessageTimeStamp>2012-11-28T11:05:32.000-05:00</inp1:MessageTimeStamp>
[code]....
Now i want to retrieve the value present under a given xpath.
View 7 Replies
View Related
Feb 4, 2013
CREATE TABLE FEB05
(NAME VARCHAR2(20),
CITY VARCHAR2(20))
INSERT INTO FEB05 VALUES ('AKON','CIT22')
INSERT INTO FEB05 VALUES ('MARIA','WOT101');
[code]......
I WANT A QUERY THAT WILL GIVE O/P AS TO GET DISTINCT COUNT OF NAME AND CITY COLUMN separately.
I.E. THE O/P SHOULD BE
Quote:Count(dist name) Count(dist city)
6 5
View 5 Replies
View Related
Nov 1, 2011
Category Table
Name Null? Type
------------------------- -------- --------------
CAT_ID NOT NULL NUMBER
CAT_NAME VARCHAR2(20)
Qusestion Table
Name Null? Type
--------------------------- -------- -------
QUS_PK NOT NULL NUMBER
CREATED_DATE DATE
VIEW_COUNT NUMBER
CAT_ID NUMBER
select category.cat_id , qus_pk , cat_name , created_date , view_count
from category , qusestion
where category.cat_id = qusestion.cat_id
order by view_count desc
How can I retrieve distinct cat_id here?
View 21 Replies
View Related
May 23, 2013
I am only able to extract only 4000 characters from the clob column "DESCRIPTION".how to get more characters or max for that column with the same query concept?
select distinct
o.id "Organization ID",
en.entity_id "Contact ID",
en.entity_cd "Note Entity Code",
ed.entity_name "Note Entity",
en.entity_id "Note Entity_id",
[code]....
View 28 Replies
View Related
Feb 1, 2012
I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.
SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host
FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p
WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id
AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT'
AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;
This query gives following result
3525125240000USDINR
35251250 USDINR
352512560000 USDINR
352512550000 USDINR
There are two distinct currency codes (USD, INR) and total amount is 350000. So I am looking for a query to give me the following result
3525125350000 2
View 9 Replies
View Related
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
Feb 22, 2010
I have a table PRODUCT with following structure:
Productcode Productname Quantity
AF Ade Fgh 100
LO Ldo Ope 50
SK Ske Kro 47
....
There may be any number of records in the table. Now I need to print distinct products in different columns such as.
AF LO SK
100 50 47
There may be any number of products...I need to print all those products as different columns as shown above. How to write the Stored procedure for this?
View 2 Replies
View Related
Nov 16, 2012
I am trying to get information about the columns of tables in Oracle databases. In MySQL, show columns provides good deal of information.But in Oracle, we do not have such equivalent call."describe" shows only the primary key.
But it does not show whether any column has unique constraint or an index.
I created a column in a table with unique constraint (but not a primary key), but it was not listed with CONSTRAINT_TYPE = 'U' in dba_constraints view, but was listed with CONSTRAINT_TYPE = 'C' - Check constraint.
View 6 Replies
View Related
Aug 5, 2013
<ORACLE VERSION : 11.2.0.2.0> i have created a table with CLOB as datatype for one of the columns, I am trying to store a string ( I am not sure about the length of the string) , when i am querying on my table for the CLOB column,instead of the actual string "(HUGECLOB)" is coming. How to get the actual string in case the problem is with the SIZE.
View 1 Replies
View Related
May 30, 2013
i have a table that contains employee id, employee name , so if i gave the correct employee id in where clause of select statement it will show employee name, in case if i give the employee id that does not exist in the table it will show 'Employee name is not found'..
View 2 Replies
View Related
Jul 4, 2012
What is difference when you issue a create controlfile with "standby" keyword on the primary database
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/path/db_stby.ctl';
vs. creating a control using
ALTER DATABASE BACKUP CONTROLFILE to '/path/db_control.ctl';
what does ORACLE put into the standby control that is "extra"? ie. what is the difference between a standby control file and a normal control file?
View 7 Replies
View Related
Dec 28, 2012
SELECT mkct_group_code FROM ID_MKT_CONTTYPE_GROUP_MASTER where mkct_group_code=20'
Note mkct_group_code column is varchar2(10) type.
It has values as
20
10
30
20'
30'
View 3 Replies
View Related
Apr 7, 2013
I am writing a SQL inside an application to retrieve data from a table for certain IDs or for All employees based on the user's input. I create a variable :SELECTION for the where clause. When I run the SQL, I receive the error of missing right parenthesis. There is no error if I hard code the IDs.
If the input is 'All' -> the variable :SELECTION is blank so if the SQL :
select name, address, ID from employee :SELECTION
will be
select name, address, ID from employee
If the input of users ID as '1234','9999' - > the :SELECTION = ' where ID in (:VARIABLEID)'
:VARIABLEID = '1234','9999'
so the SQL:
select name, address, ID from employee :SELECTION
should be
select name, address, ID from employee where ID in ('1234','9999')
However, I got the missing right parenthesis error. I suspect it is something with the single quote, so I tried different ways, but it is still not working.
View 1 Replies
View Related
Apr 19, 2012
I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:
select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one
my question is can retrieve all the data in one block more quick than this query.
View 5 Replies
View Related
Nov 10, 2011
Look into the below table:
TABLE :- EMPLOYEE
________________________
| ID | SUPERVISOR |
|_______________________|
| A101 | B102 |
|________|______________|
| | |
| B102 | C104 |
|________|______________|
| | |
| C104 | D108 |
|________|______________|
| | |
| D108 | E104 |
|________|______________|
Here B102 is supervisor of A101 and C104 is supervisor of B102 and so on. I want to get this data into new table in below format
TABLE :- Hierarchy
________________________________________________________________
| ID |SUPERVISOR_1 |SUPERVISOR_2 |SUPERVISOR_3 |SUPERVISOR_4|
|______________________|_____________|_____________|____________|
| A101 | B102 | C104 | D108 | E104 |
|________|_____________|_____________|_____________|____________|
| | | | | |
| B102 | C104 | D108 | E104 | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| C104 | D108 | E104 | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| D108 | E104 | NULL | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | |
| E104 | NULL | NULL NULL NULL
|________|_____________|_____________|_____________|____________|
I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.
View 7 Replies
View Related
Dec 13, 2011
I have the following table with the some sample data:
|| Filename || receiving_site || originating_site ||
| fileA | siteA | HQ |
| fileA | siteA | siteA |
| fileB | siteA | HQ |
| fileA | siteB | HQ |
| fileA | siteB | siteB |
| fileB | siteB | HQ |
| fileB | siteB | siteB |
The table works in such way:
1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.
2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.
My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.
How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.
View 2 Replies
View Related
Oct 14, 2011
IF I have Salary Table,In that Salary Table,How to retrieve 6 decimal place of data .
View 16 Replies
View Related
Dec 27, 2010
I have one table as shown below,
CREATE TABLE MAT
(matrl varchar2(100),
date_man date,
weight number(10)
);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);
The data will look like this .
matrldate_manWeight
mat112-Dec-10100
mat213-Dec-10200
mat321-Dec-10300
mat426-Dec-10400
mat522-Dec-10500
mat602-Dec-10600
mat723-Dec-10700
mat807-Dec-10800
I need to order by this data in terms of date and need to get the data as shown below. Acc_wt is accumulated weight till the material.
Date Weightacc_wt
mat602-Dec-106000
mat807-Dec-10800600
mat112-Dec-101001400
mat213-Dec-102001500
mat321-Dec-103001700
mat522-Dec-105002000
mat723-Dec-107002500
mat426-Dec-104003200
Can we make use of oracle lag and lead functions here?Or some other methods?
View 6 Replies
View Related
Sep 30, 2011
example for retrieve data from blob column using pl sql procedure in oracle?
View 2 Replies
View Related
Jan 20, 2012
How to retrieve first hundred records from a table ?
FYI
---
The table size is 5 GB
The table count is 127922653
Table has 14 columns
Table is partitioned as well.
The table has 10 partitions.
View 17 Replies
View Related
Jun 8, 2011
I am using LOV on some field to retrieve data, but sometimes it does not retrieve anything cause of unavailability of date its fine but i want to put N/A in that field when such condition is occurring .
View 2 Replies
View Related
Sep 28, 2011
I'm writing a PHP page to display some data from an Oracle database. Unfortunately, I can't copy the code because it's proprietary. One of the columns in the db is of type CLOB. I'm having trouble getting the data from the CLOB column.
The way the code is now, is there is a query string read into a variable, and the query string variable is read into a function that then retrieves the data from the db. Once the result set is returned, it is parsed to get data from all of the columns. The issue is that I've never worked with CLOB data before, so I'm having some difficulty extracting the data for that column. I know I can use the DBMS_LOB.READ function, but I'm not sure how to apply it in this case. The following is a generic form of the query string I'm using with b.clob_col being the column I'm having issues with.
$queryString = <<<EOD
SELECT a.col1,
a.col2,
a.col3,
b.col4,
b.col5,
b.clob_col,
from table1 b
inner join b.col4
on blah1
inner join a.col2
on blah2
where blah
and blah
EOD;
View 1 Replies
View Related
Jul 8, 2012
I have a table
select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002
2 rows selected
select * from testing where name like ('GHI,TCF');
now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.
View 9 Replies
View Related
Jun 22, 2011
,i have a table called travel detail from which i have retrieved few rows based on 1 condition by using LOV and on next few rows modification is allowed. The rows are getting modified but it is also allowing me to enter in next blank row and insert any data in that row.how can i retrieve only that data which is based on the given condition. Further blank rows should not b displayed.
View 2 Replies
View Related
Jun 3, 2012
I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.
select PERIOD_NAME "Month/Year",segment1 "Company" ,Segment6 "GL Account", currency_code "Currency",Mmt "Movement",
BEGIN_BALANCE+PERIOD_DR-PERIOD_CR "Balance At Date"
FROM
(select gb.period_name "PERIOD_NAME",decode (gcc.segment1, '20', 'Internode', '10', 'Agile') SEGMENT1,
gcc.segment6 "SEGMENT6", gb.currency_code "CURRENCY_CODE", gb.begin_balance_dr "BBDR", gb.begin_balance_cr "BBCR",
[code].....
View 3 Replies
View Related
Sep 7, 2010
I have a detailed block with a user ID column. The user name is not available in the block. Therefore, I have created a non-database column to retrieve the user name into it.
Here is the code I've used.
PROCEDURE get_details IS
iLoop number := 1;
CURSOR c is
SELECT FULL_NAME
FROM GRP_EMPLOYEE
WHERE EMPLOYEE_NUMBER = :USER_ID;
[Code]...
However, it retrieves the first record only.
The results are displayed like this.
Loop: Result:
1 Full_Name
2 Empty
3 Empty
4 Empty
View 2 Replies
View Related
Nov 16, 2012
I have a table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as
select asigno,count(*) from angdata77 group by asigno order by asigno;
Is there any other query for retrieving the data from angdata77
View 5 Replies
View Related
