syntax in 'store procedure'
A user wanted a stored procedure to build one string from many rows (e.g. 'A123 B234 C345') inside a FOREACH loop, but using a comma to join values didn't work. Replies pointed out the concatenation operator is ||, and that self-concatenating CHAR variables fails because CHAR values are blank-padded, leaving no room; the fix is to trim first and use VARCHAR, e.g. LET pronumber_set1 = TRIM(pronumber_set1) || ' ' || p_pro. Another poster gave a full example using NVL/DECODE to handle NULLs. The original poster confirmed it worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I need help on 'store procedure' codes. Did the store procedure allow
'concatenated text'?
There is an example below. It does not work. How do I supposed to do to
get the row of 'p_pro' in one line of text such as: A123 B234 ...
p_pro p_pro ---> define as char(16)
EX: pronumber_set1 = 'A123 B234 C345 D456.......' ---> the result
which I want to be
foreach
select pro_number, seqnum, mnfst_set
into p_pro, totalnumberpros, manifestnumber
from fb_delivery
where trailer_key = trailernumber
and mnfst_date = trl_status_date
order by seqnum
if totalnumberpros <= 54 then
let pronumber_set1 = pronumber_set1 , p_pro;
else
let pronumber_set2 = pronumber_set2 , p_pro;
end if
end foreach
Thanks,
Tah Davis
Yes, I did try, but it does not work.
-----Original Message-----
From: Jairo Gubler [mailto:jairo.gubler@digitro.com.br]
Sent: Wednesday, April 16, 2003 1:34 PM
To: tah davis
Subject: Re: syntax in 'store procedure' [946]
Try this.
let pronumber_set1 = pronumber_set1 || p_pro;
----- Original Message -----
From: "tah davis " <tdavis@knrq.com>
To: <ids@iiug.org>
Sent: Wednesday, April 16, 2003 2:18 PM
Subject: syntax in 'store procedure' [946]
Hi,
I need help on 'store procedure' codes. Did the store procedure allow
'concatenated text'?
There is an example below. It does not work. How do I supposed to do to
get the row of 'p_pro' in one line of text such as: A123 B234 ...
p_pro p_pro ---> define as char(16)
EX: pronumber_set1 = 'A123 B234 C345 D456.......' ---> the result
which I want to be
foreach
select pro_number, seqnum, mnfst_set
into p_pro, totalnumberpros, manifestnumber
from fb_delivery
where trailer_key = trailernumber
and mnfst_date = trl_status_date
order by seqnum
if totalnumberpros <= 54 then
let pronumber_set1 = pronumber_set1 , p_pro;
else
let pronumber_set2 = pronumber_set2 , p_pro;
end if
end foreach
Thanks,
Tah Davis
I had
to deal with the same problem.
I found that self concatenation does not work
because char types are padded with spaces.
So there is never any space left to concatenate anything to oneself.
I think this will work.
define procnumber varchar(10);
let procnumber = procnumber_set1; -- I think this trims the trailing
spaces
let pronumber_set1 = pronumber || p_pro;
-----Original Message-----
From: tah davis [mailto:tdavis@knrq.com]
Sent: Wednesday, April 16, 2003 2:38 PM
To: ids@iiug.org
Subject: RE: syntax in 'store procedure' [948]
Yes, I did try, but it does not work.
-----Original Message-----
From: Jairo Gubler [mailto:jairo.gubler@digitro.com.br]
Sent: Wednesday, April 16, 2003 1:34 PM
To: tah davis
Subject: Re: syntax in 'store procedure' [946]
Try this.
let pronumber_set1 = pronumber_set1 || p_pro;
----- Original Message -----
From: "tah davis " <tdavis@knrq.com>
To: <ids@iiug.org>
Sent: Wednesday, April 16, 2003 2:18 PM
Subject: syntax in 'store procedure' [946]
Hi,
I need help on 'store procedure' codes. Did the store procedure allow
'concatenated text'?
There is an example below. It does not work. How do I supposed to do to
get the row of 'p_pro' in one line of text such as: A123 B234 ...
p_pro p_pro ---> define as char(16)
EX: pronumber_set1 = 'A123 B234 C345 D456.......' ---> the result
which I want to be
foreach
select pro_number, seqnum, mnfst_set
into p_pro, totalnumberpros, manifestnumber
from fb_delivery
where trailer_key = trailernumber
and mnfst_date = trl_status_date
order by seqnum
if totalnumberpros <= 54 then
let pronumber_set1 = pronumber_set1 , p_pro;
else
let pronumber_set2 = pronumber_set2 , p_pro;
end if
end foreach
Thanks,
Tah Davis
let pronumber_set1 = trim(pronumber_set1) || " " || p_pro;
-----Original Message-----
From: Grumelart, .... [mailto:agrumela@foxboro.ca]
Sent: Wednesday, April 16, 2003 4:45 PM
To: ids@iiug.org
Subject: RE: syntax in 'store procedure' [950]
I had to deal with the same problem.
I found that self concatenation does not work
because char types are padded with spaces.
So there is never any space left to concatenate anything to oneself.
I think this will work.
define procnumber varchar(10);
let procnumber = procnumber_set1; -- I think this trims the trailing
spaces
let pronumber_set1 = pronumber || p_pro;
-----Original Message-----
From: tah davis [mailto:tdavis@knrq.com]
Sent: Wednesday, April 16, 2003 2:38 PM
To: ids@iiug.org
Subject: RE: syntax in 'store procedure' [948]
Yes, I did try, but it does not work.
-----Original Message-----
From: Jairo Gubler [mailto:jairo.gubler@digitro.com.br]
Sent: Wednesday, April 16, 2003 1:34 PM
To: tah davis
Subject: Re: syntax in 'store procedure' [946]
Try this.
let pronumber_set1 = pronumber_set1 || p_pro;
----- Original Message -----
From: "tah davis " <tdavis@knrq.com>
To: <ids@iiug.org>
Sent: Wednesday, April 16, 2003 2:18 PM
Subject: syntax in 'store procedure' [946]
Hi,
I need help on 'store procedure' codes. Did the store procedure allow
'concatenated text'?
There is an example below. It does not work. How do I supposed to do to
get the row of 'p_pro' in one line of text such as: A123 B234 ...
p_pro p_pro ---> define as char(16)
EX: pronumber_set1 = 'A123 B234 C345 D456.......' ---> the result
which I want to be
foreach
select pro_number, seqnum, mnfst_set
into p_pro, totalnumberpros, manifestnumber
from fb_delivery
where trailer_key = trailernumber
and mnfst_date = trl_status_date
order by seqnum
if totalnumberpros <= 54 then
let pronumber_set1 = pronumber_set1 , p_pro;
else
let pronumber_set2 = pronumber_set2 , p_pro;
end if
end foreach
Thanks,
Tah Davis
Hallo Tah.
Evaluate this example.
create table example(part int not null
,seq int not null
,strng varchar(255)
);
insert into example values( 1,1,"AAA" );
insert into example values( 1,2,"BBB" );
insert into example values( 1,3,"CCC" );
insert into example values( 2,1,"AAA" );
insert into example values( 2,2,"BBB" );
insert into example values( 3,1,"AAA" );
insert into example values( 3,2,"BBB" );
insert into example values( 3,3,NULL );
insert into example values( 3,4,"DDD" );
create procedure ccc( ppart int default 0 ) returns varchar(255);define sout,vout varchar(255);
define iout int;
let vout = "";
foreach
select strng, seq
into sout, iout
from example
where part = ppart
order by seq
-- classical concat
-- let vout = vout || " " || nvl(sout,"");-- probably better concat for you, first must be
let vout = vout || decode(sout,NULL,""," " || sout);
end foreach
return vout;
end procedure;
Pawel Tarach
>From: "tah davis " <tdavis@knrq.com>
>To: ids@iiug.org
>Subject: syntax in 'store procedure' [946] Date: Wed, 16 Apr 2003 13:18:45
>-0400 (EDT)
>
>Hi,
> I need help on 'store procedure' codes. Did the store procedure allow
>'concatenated text'?
>There is an example below. It does not work. How do I supposed to do to
>get the row of 'p_pro' in one line of text such as: A123 B234 ...
> p_pro p_pro ---> define as char(16)
>EX: pronumber_set1 = 'A123 B234 C345 D456.......' ---> the result
>which I want to be
>
> foreach
> select pro_number, seqnum, mnfst_set
> into p_pro, totalnumberpros, manifestnumber
> from fb_delivery
> where trailer_key = trailernumber
> and mnfst_date = trl_status_date
> order by seqnum>
> if totalnumberpros <= 54 then
> let pronumber_set1 = pronumber_set1 , p_pro;
> else
> let pronumber_set2 = pronumber_set2 , p_pro;
> end if
> end foreach
>
>Thanks,
>Tah Davis
>
>
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
Thanks so much for your help - it worked. I'd like
to know where I can find
the useful syntaxs or procedure commands - just like 'trim' that in the
future I can use in the store procedure or trigger codes.
Thanks in advances
Tah Davis
-----Original Message-----
From: Eric Washal.... [mailto:Eric.Washaliski@mediware.com]
Sent: Wednesday, April 16, 2003 5:34 PM
To: ids@iiug.org
Subject: RE: syntax in 'store procedure' [951]
let pronumber_set1 = trim(pronumber_set1) || " " || p_pro;
-----Original Message-----
From: Grumelart, .... [mailto:agrumela@foxboro.ca]
Sent: Wednesday, April 16, 2003 4:45 PM
To: ids@iiug.org
Subject: RE: syntax in 'store procedure' [950]
I had to deal with the same problem.
I found that self concatenation does not work
because char types are padded with spaces.
So there is never any space left to concatenate anything to oneself.
I think this will work.
define procnumber varchar(10);
let procnumber = procnumber_set1; -- I think this trims the trailing
spaces
let pronumber_set1 = pronumber || p_pro;
-----Original Message-----
From: tah davis [mailto:tdavis@knrq.com]
Sent: Wednesday, April 16, 2003 2:38 PM
To: ids@iiug.org
Subject: RE: syntax in 'store procedure' [948]
Yes, I did try, but it does not work.
-----Original Message-----
From: Jairo Gubler [mailto:jairo.gubler@digitro.com.br]
Sent: Wednesday, April 16, 2003 1:34 PM
To: tah davis
Subject: Re: syntax in 'store procedure' [946]
Try this.
let pronumber_set1 = pronumber_set1 || p_pro;
----- Original Message -----
From: "tah davis " <tdavis@knrq.com>
To: <ids@iiug.org>
Sent: Wednesday, April 16, 2003 2:18 PM
Subject: syntax in 'store procedure' [946]
Hi,
I need help on 'store procedure' codes. Did the store procedure allow
'concatenated text'?
There is an example below. It does not work. How do I supposed to do to
get the row of 'p_pro' in one line of text such as: A123 B234 ...
p_pro p_pro ---> define as char(16)
EX: pronumber_set1 = 'A123 B234 C345 D456.......' ---> the result
which I want to be
foreach
select pro_number, seqnum, mnfst_set
into p_pro, totalnumberpros, manifestnumber
from fb_delivery
where trailer_key = trailernumber
and mnfst_date = trl_status_date
order by seqnum
if totalnumberpros <= 54 then
let pronumber_set1 = pronumber_set1 , p_pro;
else
let pronumber_set2 = pronumber_set2 , p_pro;
end if
end foreach
Thanks,
Tah Davis