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.
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.
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 ...
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?
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?
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
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:
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?
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
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
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).
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.
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 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);