SQL Concatenate Using A Loop

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


ADVERTISEMENT

SQL & PL/SQL :: How To Concatenate Column

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

SQL & PL/SQL :: How To Concatenate Three Columns Into One

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

How To Concatenate A String With Variable

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

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

SQL & PL/SQL :: Concatenate String In Function?

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

Forms :: Concatenate Or Copy Value On Enter Or WVI

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

Server Utilities :: How To Concatenate 2 Fields

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

SQL & PL/SQL :: To Remove Duplicates From Concatenate String

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

SQL & PL/SQL :: Concatenate Parameter With A Column In Query

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

SQL & PL/SQL :: Concatenate A Comma But Not Single Quotes

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

PL/SQL :: To Concatenate Last_name And First_name Fields With A Space In Between

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

PL/SQL :: How To Concatenate Quote To Procedure Parameter Input Value

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

Server Utilities :: Concatenate A Date With SQL Loader From Three Fields

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

SQL & PL/SQL :: Concatenate String To Number Field In Update Statement

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

PL/SQL :: Possible To Use Forall Instead Of For Loop - End Loop

Nov 19, 2012

CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;

can i use forall instead of 'for loop ..end loop'

View 10 Replies View Related

Server Utilities :: Concatenate Timestamp To Constant Value In Control File

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

JDeveloper, Java & XML :: Concatenate Strings To Print / Display Vertically

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

SQL & PL/SQL :: Best Way To Loop Against CSV

May 25, 2010

I have a list of strings ( like a,b ,c) that I want to loop againts. I will be creating string to execute them as dynamic sql.I could have put the string in a table and loop againt the table but this is a deployment script so I do not want to create any table.I can also do

DECLARE
cursor c is SELECT 'a' FROM dual UNION SELECT 'b' FROM dual ;
BEGIN
for i in c
loop
.......
end loop.
END;

But I have many strings to loop over. What is the best way to achieve it. Can i use collection to achieve this?

View 4 Replies View Related

SQL & PL/SQL :: Going To Next Record In Loop

May 25, 2011

I have a cursor which I am opening and then looping through. Within this loop I am comparing attributes within this cursor with attributes from another loop that this one is within.

you will see in the IF statements (there are several distinct IF statements within the loop) that there is a check which assesses if the attributes are not equal.

If they are NOT, the value of v_mismatch is set to 1

What I need to do instead of setting this to 1, is to go to the next record in the loop. How can I achieve this?

OPEN c_distMatrix;
LOOP
FETCH c_distMatrix INTO r_dist;
EXIT
WHEN c_distMatrix%NOTFOUND;
--compare each field and update the counter
[code].....

View 5 Replies View Related

SQL & PL/SQL :: How Does Loop Works

Jun 27, 2011

[CODE]
DECLARE
D1 DATE:='&D1';
D2 DATE;
BEGIN
WHILE D1<=D2 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY'));
D1:=D1+1;
END LOOP;
END;

Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.

View 9 Replies View Related

SQL & PL/SQL :: Getting Next Data With Loop

Jun 14, 2013

This query that I pasted is working correctly.Let's say a case has 4 owners, it finds me the first owner and show me its address.

However, I want to execute this query for all owners so it should jump the previous owner it found. Lets say for that example that the loop ends at 4.
How may I fix this problem so that the loop do not return always the first owner but it keeps getting 1,2,3,4...also I should increase the sequence value for each situation

SELECT
DECODE(BREINV.NAMEKEY, NULL,'0','1') "BRE_INV",
DECODE(BREINV.NAMEKEY, NULL,' ',BREINV.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV.NAMEKEY, NULL, ' ', DECODE(BREINV.SEQUENCE,NULL,NULL,RTRIM(ADDINV.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY.COUNTRYDESCRIPTION,1,30))) "BRE_NOMINV",
INDIVIDU.FIRSTNAME || ' ' || INV.NAME "BRE_NOMPREINV"
[code].......

View 3 Replies View Related

SQL & PL/SQL :: Lock On Loop

