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


ADVERTISEMENT

SQL & PL/SQL :: Difference Between Primary Key And Unique / Not Null

Dec 10, 2010

what is difference between primary key and unique+not null

View 12 Replies View Related

SQL & PL/SQL :: Unique Constraint When 1 Column Is Not Null

Jun 15, 2012

I have 3 columns in a table: colX, colY, colZ.

Trying to find a way to prevent duplicates with these, but only if colX is not null.

For example, if there are already values for: colX = 1, colY = 1, colZ = 1

then:

Allowed: colX = null, colY = 1, colZ = 1
Not allowed: colX = 1, colY = 1, colZ = 1

I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.

I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.

Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.

View 4 Replies View Related

Creating Unique Constraint That Excludes Null

May 22, 2013

I have a table where I want user to fill in unique values for a field which is easy to do.

Problem is sometimes the values can be null so an ordinary unique constraint does not work because multiple null records. Is there a way of validating only non null values to ensure all data entered that is non null is unique?

View 1 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 :: 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

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

SQL & PL/SQL :: Trigger With Null Old Value

Feb 15, 2011

I have the following problem.

I have a trigger "before update" that change some values, including a timestamp column. My sql code does an update using the "returning clause" to get the values changed by the trigger. The problem is:

When I do an "update [...] returning timestamp_field", this timestamp_field has the old value equals to null (in the trigger). And, this field in the table is not null. This problem not occurs with the others fields of type number, varchar... only with the timestamp field.

Here are the code to simulate this problem:

--Table
create table TB_TEST
(
ID NUMBER(10) not null,
FLAG NUMBER(10) not null,
TAG VARCHAR2(16) not null,
TS_ATU_DTR TIMESTAMP(9) not null
);

[Code]..

Testing

exec test1;

select * from textolog order by data, texto;

Output:

DATA TEXTO
--------- ----------------------------------------------------------------------------------------------------
15-FEB-11 1:old.FLAG=123, :new.FLAG=321
15-FEB-11 1:old.TAG=teste trigger, :new.TAG=teste trigger
15-FEB-11 1:old.TS_ATU_DTR=, :new.TS_ATU_DTR=
15-FEB-11 2:old.FLAG=123, :new.FLAG=321
15-FEB-11 2:old.TAG=teste trigger, :new.TAG=10
15-FEB-11 2:old.TS_ATU_DTR=, :new.TS_ATU_DTR=15/02/2011 11:07:38.094284000

-----

"old.TS_ATU_DTR=, " Isn't it right?????
Why the other fields aren't null?
I need the "old.TS_ATU_DTR" to use in my other trigger to compare timestamps, how can I get it?

Or, is there a patch for this problem?

View 7 Replies View Related

PL/SQL :: First Three Not Null Attributes

Oct 29, 2013

MY requirment is: I want the first three nullable attributes. For Eg: If I have 60 columns in table, I need to fetch the first three  null data in a row. 

View 7 Replies View Related

Trigger With Null Old Value

Feb 15, 2011

I have a trigger "before update" that change some values, including a timestamp column. My sql code does an update using the "returning clause" to get the values changed by the trigger. When I do an "update [...] returning timestamp_field", this timestamp_field has the old value equals to null (in the trigger). And, this field in the table is not null. This problem not occurs with the others fields of type number, varchar... only with the timestamp field.

Here are the code to simulate this problem:

--Table
create table TB_TEST
(
ID NUMBER(10) not null,
FLAG NUMBER(10) not null,
TAG VARCHAR2(16) not null,
TS_ATU_DTR TIMESTAMP(9) not null
);
[code]...

Why the other fields aren't null?I need the "old.TS_ATU_DTR" to use in my other trigger to compare timestamps, how can I get it? I am using Oracle 11.2.0 - Suse Linux.

View 4 Replies View Related

SQL & PL/SQL :: Select Null Value

Feb 18, 2011

I have a sql queries in that i saw one sql querie like this

select columnname,null columnname from tablename

what is the meaning of this which i marked with red (null columanmea)

View 2 Replies View Related

If Column A Is NOT NULL Then Column B Cannot Be NULL

Dec 2, 2011

i've got a project and im wondering whether it is possible to create an if statement that says something like

"If column A is NOT NULL then column B cannot be NULL"

would I have to do this as a trigger or a constraint?

View 2 Replies View Related

SQL & PL/SQL :: NULL Usage?

Mar 31, 2011

when to use || NULL oerator or what is the meaning of this statement when it is used with the column name.

and A.RtmtCd = B.RtmtCd|| NULL

or

A.RtmtCd||NULL IS NOT NULL

View 5 Replies View Related

SQL & PL/SQL :: Count No Null

Jul 13, 2011

SQL> select * from test11;

A B C
--------------------------------------
AA 123
BB 100 200
CC 300
AA

How to count no null in the above table

View 9 Replies View Related

SQL & PL/SQL :: Variable Is NULL

May 21, 2013

I can't figure out why my variables are not filled up?

TEST CASE

