SQL & PL/SQL :: How To Get Next Not Null Value For Current Row

Dec 14, 2010

following is my input data:

SELECT TO_DATE('21-NOV-2010') DAY, 0 RATE FROM DUAL
UNION
SELECT TO_DATE('22-NOV-2010') DAY, 10.5 RATE FROM DUAL
UNION
SELECT TO_DATE('23-NOV-2010') DAY, 0 RATE FROM DUAL
UNION
SELECT TO_DATE('24-NOV-2010') DAY, 0 RATE FROM DUAL
UNION

[code]....

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related

SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row

Aug 29, 2012

I have the following query:

select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;

What I want to add is the following:

COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G

I need to connect the NON-NULL rows to the preceding NULL row.

View 15 Replies View Related

SQL & PL/SQL :: Counting NULL Vs NON-NULL In A GROUP BY Clause

Jun 21, 2010

I am running a GROUP BY query on a few columns of enumerated data like:

select count(*), Condition, Size
group by Condition, Size;

COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S

Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.

For example:

COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL

View 9 Replies View Related

SQL & PL/SQL :: Date Field - Not Null Column To NULL

Mar 16, 2011

I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.

I am using below query.

ALTER TABLE T_test
modify (paid_to_date null)

View 9 Replies View Related

SQL & PL/SQL :: Unique Null And Multiple Non-null

Oct 24, 2013

create table test
(
id int ,
dat date
)
/

I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.

create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);

insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);

The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this

iddat
------------
124/10/2013
123/10/2013
123/10/2013
1

because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.

View 2 Replies View Related

SQL & PL/SQL :: How To Modify Null Column To Not Null

Jan 9, 2012

when i follow this steps mention on this website

[URL].........

to modify column from null to not null i got this error and on this website its show successful

my steps are

first i create a table

SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));

Table created.

Then insert some rows into Stu_Table

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');

1 row created.

SQL> select * from Stu_Table;

ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh

Table Structure is like this

SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)

now when i try to modify this Stu_id column to not null its give me error.

SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

and when i try to add new column with not null its also gives me error

SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

View 6 Replies View Related

SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?

Jul 12, 2012

I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :

Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

View 8 Replies View Related

PL/SQL :: How To Update Null Data With Previous Not Null Data

Oct 25, 2012

Initially i have inserted the data into table like

Date                   xxx       yyyy
1/1/12 1 1
2/1/12 null null
3/1/12 null null
4/1/12 1 1
5/1/12 1 1
6/1/12 null null

in above example data is null for some date here my requirement is how can i copy before not null data(1/1/12) to *2/1/12, 3/1/12* .

View 3 Replies View Related

SQL & PL/SQL :: Print NULL Entry Along With NOT NULL Entry

May 30, 2010

Suppose that, I have two tables: emp, dept
emp records the empid, emp_name, deptid
dept records the deptid, dept_name

Here is a record, it's a president or some special position in company, so it's deptid is set to NULL. Here comes the question, how can I print all the emp_name with their deptartment name?

I know how to print all the emp_name with their department name if they have dept_id, but is that possible that I merge the record with dept_id NULL?

View 9 Replies View Related

Where Current Of - For Update Of

Oct 5, 2010

I have an employee table that has a paygrp_id that will be used for my subset of employees. For all the employees that have the paygrp_id = 10212 on the employee table I need to update the workbrain_user table to set the flag wbu_cansee_self to 'N'. The join between the employee table and the workbrain_user table is the emp_id.I get the following error when I run this cursor.

[error]
Error on line 0
DECLARE
CURSOR wbuFlag_cur
IS SELECT e.emp_fullname, e.paygrp_id,wbu.WBU_CANS
[code]...

View 3 Replies View Related

SQL & PL/SQL :: How To Get The Name Of Current Procedure

Jul 13, 2011

I have a procedure like below:

create or replace procedure sp_test
is
vs_proc_name varchar2(40);
begin
--get the name of current procedure ,here is "sp_test"
--[color=red]but ,i do not want the hard code here[/color]
insert into test_Page(proc) values(vs_proc_name);
---
--
commit;
end;

I don't want to coding like this:

vs_proc_name:='sp_test';

so ,is there any building function to get that ?

View 5 Replies View Related

Update Where Current Of

Sep 27, 2010

I have a table I need to update called employee_history. It has almost the exact same values as another table called Employee. The difference being that the employee_history has a start date and end date. So it allows you to have overrides in the future. The employee table is static. So it only allows you to see the current data as of today.

I need to write an insert statement that will pull values from employee table and populate the employee_history table but I also have columns in the employee_history table that requires a next value and a start and end date.

From the employee table I want to select e.emp_id, e.emp_firstname, e.emp_lastname, e.shftpat_id, e.client_id from employee e

I want to INSERT INTO EMPLOYEE_HISTORY (EMHIST_ID, EMPHIST_START_DATE, EMPHIST_END_DATE, EMP_ID, EMP_FIRSTNAME, EMP_LASTNAME, SHFTPAT_ID, CLIENT_ID)

with the VALUES being (SEQ_EMPHIST_ID.nextval, TRUNC(sysdate), 01/01/3000 and the employee data from the employee table)). I bought the book Easy Oracle PL/SQL Programming but I am not finding what I need. It gives an example of a CURSOR FOR UPDATE and a reference to an UPDATE WHERE CURRENT OF.

The examples are straight forward where you take two columns from table one and update a new table with those values, or where you select a subset of employees that need a commission and update the emp table with that commission.

View 1 Replies View Related

Get Current Date And Time?

Apr 24, 2005

I only know to retrieve data (date & time) by the following statement : "select sysdate from ..."

