SQL & PL/SQL :: Order By DATE Which Has NULL Values
Jun 11, 2010
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have Event_month, Event_date in SELECT statement. Results of the query has NULL values as well.. I am doing ORDER BY Event_month, and I want NULL values to be at the top..
SELECT d.event_code_dt, count(d.event_number) count1
FROM main_event a,sec_event d
WHERE a.event_id = d.event_id(+)
GROUP BY d.event_code_dt
ORDER BY d.event_code_dt, count(d.event_number) desc
Results are like this
11-MAY-10
21-MAY-10
22-MAY-10
NULL
And I want results to be like this:
NULL
11-MAY-10
21-MAY-10
22-MAY-10
Same thing happens with Event_month
NULL
Feb 2009
Aug 2009
View 2 Replies
ADVERTISEMENT
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
Dec 3, 2010
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
View 3 Replies
View Related
May 20, 2013
I am trying group by count on Monthly basis, entered_timestamp Column is of DATE datatype.
select count(*),to_char(entered_timestamp,'MM-YYYY') MONTHLY from payments
where type = 'BOOK' group by to_char(entered_timestamp,'MM-YYYY') order by MONTHLY DESC
Above SQL doesn't gives the output in proper order ,
View 3 Replies
View Related
Mar 29, 2010
I had a table main with 2 fields. please see the contents below.
seq name
a aaa
b bbb
c ccc
d ddd
1. My query is as below
select seq,name from main where seq in ('a','b','c','b','d','d') output of query is
a aaa
b bbb
c ccc
d ddd
but I need the output to be as shown below.
a aaa
b bbb
c ccc
b bbb
d ddd
d ddd
I need to display all the values in the same order as in the "IN" list.
View 18 Replies
View Related
Jan 26, 2011
I have table called INFO and the column called CREATED_DATE . Now the datatype of CREATED_DATE is of varchar2 . Now If I need to query the table through select statement where I need to order the result based on CREATED_DATE , how can i achieve this ?
View 1 Replies
View Related
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
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
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
Dec 17, 2010
We are working on a migration project and we need to move 75 million rows from source system to target system.
Total number of columns in source system - 90 cols.
Out of the 90 columns 10 cols are system fields and rest 80 are properties for each record.
We are required to migrate all system cols and some required properties. In total we will migrate around 25 columns[10+15] for each record.
Before actaul migration , we need to do a data cleansing activity and hence we move the data to a staging table.
To create the staging table, we considered the below appraoches.
1. Create the staging table with around 30 coloumns so as to fit the data from source system[map the columns based on datatype]
2.Create the staging table with actual columns[90 columns] and import only the required properties. The rest all columns will remain NULL.
Do the data cleansing and move to target system.
My question here is, if we go with approach 2, We will not mix the data, as there will be a one-to-one mapping. But many columns will not have data and remain NULL. Will it affect the performance since we deal with 75 million rows.
View 3 Replies
View Related
Jul 28, 2010
I have a table abc with two column (marks,id) both can have any value.
Value of the id column is zero at several places.When I divide marks by id. I get divide by zero error.
how to replace zero with null.
View 22 Replies
View Related
Jul 20, 2012
I have main query..And i written a Query for Issued Quantity. The query is retrieving exact data. And when i add the Query in main query the data is coming with extra null values.
for ex. Query for Issued Qty fetch 16 records. and i run the main query it fetch 20 records...there r 4 records which issued Qty is null. How can i restrict that records.
View 14 Replies
View Related
Nov 3, 2012
Oracle version : 11.2.0.2 Linux EL6 server
I have a query like below:
select col1, col2, col3, col4
from tab1, tab2, tab3
where conditions
union
[Code]...
Now, the col4 is a date column and I have to order by the entire result sets on it. I know I can do it by (order by col4) or by (order by 4) at the end of the entire query.
But the problem is that, the output is coming in dd-Mon-yyyy (i.e 31-Nov-2012).
I want every output in dd/mm/yyyy format so I need to use to_char function.
But in that case, I cant use the order by clause, because in that case it is getting arranged by character i.e by 1,2,3,4,5 like this.
View 3 Replies
View Related
May 16, 2013
10g- 10.2.0
This is my query
select to_char(exp_Date,'Mon-YYYY') dt, count(*) from exp_main
where exp_type like 'Income%Photo%'
group by to_char(exp_Date,'Mon-YYYY')
order by exp_date
When I run this I get :not a GROUP BY expression
If I remove the order by, it works fine.
Is there a way to order by since the output of the query is character-sorted.
I did search online most have suggested to use order by column_name. But does not work for me.
View 6 Replies
View Related
Jun 26, 2013
I want to do 'ORDER BY' by concatenating the two columns(date +varchar2).But not working and geting the error - ORA-01855: AM/A.M. or PM/P.M. required
SQL> create table dat2 (
mod_date date,
am_pm varchar2 (10) ) ; 2 3
Table created.
SQL> INSERT INTO DAT2 ( MOD_DATE, AM_PM ) VALUES (
TO_Date( '06/05/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '7:25AM'); 2
1 row created.
[code]...
ERROR at line 2:
ORA-01855: AM/A.M. or PM/P.M. required
View 25 Replies
View Related
Aug 5, 2010
My query is ignoring the null value
CODEselect count(*) as COUNT from EMP where EMP_ID in '1001,1002,1003'
returns the result as
CODECOUNT
----------------------
0
1
2
EMP_ID '1001' has 0 records(i.e., EMP_ID 1001 doesnt exists in the table)
EMP_ID '1001' has 2 records
EMP_ID '1001' has 3 records
where as the query
CODEselect count(*) as COUNT, EMP_ID from EMP where EMP_ID in '1001,1002,1003' group by EMP_ID
returns the result as
CODECOUNT | EMP_ID
---------------------
1 | 1002
2 | 1003
I want the null value also to be counted when I run the second query i.e., the output should be like
CODECOUNT | EMP_ID
---------------------
0 | 1001
1 | 1002
2 | 1003
View 2 Replies
View Related
Aug 23, 2010
How can I use OR condition in sql to check whether if the 6 col values in a row is NULL, I need something like below, but idk the syntax of checking if either of the mentioned cols is null using an or condition.
select a.mid
FROM table_a a
JOIN table_b b
ON a.dept = b.dept
JOIN table_c c
ON b.eid = c.eid
WHERE a.tid = :tid
AND (a.i1 or a.i2 or a.i3 or a.n1 or a.n2 or a.n3 or a.t1 or a.t2 or a.t3 ) IS NULL
View 1 Replies
View Related
Jun 17, 2010
There is a table with column holding 3 NULL values one each in every record. When a count function is applied to the column with a filter on NULLs,it returns 0 instead of 3.
But when count(*)is applied,expected result is returned. Would be interested in knowing about this strange behavior of NULL/Count.
View 3 Replies
View Related
Dec 29, 2012
My DB version is 10.1.0.5.0
I want extract the values from the string using below query but i am unable to bring the correct result.
WITH t AS ( select '123,1,3,22' col FROM DUAL
UNION ALL
SELECT '123,,2,1' FROM DUAL
UNION ALL
SELECT '5,1,2,,' FROM DUAL
)
[Code]...
My required result like below.
123 1 3 22
123 2 1
5 1 2
get required result using regular expressions.
View 3 Replies
View Related
Mar 29, 2011
i want to add the JCalendar.jar and JCalendarJinit.jar in order to use the date picker on my forms while using a windows environment,what attributes should i change or edit in the forms/server/formsweb.cfg file and what else am i required to add?
View 1 Replies
View Related
Jan 13, 2013
create table top22 (timed timestamp);
insert into top22(timed)
values (current timestamp),(null);
select timed from top22;
2013-01-14 10:50:22.17
NULL
select 'no is '||coalesce("num",'') from "bifrost"."top22"
getting error in DB2
if i remove this coalesce function
select 'no is '||timed from "bifrost"."top22"
no is 2013-01-14 10:50:22.17
NULL
but i need the second result is no is null
View 9 Replies
View Related
Mar 12, 2010
Our organization is attempting to learn more about the partitioning features of Oracle 11g. I've been reading the partitioning manuals, and I have not found a clear answer on this topic, but I suspect I know the answer.
If you create a range partitioned table; using interval partitioning, say something like this:
CREATE table range_parti (
CODE NUMBER(5),
DESCRIPTION VARCHAR2(50),
CREATED_DATE DATE)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION my_parti VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);
but you try to insert a null value as the partition key, you get the following error:
SQL> INSERT INTO range_parti VALUES (1,'one',NULL);
INSERT INTO range_parti VALUES (1,'one',NULL)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Elapsed: 00:00:00.07
Is there no way to tell it to use a default partition for NULL values? Or specifically designate a partition for NULL values WITHOUT having to manually list out each partition? It seems it works if you don't use the INTERVAL keyword, list out your partitions, and use MAXVALUE. However, our hope to avoid having that as it creates monstrously huge DDL statements for tables that have lots of date ranges, and we will be forced to manually add new partitions each month as data is added/time passes.
It appears from my experience so far, if your column can allow nulls, you cannot use interval range partitioning on that column.
View 5 Replies
View Related
May 18, 2010
I use a cursor to select records from a database table into a C structure as follows...
{
int iLoop = 0;
int iResult = 0;
[Code]....
The otc_multiplier field is NULL. As is the to_date sometimes. However, when I output the records later, the entries where the to_date is NULL come out fine (no value). But the otc_multiplier is getting output as 0.0 using...
// this is output later in another fuction using the following..
sprintf(newRecord, "%.1f",daServiceRecs->itemMultiplier);
If using c structures in this manner, what is the method for ensuring that numeric values are set to NULL when required?
View 2 Replies
View Related
Jun 21, 2010
I have a report in report 6 which has static field where values are shown for previous year and for current year. the output of the report will be like this
sl no department prev year cur year
01 revenue 2000 5000
02 costing 500 1000
03 excise 8000 900
and so on....
I have a created a fomula field for each and every department and get the value from a particular table. What i require is if suppose the department revenue does not have any value then that row should not be visible. for eg if revenue has a null value then the report should be like this
sl no department prev year cur year
01 costing 500 1000
02 excise 8000 900
View 6 Replies
View Related
Nov 22, 2012
I'm using the odp.net type factory (class IOracleCustomType and the specific annotations...) to map udts to c# classes.By now I had no problems with it, because I've never used "ref" attributes within the udts.Now I need the use of "ref" attributes within udts but it seems that the odp factory cannot handle those references.
For example:_*
A Simple Type:
create or replace type bookcontent_t force as object (
id number(20)
, name varchar2(1000)
);*And a collection with references to objects of this type:*
create or replace type fms_bookcontent_ref_cln force as table of *ref* bookcontent_t;The c# class is generated by odp.net.
When I retrieve data by using odp.net I get a collection with the correct number of elements but the values are null.What can I do to solve this problem with "ref" attributes/lists? How can I get the referenced object instances?
View 0 Replies
View Related
Jul 5, 2012
Below given is the sample data
SELECT *
FROM
(
SELECT 1 ORDR_ID, TO_DATE('01-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 5 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('15-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'KPD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('16-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'ALD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('02-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 9 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('05-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 10 SIZE_, '' Colr_CD FROM DUAL UNION ALL
[code]....
Where every first row is the initial entry for an order and following rows are the changes done to the order with modification date
And the expected output is
ORDR_ID Start_date End_date SIZE_ COLR_CD
1 01-01-12 01/15/2012 5 APD
1 01/15/2012 01/16/2012 5 KPD
1 01/16/2012 00-01-00 5 ALD
2 01-02-12 01-05-12 9 APD
2 01-05-12 01-06-12 10 APD
[code]....
I cam use a select statement within select, but wanted to write an efficient SQL...
I'm using
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0
View 2 Replies
View Related
May 3, 2012
As per NULL values concept
One NULL value is not equal to other NULL value.
But how DISTINCT and UNION eliminating NULL values.
Then how UNIQUE key constraint accepting more than one NULL value..
View 1 Replies
View Related
Oct 12, 2012
I have a table something as below
Things_t
Things Characteristic Char Value
Item 1 Colour Red
Item 1 Packaging
Item 2 Shape Square
Item 2 Brand Spunk
Now i want to reterive an item with none of its char values as Null. Using the query “ select distinct things from things_t where char value is Null ” will fetch the item 1 also together with item 2. i want to fetch a record from thing for which none of the char values are Null such as Item 2.
View 9 Replies
View Related
Aug 5, 2011
I have a question regarding a Date field on one of my form.
How do I make a Date field filter in a descending order when the form is opened? Also, can I add a 'when-mouse-doubleclick' trigger to sort the date field in ascending and descending order (this is a client requirement)?
View 2 Replies
View Related
Dec 22, 2011
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
View 2 Replies
View Related
