SQL & PL/SQL :: Select Rows From Table With Same Particular Field?
Aug 20, 2012select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.
View 7 Replies
ADVERTISEMENT
SQL & PL/SQL :: SELECT With Count Of Rows Depending On Sum Of A Field?
Aug 10, 2011I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.
At first a code sample of the complete selection:
SELECT DISTINCT mnr, ktxt,
(SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty
FROM reldb d
WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprÃĪgt%')
AND saext='M'
ORDER BY qty DESC,ktxt;
This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.
So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4.
I hope, this specification is exactly enough to understand my intent.
1. Can I do this in a query?
2. If yes, what would this query look like?
SQL & PL/SQL :: Generate The Number Of Rows Based On Table Field?
Mar 29, 2013generate the number of rows based on table field.
Sample code is given below.
CREATE TABLE T
(
docno VARCHAR2(10),
CODE VARCHAR2(8),
QTY NUMBER(3)
)
LOGGING
PL/SQL :: Compare Two Rows From Same Table And Show Field And Difference?
Aug 28, 2013How can I compare two rows from the same table and show the field and the difference?
Example:
select ename, job, mgr, hiredate, sal, deptno
from EMP t
where t.empno = 7782
or t.empno = 7788
ENAME JOB MGR HIREDATE SAL DEPTNO
1 CLARK MANAGER 7839 6/9/1981 2450.00 10
2 SCOTT ANALYST 7566 6/9/1981 3000.00 20
My report comparing fields should be:
FIELD BASE COMPARE DIFFERENCE
ENAME CLARK SCOTT --
JOB MANAGER ANALYST --
MGR 7839 7566 --
SAL 2450 3000 550 --(Show difference only for numbers)
DEPTNO 10 20 --
SQL & PL/SQL :: Delete Last N Sets Of Rows Of A Table With Same Dates In Sort Field
Mar 15, 2013I know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.
i.e.:
SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;
But how do I DELETE these same rows?
Application Express :: Export Table Rows To Excel And Save To BLOB Field?
Sep 26, 2012I try to find out how export data from table to Excel file format and save the result to BLOB field in some other table.I know how to download report from Page by submit, but I need to process data and instead of returning result to user as Excel file - save it in BLOB.
Also I found implementation on JAVA for the issue but actually I wanna study out - Is it possible to resolve this issue by PL/SQL and APEX API methods?
How To Select 10 Rows From Mission Table
Sep 2, 2008i want the 10 most recent mission date from mission table . i have tried using rownum but could not get it. TOP does not work
select * from
(select length(code_name) leng from missions
where length(code_name) >7
order by mission_date)
where rownum = 10
;
this does not work ::: no rows are selected and i do rownum = 1 then gives me one record and when i do rownum>=1 gives me all records
SQL & PL/SQL :: Select Updated Rows From One Table
Jul 13, 2013There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A
COL_A1COL_A2
AAAMOBILE
BBBTABLET
CCCDESKTOP
DDDUNKNOWN
TABLE B
COL_B1COL_B2
AAAMOBILE
BBBUNKNOWN
CCCDESKTOP
The select statement should return following rows from TABLE A
COL_A1COL_A2
BBBTABLET
DDDUNKNOWN
Select Query With Four Table Its Generating Around 650 Rows?
Feb 20, 2013i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.
for example
update ps_po_lining b
set y.recv_req = 'N'
where recv_req in
[Code]....
this query runs but its updating 6000 rows. which is not right. we need to update what ever the select query is retrieving.
Table Has No Rows - Select Taking Time
Aug 23, 2010We were trying to insert approx 76 million records worth of approx 4 GB in a table when the insert operation failed with the archive log error.
The database was hanged, we re-started the database and currently there are no records in the table but when we are firing a select
select count(*)
from table
It's taking approx 2 mins to come out with the result.
We checked and found that there are no locks on the table currently.
what do we need to do to get the performance back
SQL & PL/SQL :: Block To Select 2 Rows From Table And Execute Them
Aug 3, 2010I have a table with name SQL_STATEMENTS and it has 1 column, Column name is STATEMENT_DESC
The contents of rows in Table above are:
select * from emp;
select * from dept;
Can I write a PLSQL block that can select these 2 rows from the table and execute them?
Select Multiple Images Using Department Field In Spriden Table To Pull Needed Id Numbers
Jan 29, 2009I have an Oracle 10g database, on the App Serv I have an image file that has 20,000 .jpg files that has an id number as each image name.I have successfully queryed the image file and posted one image to my web page matching the image id number.
sample:
select substr(spriden_last_name,1,20)||', '||
substr(spriden_first_name,1,20)||' '||
substr(spriden_mi,1,1) stname,
'<img src = "/images/&1..JPG" width="400" height="400"/>' pic
from spriden
where spriden_id = '&1'
/
the &1 is the matching id number that is input from the user.My task now is to select multiple images using a department field in the spriden table to pull the needed id numbers.I have not been successful in the proper format to pass the id number to the <img src field.
SQL & PL/SQL :: How To View Rows In Table Format Using Select Query
May 28, 2012I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo
SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
[code]...
But the case is I need to display the value in table format Horizontally as below
EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING
[code]...
SQL & PL/SQL :: Rows Depends On A Total Field
Feb 24, 2012I have a table TP having following data (Dashes used for space as i am unable to have proper alignment)
ID1-----TOT
1 ------- 5
2 ------- 7
I need a query that repeat the records depending on the field TP.TOT
e.g.
ID1 -----TOT
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
SQL & PL/SQL :: Updating A Date Field With Field From Another Table?
Nov 14, 2011I have a table called Customer_Type with following fields
Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011
Tbale 2:Customer
Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
The sql statements are below
CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),
[Code]....
Select Field Where Results Are The Same
Feb 26, 2008I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.
lets see .. so a count of records where widget = widget or something along those lines.
Select Records With Same Values Of Field?
Oct 9, 2007there 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.
SQL & PL/SQL :: Insert As Select With Clob Field
Oct 15, 2013Oracle 11.2.0.3 on Linux.
I need to execute this insert as select:
INSERT INTO TAB1 (SQL_ID,SQL_FULLTEXT)
SELECT DISTINCT t.sql_id, t.sql_text FROM DBA_HIST_SQLTEXT;
But I get this error:
ORA-00932: inconsistent datatypes: expected - got clob
This is TAB1:
SQL_ID VARCHAR2(13)
SQL_FULLTEXT CLOB Y
SQL_CUSTOM_HASH VARCHAR2(60) Y
Should I user dbms_lob package? Is so, I do not understand how.
SQL & PL/SQL :: How To Split Multi-delimited Field Into Several Rows And Add New Fields
Mar 7, 2013 I have a query that produces around 11 fields, and one of which is a multi-delimited field and the other 10 are dimension fields. I would like to split that field into several rows, and have the other 10 fields just repeated for each one. Here is an example of the data in the 11th field :
Column 11
34^56^78,59
There are two delimiters in the field, a carat and a comma. This field is used to reference document numbers that are needed to be sent in. The carat represents the word "Or" and the comma represents the word "And". I would like to have the output of each field to be a repeat of the 10 dimension fields, plus 3 new fields. The first new field would be the document number, the second new field would be the position within the original delimited field(1, 2, 3, etc.) , and the last field would be one of three logic words :First (if it is the first value), Or (if the value followed a carat), And (If the value followed a comma). Example of the output from the above value would be :
Column 11 Column 12 Column 13
34_______ 1_______ First
56_______ 2_______ Or
78_______ 3_______ Or
59_______ 4_______ And
Any thoughts on this? I have found a few solutions online on how to break up the delimited field into rows, but never with multiple delimiters or with extra logic for the added fields.
List Count Of Rows Where DATE Field Is Not Null?
Apr 1, 2010I need to list a count of rows where a DATE field is not null and group those counts by day.
Here's my sql so far...
SELECT
COUNT(DQ_DISTRBTN_DATE) as DQR_DIST,
DQ_DISTRBTN_DATE as DIST_DATE
from
ETL_PROCESS.BATCH
group by
DQ_DISTRBTN_DATE;
Because DQ_DISTRBTN_DATE contains time, how do I modify to achieve the desired result?
SQL & PL/SQL :: Select Statement That Returns Field Size?
May 17, 2010I am using 10g.
I want to do a select statement that will give me the name of the fields in a table. And I also want to know the field type and field size.
Can this be done?
SQL & PL/SQL :: Order By Field Containing Characters And Numbers (split From Convert No Rows Returned To Zero)
Jan 13, 2012I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
View 2 Replies View RelatedPL/SQL :: Select 4 Rows And Then Next 4 Rows?
May 27, 2013in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?
I have fifty rows inside the table
select * from student where rownum between 0 and 4 order by stuid ->it work
select * from student where rownum between 5 and 9 order by stuid -> did not work
what is the correct way to do it ?
SQL & PL/SQL :: Rows Not In Inner Select?
Apr 11, 2013I am trying to find records in table A that don't have any corresponding records in table B
select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)
Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.
How To Select Rows From Two Tables
Aug 30, 2007Consider the following tables
MANAGERID
101
102
103
104
MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27
write sql query to get the following output without using minus,union,intersect.
MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27
SQL & PL/SQL :: How To Select Last 10 Days Rows
Aug 2, 2012My Table structure is
SL# Start_ts
1 7/31/2012 3:53:42.864070 AM
2 7/26/2012 2:13:58.280928 PM
3 7/25/2012 2:13:41.692569 PM
4 7/21/2012 2:13:26.821070 PM
5 7/18/2012 2:13:07.827278 PM
6 7/13/2012 2:05:35.023520 PM
Question
How to select last 10 days rows only (from sysdate)
Error
1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)
2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ;
ORA-01858: a non-numeric character was found where a numeric was expected
[Code]...
SQL & PL/SQL :: Select Rows With Max Condition
Aug 13, 2010I have been trying to find out how to write the following query:
Suppose the following table:
ID seq
24 1
24 2
24 3
67 1
67 2
67 3
67 4
67 5
13 1
13 2
I would like to retrieve the rows for every different ID with its max value. For one ID that would be:
select * from TABLE where seq in(
select max(seq) from TABLE where id=24)
How can i do this for all the rows??
Select All Rows And Order By Timestamp
Dec 27, 2006I have 3 tables that I want to select all rows from and then order by the timestamp of when the row was inserted.
View 7 Replies View Related50000 Rows Only From Select - How To Increase
Jun 1, 2012I would like to know how to increase the result set of a 'Select' statement? I did a 'Select' that should have returned 36,000 rows and got only 5000 rows. What access level do I need to change this and what do I need to change? I am trying to do a migration of data from a delimited file to a table in Oracle. Yet, the data has to be filtered out prior to loading to the table?
SQLSELECT MIN(major_zipcode)
FROM TEMP WHERE MAJOR_CITIES IN (select distinct major_cities from temp);
Select All Rows Where Multiple Criteria Met?
Sep 14, 2009My table has the follwoing 3 columns (in addition to others)
Col Name = active ; type=number ; values=1 (true) or 0 (false)
col name start_date ; type=date; format=dd-mmm-yy
col name end_date ; type=date; format=dd-mmm-yy
I need to select all rows where all active=1, start_date=<today and end_date=>today
my sql is: SELECT id, start_date, end_date FROM offers WHERE (active='1' AND start_date<='14-SEP-09' AND end_date>='14-SEP-09');
However the results are not right. Example, the first row returned is: Offer5000312 01-JAN-09 11-DEC-08
This is not correct. Due to the end_date this row should not be part of the results.