but now I want to make the fields current date and current time separately and put in a adult file with these two fields. how to do it.

View 11 Replies View Related

SQL & PL/SQL :: Bulk Collections And Current OF

Sep 2, 2011

I have a driver table from which I need to update another table while, at the same time, record the fact that I have processed each record on the driver table.

The driver table will contain around 3.5 million records, therefore I intended to handle this using some bulk collections, with a LIMIT option so that I don't hit any memory problems.

I would also prefer to commit in batches, or at least handle exceptions using the SAVE EXCEPTION clause. The problem is I seem to be running into an error when trying to make the update to the driver table (the commented out code). With this in, I get the error:

ORA-01410: invalid ROWID
ORA-06512: at "CUST_MAIL_UPDATE", line 217
ORA-06512: at line 38

Can the CURRENT OF not work with the FORALL? What is my best approach here? If I use a FOR LOOP I lose my SAVE EXCEPTIONS exception handling.

The Procedure is as follows:

-- declare some object structures to hold the retrieved data
TYPE driver_rec IS RECORD (
account_no ext_driver.account_no%TYPE,
update_action ext_driver.update_action%TYPE,
customers_rowid ext_driver.customers_rowid%TYPE);
TYPE driver_recs_tt IS TABLE OF driver_rec;
-- cursor to get the records from the driver table
[code].......

View 10 Replies View Related

Forms :: Getting Current Hostname

May 9, 2011

how can I get the form hostname that it is currently running to? I already tried the form's (forms developer 6i) built in host functionality but it doesn't return the hostname.

Our current approach is to have a shell script that will get and insert the hostname into the table and the form will just query from the table to get the hostname, but that approach has involved too many modules.

Is there an easiest way to get the hostname?

View 5 Replies View Related

SQL & PL/SQL :: Range Between X Preceding And Current Row?

Aug 26, 2011

I have a table and want to calculate the stdev of a parameter over a rolling window (past 250 records, sorted by ID):
...
stddev(parameter) over (order by ID range between 250 preceding and current row) as SD
...

I don't get any error, but if I calculate the same in excel (=STDEV(250 row range)), it seems that this code does not the same. Is there something wrong with the 250 row rolling window?

View 4 Replies View Related

Application Express :: How To Get Current Tab Name Or Tab ID

Dec 5, 2012

I would like to hide/show tabs according to users' page privileges list. So I need get current tab ID or tab Name in runtime environment to know if show/hide it for current user.

View 6 Replies View Related

PL/SQL :: Current Quarter End Date

Jan 25, 2013

My below query is working fine with sysdate but when m passing a date it's throwing an error to get the current qtr end date..

select add_months(trunc('20-dec-2012','q'),3) - 1 from dualERROR

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

even after adding the to_date function between trunc and date.                             

View 5 Replies View Related

MTS Listener Current Connection

Jan 11, 2013

I have configured MTS server with this parameters:

shared_servers : 42
max_shared_servers : 42
dispatchers : (protocol=tcp)(dispatchers=5)(LISTENER=MDB)

-bash-4.0$ lsnrctl service

LSNRCTL for HPUX: Version 11.2.0.1.0 - Production on 11-JAN-2013 12:21:51
Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=offdb2)(PORT=1522)))
Services Summary...
Service "MDB" has 2 instance(s).
Instance "MDB", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:15388 refused:0
LOCAL SERVER
[code]....

From doc. I see that the "current" is the number of client connections it is handling, that is, its current load.It is not correlating with the sessions or processes in DB, so what does this means?

Does it show that more than 150 connections try to perform connection simultaneously?

View 8 Replies View Related

PL/SQL :: Query By Current Month

Jul 12, 2013

I have an excel spreadsheet that queries an oracle data for some information.  I am trying to get the information shown to only be from the current month.  I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out.   Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".

View 6 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

Find Records That Have Most Current Date?

Apr 13, 2004

I have two tables

table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004

table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|

table_1 contain records on employee and the supervisor they are under at a certain date.

As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.And from table_2, i'll like to extract the employee's name. These records extracted from both the tables would the be put into a new table,table_3

Example: For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004

table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004

How to write an SQL statement to perform this?

View 6 Replies View Related

SQL & PL/SQL :: Query To Update Current Value In DBA View To New One

Jul 18, 2012

I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:

CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)

i.e. resulting output should be:

CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';

(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.

View 26 Replies View Related

Forms :: How To Display Current Record

Mar 12, 2013

I have problem about displaying current records with same table(tbl_pawnitem). I have 2 transactions which are the pawned transaction and for sale transaction. and both transactions save into tbl_pawnitem table..i want to display only in my list the for sale transaction without deleting the records of pawned transaction. what condition i will use?

View 5 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 :: Create Table With Current Date

Jul 22, 2010

How can we create a table with current date in oracle this is to be done daily a job as following today's table name is MYTABLE-2010-07-23

so every day a job is to create a table with tablename-current date

is this possible? and how to

View 11 Replies View Related

Forms :: Order By Current Month

Jan 30, 2011

A form has date of birth column.

Date of birth should be displayed according to current month.

Example:
01-jan-1980
01-feb-1975
01-mar-1970

Required:

Let current month is feb then
01-feb-1975
01-mar-1970
01-jan-1980

What should be done?

View 5 Replies View Related

SQL & PL/SQL :: Identifier In CURRENT OF Clause Is Not Cursor Name

Jun 30, 2011

set serveroutput on
declare
type curs_type is ref cursor;
rec employees%ROWTYPE;
curs curs_type;

[code]...

It gives the error "identifier in CURRENT OF clause is not a cursor name" even though the identifier "curs" is a cursor name

View 3 Replies View Related







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