SQL & PL/SQL :: Query To Display Repeated Column Values As Null?

Dec 13, 2012

I have table with the values as below.

C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ

I dont want to print the repeated value(NAME) of C1 multiple times as below.

C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ

I could do it using the below query using union with the rownum.

select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3

Is there any other way of displaying using a single sql query.

View 17 Replies


ADVERTISEMENT

SQL & PL/SQL :: Query To Return Not Null Column Values With Priority

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

SQL & PL/SQL :: Display Output For Null Column

Jul 24, 2012

I am having following table, and below are the out put and desired output.

CREATE TABLE tbl1
(
mon VARCHAR2(10)
, grp VARCHAR2(50)
, visits NUMBER
, redirect VARCHAR(50)
)
;

[Code]....

Query:

SELECT
mon
, grp
, SUM(visits)
FROM
tbl1
WHERE
redirect IS NOT NULL
GROUP BY mon, grp
;

Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13

Desired Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
May-12 Red 0
May-12 Orange 0

How can this be acheived?

View 1 Replies View Related

Query Ignore Null Values

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

SQL & PL/SQL :: Interval Partitioning And NULL Column Values

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

SQL & PL/SQL :: How To Deduct Repeated Values In Crystal Report

Dec 17, 2011

In my report I'm using inner join in queries .I got correct record but itz repeated.How to deduct that repeat values?

this is my query:

SELECT f.feeid,
f.studentid,
( s.first_name + last_name )AS studentname,
b.branchname,
c.coursename,
y.yearname,

[Code].....

View 7 Replies View Related

SQL & PL/SQL :: Entering Values As Not Null Constraint To Column In Oracle Without Disturbing Old Records

Jul 1, 2013

previously i set null constraint to the column and creating some rows and need to change new entering values as not null constraint to the column in oracle without disturbing the old records. how can I do that.

View 5 Replies View Related

SQL & PL/SQL :: Display Conditional Column Values

Apr 23, 2013

I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values

For example:

So when dept.deptno=10, I want to display these 2 columns values
1. dept.deptno
2. dept.dname

otherwise, display these 2 columns values
1. dept.loc
2. null

Can this be done with one case, decode or "other" type of structure going thru the table one time??

SELECT emp.empno,
emp.ename,
CASE
WHEN dept.deptno = 10 THEN
to_char(dept.deptno)
[code].......

View 6 Replies View Related

SQL & PL/SQL :: Display Values In A Column Instead Of Rows

Mar 10, 2011

Consider the following (example)table.,

TABLE_A
------------------------------
ID DEPT CRS
------------------------------
1 CS CS_100
2 SCIENCE SCI_150
3 MATH MATH_400
4 HISTORY HIS_110

[Code]...

To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,

--------------------------------------------
NO DEPT CRS
--------------------------------------------
1 MATH MATH_400, MATH_550, MATH_230
--------------------------------------------

instead of.,
--------------------------
NO DEPT CRS
---------------------------
1 MATH MATH_400
2 MATH MATH_550
3 MATH MATH_230
---------------------------

View 2 Replies View Related

PL/SQL :: How To Calculate Values And Display To Virtual Column

Mar 12, 2013

How I can build a query with conditions and calculations? E.g. I've got this table

Start          | End     |     Working Place     |     Mandatory
------------------------------------------------------------------------------------
01-JAN-13 | 11-JAN-13 |     Office           |          1
14-JAN-13 | 25-JAN-13 |     Home Office      |     0
04-MRZ-13| 15-MRZ-13 |     Office           |          0
11-FEB-13 | 22-FEB-13 |     Office           |          1

Now if column working place=Office and column mandatory=0
the new column "price" has to calculate: (End-Start)* $25.00
and if working place=Office and column mandatory=1
the "price" column has to calculate: (End-Start)* $20.60
else $0.00

I tried it with the case statement but I didn't know how to calculate my values and display it to the virtual column "price".

Something like
case
when Working_Place = 'Office' and Mandatory=1
then ...
else '0.00'
end as PRICE
?????

Or is it not possible?

View 5 Replies View Related

SQL & PL/SQL :: Query To Retrieve Number Of Not Null Rows For Each Column?

Jul 7, 2011

I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.

ex:

the output should look like this.

owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------

# of records not null # of records null
---------------------- --------------------

View 12 Replies View Related

SQL & PL/SQL :: Count Of Repeated Words In A Column?

Mar 1, 2012

I have a table with the following column and data is like this.

