SQL & PL/SQL :: Command To Take Previous Day Upto Midnight

Mar 19, 2013

What command can be used to take the previous day upto mid night?

For example

TO_DATE('18-MAR-13 23:59:59', 'dd-mon-yy hh24:mi:ss')

Instead of me entering the date any way to take the previous day till mid-night.

I dont think sysdate-1 will work for me because if I enter sysdate-1 it will take from now -1 that means 18-mar-13 15.45.45 but I want till the previous date until mid-night.

View 3 Replies


ADVERTISEMENT

SQL Plus Command Line - Arrow Keys Not Giving Previous Commands Back

Mar 28, 2007

I am using Oracle 10g Express Edition on Fedora core 5 32+ bit os. The problem is when I use the SQL Plus command line to make SQL statements I can not get the previously typed command back at the prompt when I use the up and down arrow keys on my keyboard. This is quite easy when I am using a shell, but here with this Oracle command line interface it is not working at all. Here is the example as what actually is happening whe I press the up or down arrow keys.

SQL> drop table mailorders;

Table dropped.

SQL> ^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A

View 2 Replies View Related

PL/SQL :: Time From Midnight

Oct 17, 2012

query to identify the time from midnight.

For ex. 3/06/2012 2:29:24 AM means 2:29 from midnight of 3/06/2012 and 21:31 from midnight of 4/06/2012

View 4 Replies View Related

SQL & PL/SQL :: Refresh View At Noon And Midnight

Jun 23, 2010

I would like to set my next refresh on my materialized view so that my view refreshes daily at noon and midnight.

Initially I tried:

sysdate + 12/24

The problem with this is that I find my times tend to 'drift' a little bit each time the refresh is done. I wan't my refresh EXACTLY at noon and midnight.

next I came up with:

decode(to_char(sysdate,'AM'),'AM',trunc(sysdate) + 12/24,trunc(sysdate) + 1)

Does this look like it will accomplish what I need? Also, when I try setting next refresh to this using the Enterprise Manager Console, I get an error:

Ora-06550: Line 4, Column 43:
PLS-oo103: Encountered the Symbol "AM" when expecting one of the following: . ( ) , * @ & + - < / >at in is mod not rem <an exponent(**)> <> or != or ~= >= <= <> and or like between ...

The database in question is version 9.2

View 5 Replies View Related

How To Rebuild Huge Indexes Upto 30gb In Size

Sep 7, 2011

i am having some indexes upto size of 40gb, i need to rebuild them having only 15 temp tablespace and without downtime

View 5 Replies View Related

Create As Many As 100 Database With Upto 100 Users (max) For Each Database As Client

Aug 25, 2010

I have CPU based Oracle server license. In this case as per license policies of Oracle I can have unlimited database with unlimited users connecting it.

I need to create as many as 100 database with upto 100 users (max) for each database as client. What is the recommended hardware for this server? CPU/ RAM/Harddisk, recommended chipset on the motherboard (in case of database storage capacity required is not high typically - 20 GB is OK per database - average), redundancy is critical, also Oracle database vault is going to be installed; as security is of prime importance between users of different database. Which specific server models from specific vendors like IBM/ Dell/ HP are good ones for this purpose?

View 2 Replies View Related

Brtools Command Versus Oracle Command For Shutdown And Startup (statistics)

Sep 13, 2013

we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?

View 3 Replies View Related

SQL & PL/SQL :: Query For Previous Day And Day Before

Jan 15, 2013

The main condition in SQL is like this.

SELECT TO_DATE (TO_CHAR (doc_date, 'MON-YY'), 'MON-YY') "INV_MTH",
SUM (inv_amt) INV_TOTAL
FROM table_x
WHERE doc_date BETWEEN TRUNC (SYSDATE, 'YYYY')
AND LAST_DAY (
ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), 11)
);

My Output from if run in JAN as of now 16 Jan.

INV_MTHINV_TOTAL
Jan-136260830.42

I want an sql until previous day of that month for example 15 Jan and another sql until day before previous day of that month for example 14 Jan.

View 15 Replies View Related

SQL & PL/SQL :: Get Previous Value Of Rows?

Dec 29, 2010

How to get the previous value of row with calling function to add value in SELECT statement for the row value.

Consider the example Table A1 having column a with values 1,NULL,NULL,NULL

SELECT CASE WHEN a IS NULL THEN (prev_row_value+function_return_Value) ELSE a END as A from A1

And my result-set should be like

a
----------------------
1
1+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Below is sample code but doesn't fulfill my criteria

[code]....

Output is

A A2
---------------------------
1 1
3
3
3

View 8 Replies View Related

SQL & PL/SQL :: How To Know Previous Scn Number

Jun 4, 2012

i want to know the all scn number's that are generated yesterday or in any previous day? how can i achieve it?

View 5 Replies View Related

PL/SQL :: Multiply With Previous Row Value?

Aug 21, 2012

i need to multiply with previous row value?

my table have about to 100 columns and lakhs of rows

Ex:

date adj
------ -----
8/21/2012 1
1/1/2012 1
12/1/2011 0.5
8/1/2011 0.5

