SQL & PL/SQL :: How To Concatenate While Removing Nulls
Jan 26, 2012
This is a simple question, but I cannot seem to find a solution. Here's the basic query:
select distinct accountno, parcelno, streetno||' '|| predirection ||' '|| streetname||' '|| streettype||' '|| postdirection||' '|| unitname||', '|| propertycity
from tblacctpropertyaddress ....
What I want to do is add is this logic: If Predirection is null, then no space between streetno & streetname. Same for postdirection and unitname. (for example, if both postdirection and unitname are null, there are no spaces between streettype and the comma before propertycity)
Also, when unitname is not null, I want to add the string "Unit " prior to the returned value in unitname.
View 5 Replies
ADVERTISEMENT
Mar 21, 2010
I AM HAVING A TABLE WITH BELOW STRUCTURE AND VALUES.
Table A
Col1Col2 Col3
M1D1V1
M1D2
M1D3V2
M1D4
M1D5
[Code]...
Below is the scenario provided with sample data to replicate the actual requirement.
I am trying to write a query to get below output. But I am not able to succeed.
The column having null values should have the non null value as showed in example.
OUTPUT
------
Col1Col2 Col3
M1D1V1
M1D2v1
M1D3V2
M1D4v2
M1D5v2
M1D6v2
M1D7V3
[Code]...
Below is the script for table and values
create table tmp (col1 varchar2(10),col2 varchar2(10),col3 varchar2(10));
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D1', 'V1');
insert into TMP (COL1, COL2, COL3)
values ('M1', 'D2', null);
[Code]...
View 1 Replies
View Related
Dec 20, 2011
Am trying to extract data from oracle onto flatfile(.txt), am using UTL_FILE but, NULLs in oracle tables are getting converted into space and if i try loading into table it is getting loaded as space,
View 1 Replies
View Related
Oct 2, 2013
I'm on 11.2.0.3. I want to write a query to get calculate a running total of incidents per day - this query will be used for an APEX line chart.Sample table and data:
create table sales (
id number primary key,
time_of_sale date,
item varchar2(20));
insert into sales values (1, to_date('02-JAN-2013','DD-MON-YYYY'), 'book'); ....
View 6 Replies
View Related
Apr 8, 2010
I have a sql like
select TRIM(column_name)
from user_tab_columns
where table_name = 'MTL_SYSTEM_ITEMS_VL' AND COLUMN_NAME IN ('DESCRIPTION ');
[Code]....
Like this i have many rows. What clause other than trim i should apply to no cosider the spaces?
View 27 Replies
View Related
Jul 24, 2012
Are NULLs get indexed in bitmap indexes? How can I verify that.
View 3 Replies
View Related
Aug 18, 2013
We have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.
provide a simple query using case in ORDER BY clause.
ID CLASS NAME DIST_ID DIST_NAME
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL NULL NULL NULL NULL
0 C123 RAJESH 0 AP
NULL C123 RAVI NULL AP
We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as
1 2 3 4 5
ID CLASS NAME DIST_ID DIST_NAME
for the following three records number of NULL values are same. but positions are different.
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
Based on the column positions the ranks as
2*2+3*3=13
1*1+3*3=10
2*2+3*3=13
1*1+4*4=17
Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as
ID CLASS NAME DIST_ID DIST_NAME
0 C123 RAJESH 0 AP
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
NULL NULL NULL NULL NULL
View 15 Replies
View Related
Jul 19, 2013
How do I collapse all nulls for every table column in a SQL Statement?
View 15 Replies
View Related
Feb 17, 2010
I have a table which I will call 'fruit basket' which contains the following data:
basket_ref, fruit, fruit_serial
1, apple, 1
1, banana, 2
1, pear, 3
2, apple, 1
2, lemon, 2
What I want to produce is a table in the following format
basket_ref, all_fruit
1, apple banana pear
2, apple lemon
There could theoretically be any amount of fruit in a given basket, so I will need to set up some kind of loop in order to read each record from the original table. I am not sure how it would be best to do this.
View 2 Replies
View Related
Jul 26, 2012
I have the following info in a table:
LEVELPARENTPOSCHILD
1100571010055
1100572010053
2100552010056
2100551010054
The output that I need is:
LEVELPARENTPOSCHILD
1100571010055
21005510.1010054
21005510.2010056
1100572010053
If there are even more levels, I also need those childs to be shown right below their parent and with POS as 10.20.10 )(for the first item below POS 10.20.
View 4 Replies
View Related
Feb 21, 2012
I'd like to concatenate my 3 columns into one. I have this text item(NAME) and i want my columns lname, fname and mname to be bound into name text item.
this is so far my code to do the concat:
SELECT lname
INTO :ADVISEMENT.name
FROM students
WHERE :ADVISEMENT.name = lname || ',' || fname || ',' || mname;
but it didn't appear at all.
View 17 Replies
View Related
Mar 5, 2008
I am trying to replace the string_name in the following execute immediate statement with a concatenated
string_||v_variable.
execute immediate
'select
table_name
from
user_tables
where table_name = :a'
into v_table
using 'string_name';
I tried different declaration and concatenations but nothing worked so far.
View 2 Replies
View Related
Jul 29, 2010
i m create one function in which i have to concatenate result likeDate is 19/07/2010'. i tried to concatenate in select statement. in this case function is created successfully but it gives error when it run.
CREATE or replace function TEST(FINYEAR VARCHAR2)
RETURN VARCHAR2 AS
QRY_STRING VARCHAR2(1000);
BALANCE VARCHAR2(100);
[code]...
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at "TEST", line 11
View 19 Replies
View Related
May 27, 2013
I have three database fields on my form , field_1 , field_2 and field_3 , user will enter the data in field_1 , and sometime he may not enter in field_2 ,
I want to bring the value of field_1 as it is into field_3 if he does not enter anything in field_2, if he enters the data in field_2 , then field_1 will get concatenated with field_2 and gets copied into field_3.Is there a way to do this automatically at form level using a trigger when-validate-item.In short this should be done after user presses the enter key on field_2.
create table ot_Sale_code_head ( sale_code varchar2(12),code1 varchar2(6),code2 varchar(6),code3 varchar2(12));
insert into ot_Sale_code_head ('A','01.01',NULL,NULL);
--inserted record in form will be 'A','01.01',NULL,'01.01'
insert into ot_Sale_code_head ('B','01.01','PS-P',NULL);
--inserted record in form will be 'B','01.01','PS-P','01.01.PS-P'
View 2 Replies
View Related
Aug 19, 2010
I am running SQLLDR on unix.
LOAD DATA
INFILE F56100c.dat
APPEND
INTO TABLE tstdta.F56100C
(
Z59SFU13 POSITION(14;21),
[Code]...
I have already used
1.Z59SFU17 POSITION(55:59)||POSITION(51:53)||POSITION(47:49) getting err:Illegal combination of non-alphanumeric characters
2.Z59SFU17 POSITION(55:59,51:53,47:49) this is also not working.
3. i can not use ":Z59SFU15 || :Z59SFU16" this option.
View 6 Replies
View Related
May 12, 2011
I have a query like
SELECT country_name,
substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME,
substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE,
substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE
FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as
[code].......
In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.
View 2 Replies
View Related
Sep 30, 2010
SELECT OM.ORG_NAME||' Unit - ':p_unit name
FROM org_mst om
In the above query I want to concatenate Unit number passed by a parameter.
View 4 Replies
View Related
Oct 8, 2010
In table_A , the primary key is Col_A which is of data type number.I want to concatenate it with ' '
Col_A
______
123
124
select '|| col_A ||'||','
from
Table_A;
The output should be
'123',
'124'
I can concatenate a comma but not single quotes.
View 3 Replies
View Related
Mar 19, 2013
I'm a fledgeling to start of with databases. Query below :
Select CONCAT(last_name,'',first_name)
from employees
When I run this query in SQL developer, I'm getting the following error :
ORA-00909: invalid number of arguments
+00909. 00000 - "invalid number of arguments"+
The aim is to concatenate last_name and first_name fields with a space in between.
View 4 Replies
View Related
Dec 6, 2012
create or replace procedure ab(a in varchar2, b in varchar2)
is
test varcha2(8);
begin
if (a is not null) then
for i in(select c
from t
where c between ||'''||a||'''|| and ||'''||b||'''||)
loop
test:=i.c
end loop;
end if;
end;
I want both parameter input values to be enclosed in quotes so that it considers both parameter values as char.Receiving ora 00936 missing expression error.
View 7 Replies
View Related
Jul 26, 2010
I want to load a delimited file that contains many records which contained within the table where I'm going to load a date type field and I need to do this by concatenating three fields
field1 = 1 - this is the day
field2 = 11 - this corresponds to the month
field3 = 5 - this corresponds to the year
I need is in the field Save as type date 01/11/2005 i don´t know how to do it but I tried as follows but I get error loading.
"TO_DATE (TO_CHAR (: field1 ||'/'|| DECODE (: field2, 1, 'JAN', 2, 'FEB', 3, 'MAR', 4, 'APR', 5, 'MAY', 6 , 'JUN', 7, 'JUL', 8, 'AUG', 9, 'SEP', 10, 'OCT', 11, 'NOV', 12, 'DEC': field2 )||'/'|| : field3)) "
View 3 Replies
View Related
Oct 4, 2011
I need to concatenate string to the number field in an update statement like this:
update test1 set model_pin = seq_no || '_' || model
where eoc_code like 'AEW%'
When I run this command in sql , I get ERROR at line 1:
ORA-01722: invalid number
View 7 Replies
View Related
Jun 13, 2012
I am loading data using sqlldr command in UNIX to an oracle table and want to concatenate timestamp to a file name in the "create_file_name" column in the code below.
I have the below code within the control file..
LOAD DATA
TRUNCATE
INTO TABLE TABLEA
TRAILING NULLCOLS
(
file_type POSITION(1:5) CHAR,
business_date POSITION(16:23) DATE "YYYYMMDD",
create_file_name "FILE_NAME" EXPRESSION "SELECT TO_CHAR(CURRENT_TIMESTAMP(3), 'YYYYMMDDHH24MISS') FROM DUAL")
The load fails with SQL Loader error: "Expecting valid column specification, ",", ")", found keyword EXPRESSION found instead of column. How the timestamp to a filename can be appended?
View 5 Replies
View Related
Feb 16, 2011
I created a sample application in java which concatenates three strings and displays the result in a final string.
Check the -
public class conCat
{
public static void main (String[] args)
{
String Message1 = "This ";
String Message2 = "is a Sample ";
[code].......
This is a Sample Application
And the three strings are concatenated to the final string "Message" only. How to concatenate them vertically? The required output is :
This is a sample Application
View 1 Replies
View Related
Nov 16, 2010
I was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]
View 11 Replies
View Related
Sep 19, 2013
db and dev 10g rel2 ,suppose that i have a table with a lot of duplicate rows ,what i need is to delete the duplicates and retain one row of these duplicates . likecolumn -- with those values...how to delete two (hi's) and retain the third , ?it is all applied to all the duplicate values in the column.
View 5 Replies
View Related
Jul 3, 2008
Trying to delete duplicate rows from a table. The problem is, they aren't exactly duplicate rows. Let me explain.
I am migrating data from a Oracle 8.1.7 db to a 10.2.1 db. In the older db, this certain table does not have a PK/Unique Index, but in the new db there is a unique index. The fields that the index is unique on are:
SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE.
In the old db, when I run this query I get 1229 rows. With a count of 2 each.
select SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE, count(*)
from customer_id_equip_map
group by SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE
having count(*)>1;
They are duplicates on those fields, but they are not totally duplicate rows because there is a field called is_current that has 0 in one row and has 1 in the other. What I need to do, is delete the 1229 rows with is_current=0.
View 4 Replies
View Related
Aug 5, 2012
To remove fragmentation which is the best method.
First one :
-----------------
1)Created a backup table from the Fragmented table (This table is a partitioned one).
2)Analyzed this table.
3)DROP the Fragmented table
4)Inserted the backuped up data from backup table to the Re-created table.
5)Analyze this table.
Second method
-------------------
1) Create a backup table newly, with PCTFREE =0
2) Inserted the data from Fragmented table ( This is a partitioned table) to backup table
3) Analyzed this table.
4) Truncate Fragmented table
4) Did Exchange partition of Fragmented table with Backup table.
The second method is not found to be removing the Fragmentation. Before the fragmentation was 28% after Second method the fragmentation is still the same. While the first method the fragmentation reduced to 16%.
Query used to find Fragmentation.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
[code]...
View 9 Replies
View Related
Sep 10, 2010
TYPE CashRecord IS RECORD(client_id VARCHAR2(100),
account_letter VARCHAR2(100),
cash_amount VARCHAR2(100),
cash_amount_ccy VARCHAR2(100) );
TYPE CashRecordTable IS TABLE OF CashRecord INDEX BY VARCHAR2(100); -- Indexed by client_id~account_letter~ccy
So if I did something like this;
l_cash_records CashRecordTable;
-- say for example that l_cash_rec/l_cash_rec2 has been defined..
l_cash_records('some index') := l_cash_rec;
l_cash_records('some index 2') := l_cash_rec2;
l_cash_records.COUNT would give me 2
How can I somehow remove 'some index 2' so that l_cash_records.COUNT is 1 ?
View 3 Replies
View Related
Dec 10, 2012
One of my clients need to remove three(of four) CPU to comply the licensing agreement with Oracle.
To avoid problems and also to list the possible problems that removing the CPU can bring, I wish to make a survey of the possible impacts, especially in performance, that removal can cause.
How can I get this information?
View 8 Replies
View Related
