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.
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
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
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
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 RelatedI 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?
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 RelatedThe 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.
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
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 Relatedi 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]....
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.
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
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]....
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?
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
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]...
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.
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.
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
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
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 RelatedI 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?
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.
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
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 RelatedI 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);
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.
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;
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