I try to create table and index with tablespace name but tablespace_name column in tables user_tables, USER_INDEXES seems null.
CREATE TABLE TEST_SAMPLE_OUTPUT
(
SAMPLE_ID NUMBER,
SAMPLE_NAME VARCHAR2(120) NOT NULL,
SAMPLE_TYPE VARCHAR2(200)
[code].......
Firstly the table structures :
Create table cd_patient (pat_mrn varchar2(100)) ;
Create table cd_encount (pat_mrn varchar2(100), encounter_id varchar2(1000));
I need to write a code which dynamically reads the table name from user_tables (starting with cd) & load the data from remote database which has the same table name & structure based on the load number..
Static PLSQL statement would be :
declare
BEGIN
INSERT INTO "cd_patient" C-- DYNAMICALLY GET THE TABLE NAME
SELECT C1.PAT_MRN -- DYNAMICALLY GET THE COLUMN NAME
FROM REMOTE_DB.CD_PATIENT@XXX C1
WHERE C1.LOAD_NUMBER > 2;
[code]....
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.
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
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)
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.
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
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
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* .
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?
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]....
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?
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 RelatedI 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.
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)
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?
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
SQL> select * from test11;
A B C
--------------------------------------
AA 123
BB 100 200
CC 300
AA
How to count no null in the above table
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>
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.
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'.
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'
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).
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.
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?
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]...
Is there a way to replace a field with another if the particular value returned is null?
View 11 Replies View RelatedMy 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 ?
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'
I have a two tables with same column name , I wanted to find different record in table1 when compared with table2
create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);
create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);
insert into table1 values(1,2,NULL,NULL,NULL);
insert into table2 values(1,2,NULL,NULL,NULL);
commit;
select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);
no rows selected
how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so
NULL-NULL cannot be equal to zero how is it possible