PL/SQL :: Selecting Data For One Day
Jun 18, 2013
I want to select data inserted in the table for that day only.
Table name -->ADJCOLUMNS
i want to select areAccount_no-->number datatype TRANSACT_DATE-- NOT NULL DATE I have written the query below .Is the below query correct.
select account_no,to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') T_date from adj
where to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') between
to_char(TRUNC(sysdate),'DD-MON-YY hh24:mi:ss') AND
to_char(TRUNC(sysdate+1) - 1/86400,'DD-MON-YY hh24:mi:ss');
View 4 Replies
ADVERTISEMENT
Jul 15, 2013
I have table TEST_REP with below data
DA SUMA
---------------------- ----------------------
2011 2
2011 3
2011 5
2012 2
2012 7
2014 2
2014 10
2015 2
2016 33
2015 26
2017 21
2017 2
2018 23
13 rows selected
I have used following query to get the below output:
select
br_mat MAT_YEAR,
sum(br_par) TOTAL
from (
(select to_char(da) br_mat,suma br_par from test_rep)
UNION ALL
[code].......
Output :
MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 23
2018 23
2019 0
2020 0
10 rows selected
Expected Output :
MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 and Greater 46
View 6 Replies
View Related
Feb 7, 2007
Vehicle 1 Odometer OilLife Drop
18454 85.9% < 1st Oil Change keep
20000 35.9% keep
22781 21.5% keep
25911 89.4% < discard 2nd Oil Change keep
28598 74.1% < discard from data
32365 65.1% < discard from data
46000 45.4% < discard from data
49343 94.4% < discard 3rd Oil Change
Vehile 2 Odometer OilLife..
This shows a drop in oil life. I only want up until the FIRST Oil Change happened. So when the Oil Life drop goes up stop selecting data.
View 1 Replies
View Related
May 27, 2011
I am new to oracle and have an issue with selecting data from a table on date criteria.
select * from table1 t where t.DT BETWEEN TO_DATE(sysdate-1,'dd/mm/yyyy') AND sysdate
my understanding is that this should give me the data for just one day. but it is not.
What I want is, data for last 30days from today.
View 2 Replies
View Related
Sep 7, 2013
I'm facing ORA-28500 error while configuring HSODBC to SQL Server.
Below is the complete information related to listener.ora, sqlnet.ora and tnsnames.ora files. Listener status is showing dg4msql instance successfully started.
I guess I'm facing some issue while creating DB link.
In Oracle 11g Release 11.2.0.1.0 we need to create DB link as below
CREATE DATABASE LINK "DBLNK_ETM_PRODUCTION"
CONNECT TO "USER_NAME" IDENTIFIED BY VALUES 'Password'
USING 'ServerIPAddress:PortNumber/SID';
How to create DB link to SQL Server.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE11.2.0.1.0Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
---------------------------------------------------------------------------------------------------------------------
$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
[Code]...
CREATE PUBLIC DATABASE LINK DBLNK_1 CONNECT TO "manoj" IDENTIFIED BY "mypassword" USING 'SQLDB' ;
SELECT * FROM MyTable@DBLNK_1 ;
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver][libssclient24]General network error. Check your network documentation.
{08001,NativeErr = 11}[Oracle][ODBC SQL Server Driver][libssclient24]ConnectionOpen (Name or service not known()).
{01000}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
ORA-02063: preceding 2 lines from DBLNK_AVL_CLUSTER2
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
Error at Line: 5 Column: 20
View 21 Replies
View Related
May 9, 2010
how to select the sixth highest earner in my employees table.how to select 6th lowest earner..
View 18 Replies
View Related
Jul 3, 2011
I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.
CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)
[code]....
View 17 Replies
View Related
Sep 7, 2007
Have a table like this:
ID1ID2DATEID2Value
1121/1/20066
1241/1/2006400
1246/1/2006410
1366/1/2006100
2121/1/20077
2246/1/2007350
2247/1/2007360
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1246/1/2006410
2247/1/2007360
I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.
View 3 Replies
View Related
Jul 6, 2012
I need to find the top two values value for each ID Number:
CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));
INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');
Desired Result:
ID Number 1st 2nd
12345679 30 30
99999999 30 25
View 7 Replies
View Related
Mar 29, 2013
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
[code]...
SELECT job_request_id,
CAST (COLLECT (USER_ID) AS SYS.OdcinumberList) user_ids
FROM mytable
GROUP BY job_request_id;
ORA-22814: attribute or element value is larger than specified in type
View 6 Replies
View Related
Jan 31, 2013
I have tables SUBJECT(subject_id, name, number) and PS(ps_id, subject_id, student_id). I need to select all from SUBJECT,subject_id and student_id from SP, joined by subject_id, where student_id needs to be read from session. I'm using asp.net with oracle database. How to get the value from the session.
View 2 Replies
View Related
Jan 15, 2011
how can I select whole table in parts of 100 rows?
If I have primary key I can:
CODEstart=0;
end=100;
select * from table where ID>=start_point and ID<end;
start=end;
end=end+100;
and repeat:
CODEselect * from table where ID>=start_point and ID<end;
How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?
View 1 Replies
View Related
Jan 19, 2011
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]...
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]...
One person (zmo_kodas) can have a lot of reports (ats_nr).
Every report can have one or more services (pas_kodas).
View 1 Replies
View Related
Sep 10, 2012
any way to select the clob value from dblink.?
View 7 Replies
View Related
Feb 25, 2010
I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:
SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID
However, I only need the DATABASE piece selected in my FOR loop below.
CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;
[Code]...
View 2 Replies
View Related
Apr 2, 2010
I'm putting together a path to select a revision of a particular novel:
SELECT e.documentname, e.Revision, e.VersionNumber
FROM Catalog, BookInCatalog
INNER JOIN NovelMaster
INNER JOIN HasNovelRevision
INNER JOIN NovelRevision e
LEFT JOIN NovelRevision s
[code]...
My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.
When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.
One other challenge is the format of the revision- a revision sequence could look like this:
A
B
C1
C2
C
D
E1
E
So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:
A1
A
B1
B
I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).
View 12 Replies
View Related
Jan 19, 2011
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]
One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).
View 2 Replies
View Related
Mar 21, 2013
correct this one.
declare
v_ename varchar2(10):='emp';
begin
for j in (select ename from v_ename)
loop
dbms_output.put_line(j.ename);
end loop;
end;
getting error v_ename table does not exists. i should use v_ename, as i dont know the table name.
View 4 Replies
View Related
Aug 23, 2012
have a bit of a SQL trouble. I have a simple table (pcuk_BG_alloc_TAB) which stores Parts, Quantities and Applied dates
PART_NO QUANTITY APPLIED
PartA 100 10/8/2012
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 50 10/8/2012
PartC 75 15/8/2012
PartC 80 21/8/2012
I am only interested the latest applied date for each part and am looking for this to be returned in a select statement (as below)
PART_NO QUANTITY APPLIED
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 80 21/8/2012
I have tried using the max function (select part_no, quantity, max(applied) from pcuk_BG_alloc_TAB group by part_no, quantity) but seems as the records have different quantities it treats them separately.
View 21 Replies
View Related
Sep 19, 2013
I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way
SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /
#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US
- - - - - - - - - - - - - - - - -
Desired Output:
#ENCODING WINDOWS-1252
Language Section EN-US
Catalog Section Title Date Source
View 12 Replies
View Related
Aug 19, 2010
I'm selecting a set of records from one table, for example: ID, description and date. From this I'm only wanting the latest inserted row. I've used the max function on the date which is fine, however, there are some records that have had their description changed. This then returns two values for one ID, the max for the original description and the max for the changed description.
I'm getting:
ID |Description |Date
1 ABC 01/01/2010
2 XYZ 02/03/2010
2 XYZ1 03/05/2010
When I want:
ID |Description |Date
1 ABC 01/01/2010
2 XYZ1 03/05/2010
As ID 2 with XYZ1 Description is the very latest row for that ID.
This is an audit table so the ID appears on numerous rows as it a composite key with date.
View 1 Replies
View Related
Apr 24, 2012
I just want to know that "is it safe to select a columns using ROWID in a table?"
View 3 Replies
View Related
Oct 26, 2010
Is information in v$sql enough to select all queries executed between given date and now? When the queries are removed from v$sql?
View 1 Replies
View Related
Sep 13, 2010
I am trying to select the owner of a certain object, only knowing the name of the object and the user calling it.
Problem is this object might exist in more than one schema with the same name, and I only need the one that is called by the active user, which himself/herself might have access to other schemas containing their own copy of the object.
Example:
SELECT OWNER
INTO v_schema_name
FROM ALL_OBJECTS
WHERE OBJECT_NAME = p_object_name;
This works fine, until the object exists in more than one schema to which the current user has access.
View 4 Replies
View Related
Sep 26, 2013
I have a table with following structure:
CREATE TABLE ID_comments
(
ID CHAR(10 BYTE) NOT NULL,
S_COMMENTS VARCHAR2(255 BYTE),
P_COMMENTS VARCHAR2(255 BYTE),
C_COMMENTS VARCHAR2(255 BYTE)
);
For each Id, I can have multiple records.
Below is the insert script of one of the ID:
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE','');
Insert into ID_comments values ('0813654254','','JR','');
[code].......
Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.
View 14 Replies
View Related
Jul 5, 2010
i created a form by using wizard...i want to improve that form...
I created one lov which is attached in one of form field..now when i press f9 lov displayed..
But when i select any one record in that LOV , its does not come in my form field
View 7 Replies
View Related
Apr 9, 2013
I have a field which stores notes. These notes are either just text, just numbers or a mix of the two.
I am looking for a way to return the fields containing a mix of the two. For example
123
abc
12cd
Would just return 12cd.
View 7 Replies
View Related
Nov 21, 2012
I have a table with a count of customers for about a year, but I only want to select Wednesday and Thursday of each week, starting at the beginning of dates. Table is simple and has two columns. Each row is Distinct to a Date, there are no Date duplicates, it's counted for each day of the year.
column 1: count of customers, count(customer_id)
column 2: Date
Not even sure if this is possible to select a date using the day name ?
Basically I want to select each Wednesday and Thursday from each week and compare the counts week over week, over week for all week to see if the counts are going up or down, to get trends .
View 4 Replies
View Related
Jun 7, 2012
I have records like the following
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N
ABCD 2/14/2012 N
ABCD 2/20/2012 Y
ABCD 3/01/2012 N
ABCD 3/10/2012 N
[Code]...
I have to write a select statement to to keep the first record and then pull only the records when the Valid_Flag changed. The result set should be like below.
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N -- I have preserved the first record
ABCD 2/20/2012 Y -- Valid_Flag chages to a Y for teh first time and so on.
ABCD 3/01/2012 N
ABCD 3/14/2012 Y
ABCD 3/25/2012 N
ABCD 4/25/2012 Y
If there is no change in the flag, I do not have to pull that record.
View 3 Replies
View Related
Dec 5, 2012
I have a table test with 10,000 records in it and 50 columns.I have to select those rows which contain values as "Sales Dum" in their field..For table with small number of colums i did this
SELECT * FROM tbl_website_dtl WHERE created_by like '%Sales%' or website_name like '%Sales%' or website_code like '%sales%';But should i do for table containing 50 columns.
View 5 Replies
View Related
