SQL & PL/SQL :: To Retrieve Rows Which Have Same Buyer ID
Dec 1, 2010
My table "xxbuyer_det" contains the below data.
buyer_id Invoice_numPriceItem
------------------------------------
148137110A
149314211B
149314315C
149314415F
148120520D
148120625E
My requirement is to select the rows which have same buyer_id more than or equals to two times (>=2). I am trying with the below query. But this query is failing(not retrieving any data) as i have used group by class and count value is 1 for every record.
SELECT BUYER_ID, INVOICE_NUM, PRICE
FROM
xxbuyer_det
GROUP BY
BUYER_ID,
INVOICE_NUM,
PRICE
HAVING COUNT(*) >=2
View 7 Replies
ADVERTISEMENT
Jun 13, 2013
how to retrieve deleted rows in oracle?
View 7 Replies
View Related
Jun 14, 2010
How to retrieve duplicate rows from a table, suppose i don't know any column names. Without hard-coding column names I need to find.
View 5 Replies
View Related
Mar 7, 2012
I have created a table called ct_temp and it's structure is:
create table ct_temp(id char(2),srs number(3),amt number);
Result : Table Created
I now insert the following rows into the table:
id srs amt
1 62 30000
2 65 50000
3 65 70000
4 65 80000
5 62 16000
6 65 10000
7 65 100000
8 65 10
Commit
Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)
criteria : I then want to retrieve the following:
for srs = 62, all values that is greater than 10,000
Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
Works like a charm and retrives the results.
Now comes the big issue
I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.
Typically I wrote the query as:
select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)
I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".
I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.
I have tried the following in the office:
using EXISTS operator = no luck
using INLINE VIEW = no luck
Is there any way around?
View 26 Replies
View Related
Aug 15, 2012
I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.
For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.
I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.
Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.
I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0
View 16 Replies
View Related
Jun 6, 2008
I am trying to retrieve all rows with the date of May 23rd, 2008. Our data is stored with the date and time.
How do I format the where clause so that the time is taken off? I am still a newbie with oracle and what I have is this:
Select Distinct Table1.*, Table2.*
from Table1, Table2
where Table1.file_name = Table2.file_name
and Table1.downloaded_date = Table2.date_loaded
and downloaded_date = '23/May/2008'
Of course, it's the last line not working because I don't know how to format it and no rows are retrieved when there are definately May 23rd rows stored in there.
View 6 Replies
View Related
Jul 7, 2011
I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.
ex:
the output should look like this.
owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------
# of records not null # of records null
---------------------- --------------------
View 12 Replies
View Related
Apr 12, 2013
The Table having 3 columns
col0col1 , col2
P112
P112
P102
P113
P115
I want to retrieve the changes rows only.
Output like
col0col1 , col2
P112
P102
P113
P115
View 5 Replies
View Related
Oct 12, 2010
I have googled so much but unable to find the explanation how the following query work.
SELECT * FROM EMP a WHERE 2 = (SELECT COUNT(rowid) FROM EMP b WHERE b.rowid <= a.rowid);
View 2 Replies
View Related
Jan 20, 2012
example
My table mst_product(product_code,product_name) and its value is (190,toy),(198,tim),(280,bush),(jupiter,top),(AUTO2,auto),(281,tig),(282,com)...and so on
I want to make a query to select those items which is equal to and greater than 280. So how we make a query.How select those items which does not have any alphabetic letters.
View 4 Replies
View Related
Dec 10, 2012
I have kept xml in blob and i am retriving it as following.
select utl_raw.cast_to_varchar2(dbms_lob.substr(colblobforxml,2000,1)) from t1
the problem is, it only gets 2000 chars and i have more than 2000 chars.
View 2 Replies
View Related
May 28, 2008
I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:
(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER )
OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))
If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.
View 7 Replies
View Related
May 22, 2007
I'd like to know which SQL command (if there is any) I can use to display the current schema of a table. I'm particular interested in knowing which columns are indexes.My privileges to the DB are quite basic (I think that I'nm only allowed to run SELECT statements
This is the Oracle version that I'm using:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
View 2 Replies
View Related
Mar 29, 2010
select rownum, CATR_ID, CAT_ID, CATR_REG_COPY, CATR_REG_LABEL, CATR_ACQUIRED_DATE, CATR_REG_DATE, CATR_MEDIA_COMMENTS, CATR_WITH_DIGITAL,
CATR_ORIGINAL, CATR_LINK, CATR_CREATED_BY, CATR_CREATED_DATE, CATR_MODIFIED_BY, CATR_MODIFIED_DATE, CATR_CHECKOUT, Available,
CATR_RETURN_DATE, LOCN_ID, LOCN_SITE, LOCN_LOCATION, MTYPE_GROUP, MTYPE_NAME, ACCESS_LEVEL, DESCRIPTION, CAT_TITLE, CAT_DESCRIPTION,
CATEGORY_ID, CAT_AUTHOR, CAT_PUBLISHED_DATE, CAT_PUBLISHER, CAT_EVAL_RELEVANT_KEYWORDS, CAT_REG_NUMBER, CAT_REG_SUBNUMBER, U_NAME
[Code]..
There are over 1500 records, but this query does not return any row. If i change rownum >= 100 to rownum <= 100 it returns first hundred records though... What is wrong here?
View 12 Replies
View Related
Feb 24, 2010
I need to retrieve only one telephone number against the parent id. The preference is to get mobile if available if not home number else work phone. How can I achieve this
I have given the sql code and sample output below
select
pp.phone_id
,pp.parent_id
,pp.phone_type
,pp.phone_number
from per_phones pp
[code]....
Actual results should be
PHONE_ID PARENT_IDPHONE_TYPEPHONE_NUMBER
12960 2500H1 9385789
311108 146564M 04879652
View 6 Replies
View Related
Jan 6, 2011
I need a query to retrieve a particular string from all the columns and all tables in a specified schema.
For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.
View 2 Replies
View Related
Dec 2, 2010
how do i retrieve only last rank row from table ?
View 7 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
Apr 13, 2011
I want Query on this table. EXAMPLE TABLE:
SENDER RECEIVER AMOUNT
A B 1000
B C 2000
A D 3000
C A 3000
B A 4000
A B 5000
A B 1000
B C 2000
C A 3000
B A 4000
A B 5000
A C 10000
Dynamically I am giving user name A. I want to retrieve the data how much money user B taken from user A and how much money user B given to A lly for C and D.
CONDITION: I want to retrieve the records of 2 way transaction that means user A sent money to user B and user B must sent money to user A. I don't want one way transaction records i.e user A sent money to D but user A didn't get money from user D like this records.
OUTPUT LIKE:
USER MONEY_TAKEN_FROM_A MONEY_GIVEN_TO_A
B 12000 8000
C 10000 6000
View 14 Replies
View Related
May 26, 2011
In SQL server the cursor is defined to retrieve row by row and process it. We can fetch records as FIRST, LAST, NEXT, PREVIOUS and SCROLL like that. Can we do the above in oracle. Else what is the use of cursor in Oracle. I know that it is a private area in which the records are processed.
View 6 Replies
View Related
Oct 27, 2011
I create a directory using this:
CREATE DIRECTORY xml_data AS 'c:\mytest';
How can I retrieve the 'C:mytest' in a select statement?
View 5 Replies
View Related
May 12, 2010
I have a cluster setup, and i want to retrieve host name for my small app.Is there any query which gives me the HOSTNAME where oracle is running??
View 14 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
Jun 19, 2013
I need function to pick the record from DB random manner.For example, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5
View 12 Replies
View Related
May 21, 2013
I have registered an XSD schema (with gentypes = true) which contains an enumeration type like this:
...
<xsd:simpleType name="OperatoreLogico">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="OR"/>
<xsd:enumeration value="AND"/>
</xsd:restriction>
</xsd:simpleType>
[Code]....
was successfully generated the object type:
TYPE "Richiesta_getListaPersoneFisiche"
AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T",
*"operatoreLogico" "XDB"."XDB$ENUM_T"*,
"nome" VARCHAR2(4000 CHAR),
"cognome" VARCHAR2(4000 CHAR),
"codicefiscale" VARCHAR2(4000 CHAR),
"datanascita" DATE) NOT FINAL INSTANTIABLE
Fill correctly the object from an xml valid for the schema using the function "toObject".
I can retrieve the values of all attributes of the object (objectName.attribute), but I do not know how to retrieve the value of the enumeration type (XDB$ENUM_T) "operatoreLogico".
View 8 Replies
View Related
Dec 20, 2012
How can i retrieve which tables have composite primary keys, and only one of the primary key columns is a foreign key to another table?
Like:
CREATE TABLE club (
clubId NUMBER,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (ClubId)
);
CREATE TABLE team (
teamid NUMBER(10),
clubid NUMBER(10),
teamname VARCHAR(10) NOT NULL,
PRIMARY KEY (teamid, clubid),
FOREIGN KEY (clubid) REFERENCES club (clubid));So in this case, the team table
View 4 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
Feb 5, 2013
Objective: I need to compile a final string by concatinating the unique values from different strings.
Here is the script to create tables and data.
Create table temp_acronyms(id number, acronym varchar2(30);
insert into temp_acronyms values(1, 'ABC');
insert into temp_acronyms values(2, 'DEC//NOFO');
insert into temp_acronyms values(3, 'CBK//FO TO USA');
insert into temp_acronyms values(4, 'DEC//NO ENTRY');
insert into temp_acronyms values(5, 'ABC//NOFO');
COMMIT;
select * from temp_acronyms;
ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO
I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.
my final string should have the values as below
ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY
View 6 Replies
View Related
Apr 19, 2011
am using oracle 10g
and i want to retrieve IP address for client who do the transaction
and i try to using some selects like select terminal from v4session but it return the IP for application server
so i think to use commands ipconfig
how can i call windows commands by oracle
View 8 Replies
View Related