[code]....

My requirement is multiply the existing adj by adj

adj value coming as 0.5
present year always 1

Ex:

date adj
------ -----
8/21/2012 1
1/1/2012 1
.
.
12/1/2011 0.5
8/1/2011 0.5

[code]....

View 8 Replies View Related

Client IP Of Previous Transaction

Mar 11, 2013

Is it possible for me as a DBA to find IP address of the client who ran a specific transaction or query in past?

Oracle server version I'm using is 11g.

View 4 Replies View Related

SQL & PL/SQL :: Executing Rows With Previous Row

Nov 3, 2010

i have tablestructure like this

empno ename sal
1 sam 1000
2 tom 2000
3 ric 3000
4 mac 4000
5 doy 5000

i want TO WRITE SELECT QRY WHICH WILL GO like this

empno ename sal prevemp prevename presale
1 sam 1000 0 0 0
2 tom 2000 1 sam 1000
3 ric 3000 2 tom 2000
4 mac 4000 3 ric 3000
5 doy 5000 4 mac 4000

means when each current row executes it shld show details from previous row also means when details of tom is executing it also shows sam details

View 4 Replies View Related

SQL & PL/SQL :: Cumulative Sum Of Previous Row In Same Column?

Jun 28, 2013

Is it possible to get cumulative sum of the same column? I am trying to get a value for COL6... it is dependent on the values of previous row

COL6 Formula:

IF COL2 = 'A' THEN
IF 100 - [SUM_COL6] > COL5 THEN
COL5
ELSE
(TRUNC(100 - [SUM_COL6] / COL4) )* COL4
END IF

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Getting Data From Previous Month

Mar 8, 2011

I need to get data from a table in which dates is equal from previous month. The dates in this table has a formula DD-MMM-YY (CRE_DTTM is the name for date column).

I've already achieve getting data from the previous month by using this formula:

(to_char(CRE_DTTM,'MON')) = UPPER(to_char(add_months(trunc(sysdate,'MONTH'),-1), 'Mon'))

My problem now is what if the current month is for example JAN 2011.. I need to get the data from DEC 2010. How can I query the previous year in this case?

View 10 Replies View Related

PL/SQL :: Previous Record Find?

Jun 25, 2013

CREATE TABLE F_TIME(  PERIOD_ID      NUMBER,  PERIOD_NAME    VARCHAR2(30 CHAR),  PERIOD_YEAR    NUMBER,  PERIOD_TYPE    VARCHAR2(30 CHAR),  CREATION_DATE  DATE,  UPDATE_DATE    DATE,  UPDATE_BY      NUMBER); SET DEFINE OFF;Insert into F_TIME   (PERIOD_ID, PERIOD_NAME, PERIOD_YEAR, PERIOD_TYPE, CREATION_DATE,     UPDATE_DATE) Values   (16, 'Q4', 2012, 'q', TO_DATE('04/20/2013 17:41:28', 'MM/DD/YYYY HH24:MI:SS'),     TO_DATE('04/20/2013 17:41:28', 'MM/DD/YYYY HH24:MI:SS'));Insert into F_TIME  

[code]...

if i pass 30 then it will return period id=16 data

View 3 Replies View Related

Compare Current With Previous Row Until Mismatch

Nov 2, 2011

We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:

CODEemp_salary
----------
emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL

Primary key - emp_id, effective_date..This table is yearly partitioned...I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:

CODEWITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary

[code]....

For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.Below query will give that result:

CODE
WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Find Previous Cursor Loop Value

Dec 15, 2011

I take a select into a cursor and process it record by record.I have to do sum based on a column and display row by row by using dbms_output.put_line .... So the sum has to happen based on a column. Based on the column value i need to display the cumulative sum as well.

Example:-

col1 col2 amount
DL AADD 25
DL BBCC 10
DL BBRR 15

Sum value for DL ----- 50

TX ADED 20
TX EDWW 60

Sum value for TX ----- 80

All the above data should be displayed using DBMS_OUTPUT.PUT_LINE in a pl/sql code. I use cursor to take the values from the table but the problem i face is .... I am not able to display the sum based in the col1 values.

Since i use the cursor .. i took the col1 values in to a variable and checked every time
old_variable = new_variable
if yes then continue the sum
else
display the sum value.

once i get the above check satisfied i am loosing a new col1 row in the check. The next loop only run for the new col1 values -1( which is used in the check loop).So is there any better way to get the solution or is there a facility to store the previous loop values in a cursor ? so that i dont have to loose that one row of data.

I am not able to come up with proper loop so which can identify that the col1 has changed and you have to display the sum value.

View 17 Replies View Related

SQL & PL/SQL :: Previous Record For Multiple Combination

Aug 17, 2012

How to achieve "Prev_Value" column as shown below without using ORACLE analytic functions

I have records stored in table for various categories and based on ID / Name / Office / Product / Category combination I want to achieve previous value column through efficient SQL query

Test Scripts as below