Aug 28, 2012

begin
for i in 1..10000 loop
update table1
set col1= col1+1
where type =1;
commit;
end loop;
end;

My question is, if a strored procedure contains the script above and the said procedure will be invoked by two or more sessions at the same time. Does it mean that the 1st session will lock the related rows and other sessions will have to wait for loop in session one to finish?

View 18 Replies View Related

SQL & PL/SQL :: Getting Error By Using NVL() And Loop

Sep 22, 2012

Declare
x number;
Begin
Loop
if NVL(x,1)>=1 then
[code].......

May be this code is going into an infinite loop. I'm unable to understand where the problem is?

View 6 Replies View Related

SQL & PL/SQL :: Cursor For Loop

Jul 30, 2012

When a cursor returns no records, how to handle this in Cursor for loop..

I had given sample code for handling this by using normal open,fetch statement, provide by applying for -Cursor For loop

DECLARE
P_BU_ID NUMBER;
P_SUMRY_DATA_TY_ID NUMBER;
P_OP_DATE VARCHAR2(32767);
P_PROC_DATE VARCHAR2(32767);
P_FIN_YEAR NUMBER;
[code]........

View 3 Replies View Related

SQL & PL/SQL :: Cursor FOR Loop?

Jun 15, 2012

I am an experienced SAS programmer jumping into PL/SQL for the first time and have already encountered a problem.Let's assume I have 7 records (shown below). (In reality, I have millions of records, but the concept's the same.) The Value field is only populated when it changes. Therefore, I am forced to "fill in the gaps" as I read the data file. It's fairly straightforward. I carry the value foward one record at a time, using it if the Value field in not populated. The ANSWER I want is also shown.

In reading through a PL/SQL book, I realized that only 2 chapters are relevant to what I do. My guess is this solution involves cursors; probably a Cursor FOR loop,

ID Year Amt ANSWER
1 2010 200 200
1 2011 ..... 200
1 2012 ..... 200
2 2009 300 300
2 2010 ..... 300
2 2011 450 450
2 2012 ..... 450

View 5 Replies View Related

PL/SQL :: Exceptions In WHILE Loop

Jul 20, 2012

I want to do something like this

-------------------------------------------------------------------------------------------------------
While Condition
LOOP
BEGIN
Insert into table1 values(......);

[Code]...

EXCEPTION
When OTHERS THEN <capture the error while inserting into an Error table>
END;
END LOOP;
-------------------------------------------------------------------------------------------------------

Now I want that If one of the insert statement within the loop fails (say table2) the exception should be captured and next insert statement (table3) should be executed.

How can I do this ? I guess I wont even need a loop

View 10 Replies View Related

PL/SQL :: LAST Record In A Loop?

Aug 27, 2012

in my loop with cursor in a procedure body i am displaying some field values to create a report and after every record i am displaying horizontal line(-------) but i don t want this line to appear after the last record displayed like below,

gfsfsf gsgfsfds gsdgfdg
------------------------------------
edyet gdgtdgt gtdfdfdgd
------------------------------------
dfds hedhgg idudhdh

how can i achieve this within pls/sql procedure body.

View 20 Replies View Related

VPD Function With Loop - Oracle 11g

Jul 5, 2012

I have the following function:

CREATE OR REPLACE FUNCTION get_project_id(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
IS
projects_pred VARCHAR2 (400);
[code].......

I am trying to get the projects a user has from the works_on table (user_id, project_id). The user_id is retrieved from the context projects_ctx. I am getting the error Function created with compilations errors.

View 6 Replies View Related

Loop Inside Cursor

Sep 11, 2012

I have a cursor returning some value.

for each value returned by the cursor i need to traverse through 31 rows(1 row per day * no of days in the month).

E.g. if cursor returns service_name as xyz then for xyz there can be 31 rows(service may not be used on some days)

I need to go to all of them and take some values and move them to a flat file. how should that be done?

Attached File(s)
Query.png ( 20.99K )
Number of downloads: 9

View 1 Replies View Related







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