CREATE TABLE LIST_STEP_LINK
(
FAL_SCHEDULE_STEP_ID NUMBER(12) ,
FAL_SCHEDULE_PLAN_ID NUMBER(12) ,
SCS_STEP_NUMBER NUMBER(9) ,

[Code]....

Result:

SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> DECLARE
2 VAR_SCS_LONG_DESCR FAL_LIST_STEP_LINK.SCS_LONG_DESCR%type;
3 VAR_SCS_FREE_DESCR FAL_LIST_STEP_LINK.SCS_FREE_DESCR%type;

[Code]....

PL/SQL procedure successfully completed.

SQL>

View 13 Replies View Related

Function With Null Parameters

Mar 17, 2009

i have a function which takes in two variables and return a varchar.

ex: Function(var1,var2) return as varchar2.

in the function,i query a table for var1 and var2 and concatenate the result set to return a varchar. But if either var1 or var2 is null,then my query in the function fetches the result set for the other variable.

My question is,how would i pass a null value through the function and handle it in the function.

View 1 Replies View Related

Handling NULL Value In XML Using GetStringVal?

Dec 13, 2012

I have below query which works fine if column 'XML_COL' has values. This select statement fails if the value is NULL for

select xmltype(t.xml_col).extract('//fax/text()').getStringVal() from mytab t

How to handle rows with NULL values in the column 'XML_COL'.

View 3 Replies View Related

Select Only Non-null Columns?

Sep 19, 2006

I have a table containing hundreds of columns and I would like to be able to qualify my select statements so that only those columns containing a value are returned. Something like:

Select (non null columns) from tablename where columnX = 'whatever'

View 7 Replies View Related

Select Into With Null Values?

Apr 20, 2007

i have a stored proc where i am selecting a value into a variable like so:

SELECT FUNCTION
INTO V_FUNCTION
FROM FUNCTION_TABLE
WHERE FUNCTION = P_INPUT;

Now, my problem lies in where there is no value returned (oracle will throw an error).

View 3 Replies View Related

How To Catch Null Value With C# Code

Jan 5, 2010

how to catch a null value with C# code?

My Asp.Net/C# application is connected to an Oracle database where it retrieves database values and also values calculated/returned from Oracle functions. My problem is that sometimes an Oracle function I calling returns a null value because of not correct input parameters which will cause Oracle function to (I think) return an null value. My problem and question is how to catch/obtain if he Oracle function returns a null value? Oracle function should return a decimal value if correct input parameters. Below is my row of code that crashes when Oracle function returns a null value in case of incorrect input parameters.

decGrossFreightRevenue = (decimal)(OracleNumber)cmdCalculateRevenueCall.ExecuteOracleScalar();

how to change my code to catch a null value and avoid a crash. I have tried several checks (if statements) without success.

View 2 Replies View Related

Index On Null Column

Oct 31, 2012

I have a table with column A which contains very few null values. I need to select these rows. I am considering two options:

a) create function based index on NVL(A, 0) and use this in where clause NVL(A, 0)=0 (column doesn't have values 0)
b) create function based index on NVL2(A, 0, NULL) and and use this in where clause NVL2(A, 0, NULL) = 0

First idea was option A. But I realized in option B the index will be much smaller, because most of values of column A isn't NULL so NVL2 will return NULL and index will not have as much leafs as in NVL. It is good idea to use NVL2? Is there any against to use option B instead of A?

View 1 Replies View Related

Handling NULL Values?

Jan 23, 2013

I have 2 tables, AFF_TEMP and COUNTY
AFF_TEMP has the following columns FNAME, LNAME, EMAIL and COUNTY
COUNTY has 2 columns COUNTY_ID and CNAME

Both tables have the following test data
AFF_TEMP
Joe, Bloggs, joe@gmail.com, ''
Ann, Bloggs, anne@gmail.com,Donegal

and COUNTY column in AFF_TEMP can contain a NULL value

County table has the following Test data,
1, Dublin
2, Donegal
3, Tipperary,
4, Galway

I am trying to select the following from both tables FNAME, LNAME, EMAIL, COUNTY_ID.Tried the following queries
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A LEFT OUTER JOIN COUNTY C ON A.COUNTY=C.CNAME
OR (A.COUNTY IS NULL)
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A, COUNTY C
WHERE C.CNAME IN (SELECT UPPER(A.COUNTY) FROM TEMP_AFF A)

[code]...

View 3 Replies View Related

Replacing Null Values

Apr 23, 2010

Is there a way to replace a field with another if the particular value returned is null?

View 11 Replies View Related

SQL & PL/SQL :: Select NULL Using REGEXP_SUBSTR?

Feb 2, 2011

My Database is in Oracle 11g.

My query returns the 3rd field from a CSV string. If the third field in the string is empty I want the select to return a null but it returns the 4th field :

SELECT REGEXP_SUBSTR( 'A,B,,D,E','[^,]+',1,3) from dual;

.. this returns 'D'.

Can we somehow make the REGEX_SUBSTR return a NULL for the third field ?

View 10 Replies View Related

SQL & PL/SQL :: Decode Functionality With Null

Jun 29, 2010

Explain me the following behavior of DECODE?

SQL> select decode(null,null,'Matched','Not Matched') from dual;

DECODE(
-------
Matched
As expected, output should be 'Not Matched' instead of 'Matched'

View 10 Replies View Related







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