CREATE TABLE TEST_Prev
(
ID1 NUMBER(3),
Name1 VARCHAR2(10),
OFFICE VARCHAR2(20),
PRODUCT VARCHAR2(20),
Overall VARCHAR2(20),
DATE1 DATE,
VALUE1 NUMBER(1)
);
commit;
[code]......

Expected output as in attached sheet.

View 11 Replies View Related

SQL & PL/SQL :: Compare Previous Records With Current?

Aug 29, 2013

I am trying to set the min date if there is no gap between dates.compare previous date2 value with current date1,if they are same then my new date will be min(date1).

source data

date1 iddate2 new_date
1/2/20111234/2/2011
4/2/20111237/2/2011
7/2/201112310/2/2011
10/2/20111231/2/2012
1/2/20121234/2/2012
4/2/20121237/2/2012
12/17/20121233/17/2013
3/17/20131236/17/2013

and I am expecting the out put like this

date1 id date2 new_date
1/2/20111234/2/20111/2/2011
4/2/20111237/2/20111/2/2011
7/2/201112310/2/20111/2/2011
10/2/20111231/2/20121/2/2011
1/2/20121234/2/20121/2/2011
4/2/20121237/2/20121/2/2011
12/17/20121233/17/201312/17/2012
3/17/20131236/17/201312/17/2012

how to achieve this with SQL

View 3 Replies View Related

SQL & PL/SQL :: How To Get Data For 25th Of Previous Month

Dec 12, 2010

i am looking for a SQL query by which i can extract data between 25th of previous month till today .

i tried the below code but no luck

SELECT TO_CHAR(SYSDATE, 'YYMM'), TO_CHAR(SYSDATE, 'YYMM') -1, TO_CHAR(SYSDATE, 'YYMM') -2, TO_CHAR(SYSDATE, 'YYMM') -3
FROM DUAL

View 4 Replies View Related

Forms :: How To Go Back To Previous Block

Feb 21, 2012

I'm making a menu in my form, wherein it has FILE, TRANSACTION and REPORT. Under FILE it has BACK, and LOGOUT. In my back menu item, i want to go back to the previous block or previous module. I used previous_block but it's not working in some of my blocks.

View 8 Replies View Related

Tar - Error Exit Delayed From Previous?

May 2, 2013

I have a tar.gz file when i issue command like this to untar,

mysql@test ~]$ tar xfvz mysql-advanced-5.5.17-linux2.6-x86_64.tar.gz

output:

mysql-advanced-5.5.17-linux2.6-x86_64/bin/mysql2mysql
mysql-advanced-5.5.17-linux2.6-x86_64/bin/mysqlslap
tar: skipping to next header
tar: Archive contains obsolescent base-64 headers incomplete literal tree
gzip: stdin: invalid compressed data--format violated
tar: Child returned status 1
tar: Error exit delayed from previous errors

But when i check i found the file like below,

mysql@test ~]$ ls
mysql-advanced-5.5.17-linux2.6-x86_64

This means its completly untar the file?Why that error is showing?

View 2 Replies View Related

PL/SQL :: Find Previous Value From Oracle Table

Jun 4, 2013

I have a stat table that got info like login_date,user_id etc.For a specific user, i have a requirement based on the no of days difference between the current login date and last login date.

For example, Tom logged in on June 4th 2013. His previous login was May 31. So no_of_days_difference is 5 days.How to programmatically get this for each user inside a pl-sql sub block.

View 2 Replies View Related

PL/SQL :: Function To Round To Previous Quarter

Feb 28, 2013

I need to have a function which will round to previous quarter

For ex :

if the value is 1.95 it should give 1.75
if the value is 1.60 it should give 1.50

View 2 Replies View Related

SQL & PL/SQL :: Function Recovery Of Previous Version

Jun 2, 2011

I've overwritten my PL/SQL function and it's not working now. Is there a way to recover the previous version of this function?

View 6 Replies View Related

SQL & PL/SQL :: Updating Current Column With Previous Columns?

Dec 22, 2011

I need to update the current column with sum of the previous column values. Following are the creation scripts

DROP TABLE TEST_LOG;
CREATE TABLE TEST_LOG
(

[Code]....

Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.

I tried by using the following query

UPDATE TEST_LOG T SET SUM_PRE_COLS =
( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0)
FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);

View 5 Replies View Related

SQL & PL/SQL :: Selecting Value Of Previous Column In Case Statement?

Feb 20, 2012

Is there a way of selecting the value of a previous column in a case statement, without having to duplicate. E.g.

SELECT prod_id as Product,
Some complex calculation... as Total,
CASE

[Code]....

I do not want to re-write the same calculation in the else statement but just want it to be be the value of Total column.

View 1 Replies View Related

SQL & PL/SQL :: Revert Back Package Changes To Previous Version?

Mar 29, 2012

I overwritten the package and want to get the previous version.

Is there a way I can get it using FLASHBACK or any other feature?

My user_recyclebin is showing only tables.

SELECT object_name, original_name, TYPE
FROM user_recyclebin;

View 10 Replies View Related

Forms :: Repeat Same Values To Next Record From Previous?

Jun 16, 2011

How do you repeat the same values to the next record from the previous record to reduce the user entry in d2k ??

View 1 Replies View Related







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