SQL>CREATE TABLE test (
column1 varchar2(50));
SQL>INSERT INTO test VALUES('ABC XYZ');
SQL>INSERT INTO test VALUES('MNO PQR');
SQL>INSERT INTO test VALUES('ABCD ABC');
SQL>INSERT INTO test VALUES('PQR MNOP');

[code]....

View 13 Replies View Related

PL/SQL :: Number Range Query Causes Repeated Scan By Index

Oct 16, 2012

I have a query that seems to repeatedly call an index scan on a table for reasons I'm not sure about. Why it would be doing the index scan on totaldwellingarea in the dimensions table (DIMEN_PID_TDWELLAREA) repeatedly? This only seems to happen when I put on the range clause d.totaldwellingarea between scr.lowvalue and scr.highvalue.

I am using Oracle version 9.2.0.3.

select d.propertyid,d.totaldwellingarea, e.size_,  scr.size_
from  eqid e, dimensions d,  brt_eval.size_code_ranges scr
where e.style not in ('1','A','G','L') and e.size_  = '0'
and d.propertyid = e.propertyid and e.style = scr.style and d.totaldwellingarea between scr.lowvalue and  scr.highvalue;

CREATE
  TABLE "BRT_ADMIN"."EQID"
  (
    "PROPERTYID"   VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "EQID"         CHAR(10 BYTE),
    "ZONE_"        CHAR(1 BYTE),
    "AREA"         CHAR(1 BYTE),
[code].....

View 3 Replies View Related

SQL & PL/SQL :: Query On Single Column To Display As Rows

Aug 5, 2010

I has a table of structure of varchar2 datatype.

NO
----------
1-2
3-4
5-6

desired output is:

SQL>1
2
3
4
5
6

The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.

I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.

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

Replacing Null Values Of Outer Join With Meaningful Values

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

PL/SQL :: Query For Checking Column Having More Than One Type Of Values?

Dec 6, 2012

I have a table and data as below.I need to select all the records if value have both 'M' and 'D'.If there is only 'M' or 'D' then select should not pull any records.

WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select value from data group by value

I tried below query but it is not working.

WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select * from data group by value having sum(count(distinct(value))) > 1

View 3 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 :: To Display Null Rows When No Match

Apr 23, 2010

I have table as follows:

create table sample1 (
i number ,
j date,
k number)

insert into sample1 values (1,'23-Apr-2010',11)
insert into sample1 values (2,'22-Apr-2010',12)
insert into sample1 values (3,'21-Apr-2010',13)
insert into sample1 values (4,'19-Apr-2010',14)
insert into sample1 values (5,'18-Apr-2010',15)
insert into sample1 values (6,'17-Apr-2010',16)

I would like to get nulls , if there is no data for a date. As we can see , here i am missing the data for '20-Apr-2010'.

I did it through "UNIX" , but it's not efficient.

The data might be missing for the complete week also. I need to test in this way only for the last 7 days. I tried something like this:

select i, j , sum(k)
from sample1
where j in (select to_date(sysdate - rownum)
from dual
connect by rownum < = 7)
group by i, j

View 3 Replies View Related

Forms :: Replace NULL With Different Value During Display

Jan 17, 2012

There is a table, desc :-

name varchar2(10), class number(1)

values:-

name class
----- ------
qwe 1
asd
zxc 2

Here 'asd's class is NULL.

Now, using forms, how do i display the above with the modification where NULL will be shown as -1?

that is-

name class
----- ------
qwe 1
asd -1
zxc 2

View 39 Replies View Related

Application Express :: Possible To Display Null Item Value As All In Blank HTML Region

Jul 2, 2012

Apex 4.1
Theme 21

I have select list which has null and other values. I have created a blank HTML region with the following text

Report for the year &P2_YEAR. and for geography &P2_GEOG.

Now P2_YEAR works fine because, it is always populated with a year. However, &P2_GEOG has 'All' as null value since we can see the report for all geographies. However, if the all option is selected, the above &P2_GEOG. shows as null. Since its not a PL/SQL proc, I cannot put in a NVL. I am guessing one possible way is to write a PL/SQL code before region and then check if its null and then try to populate a new hidden field and show, something like that. Wondering if there is a way to do it directly in the HTML region?

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

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 :: Columns With NULL Values?

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

SQL & PL/SQL :: Replace Zero With Null Values

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

PL/SQL :: How To Restrict Null Values

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

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

SQL & PL/SQL :: How To Use Or Condition To Check If 6 Col Values In A Row Is NULL

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







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