When i execute the following instruction;

(
db2 -p- -w- -s- -tv <<!EOF
select u.login, c.email_addr, length(STRIP(TRANSLATE(u.login, ' ',' +-.
0123456789'),B)) from siebel.s_user u, siebel.s_contact c,
siebel.s_emp_per ep where
u.row_id = c.row_id and ep.par_row_id = c.row_id and
(ep.termination_dt IS NULL) and (length(STRIP(TRANSLATE(u.login, ' ','
+-.0123456789'),B)) = 0 ) and
(round(RAND(SECOND(TIMESTAMP(CURRENT TIMESTAMP))) * 10,0) = 10 ) fetch
first 1 rows only with ur;
!EOF
)|awk ' /^[0-9]{10}/ { print "##" $1 "#!#" $2 "#" }'

I get the expected output; "##1002502921#!#U.Sername@Domain.Somwhere#"

What i want is to redirect this output to a shell variable. Normally i
use the $( command or backticks to execute a command and store the
output in a variable. When i do this for the previous mentioned
instructions, the output variable is empty and no output is sent to
the stdout

output=`
(
db2 -p- -w- -s- -tv <<!EOF
select u.login, c.email_addr, length(STRIP(TRANSLATE(u.login, ' ',' +-.
0123456789'),B)) from siebel.s_user u, siebel.s_contact c,
siebel.s_emp_per ep where
u.row_id = c.row_id and ep.par_row_id = c.row_id and
(ep.termination_dt IS NULL) and (length(STRIP(TRANSLATE(u.login, ' ','
+-.0123456789'),B)) = 0 ) and
(round(RAND(SECOND(TIMESTAMP(CURRENT TIMESTAMP))) * 10,0) = 10 ) fetch
first 1 rows only with ur;
!EOF
)|awk ' /^[0-9]{10}/ { print "##" $1 "#!#" $2 "#" }'
`
echo $output

I must overlook something simple...

Peter (feeling quite stupid at the moment)

On Aug 30, 7:31 am, Peter <mail...@petervannes.nl> wrote:
> When i execute the following instruction;
>
> (
> db2 -p- -w- -s- -tv <<!EOF
> select u.login, c.email_addr, length(STRIP(TRANSLATE(u.login, ' ',' +-.
> 0123456789'),B)) from siebel.s_user u, siebel.s_contact c,
> siebel.s_emp_per ep where
> u.row_id = c.row_id and ep.par_row_id = c.row_id and
> (ep.termination_dt IS NULL) and (length(STRIP(TRANSLATE(u.login, ' ','
> +-.0123456789'),B)) = 0 ) and
> (round(RAND(SECOND(TIMESTAMP(CURRENT TIMESTAMP))) * 10,0) = 10 ) fetch
> first 1 rows only with ur;
> !EOF
> )|awk ' /^[0-9]{10}/ { print "##" $1 "#!#" $2 "#" }'
>
> I get the expected output; "##1002502921#!#U.Sern...@Domain.Somwhere#"
>
> What i want is to redirect this output to a shell variable. Normally i
> use the $( command or backticks to execute a command and store the
> output in a variable. When i do this for the previous mentioned
> instructions, the output variable is empty and no output is sent to
> the stdout
>
> output=`
> (
> db2 -p- -w- -s- -tv <<!EOF
> select u.login, c.email_addr, length(STRIP(TRANSLATE(u.login, ' ',' +-.
> 0123456789'),B)) from siebel.s_user u, siebel.s_contact c,
> siebel.s_emp_per ep where
> u.row_id = c.row_id and ep.par_row_id = c.row_id and
> (ep.termination_dt IS NULL) and (length(STRIP(TRANSLATE(u.login, ' ','
> +-.0123456789'),B)) = 0 ) and
> (round(RAND(SECOND(TIMESTAMP(CURRENT TIMESTAMP))) * 10,0) = 10 ) fetch
> first 1 rows only with ur;
> !EOF
> )|awk ' /^[0-9]{10}/ { print "##" $1 "#!#" $2 "#" }'
> `
> echo $output
>
> I must overlook something simple...
>
> Peter (feeling quite stupid at the moment)

When we use Sybase and want to capture the output into a variable we
use:
(ksh on AIX examples)

$ISQL_COMMAND -X -U$SQLUSER -P$(cat "$PASSFILE") -S$SERVER << ! | read
NUM_LEFT
<Sybase commands>
go
!

To string more commands together, I've done something like:
unix_command <<! | grep <grep flags> | read VARIABLE
<Sybase commands>go
!

So perhaps you could:
db2 -p- -w- -s- -tv <<!EOF | awk ' /^[0-9]{10}/ { print "##" $1 "#!#"
$2 "#" }' | read OUTPUT
select u.login, c.email_addr, length(STRIP(TRANSLATE(u.login, ' ','
+-.
0123456789'),B)) from siebel.s_user u, siebel.s_contact c,
siebel.s_emp_per ep where
u.row_id = c.row_id and ep.par_row_id = c.row_id and
(ep.termination_dt IS NULL) and (length(STRIP(TRANSLATE(u.login, '
','
+-.0123456789'),B)) = 0 ) and
(round(RAND(SECOND(TIMESTAMP(CURRENT TIMESTAMP))) * 10,0) = 10 )
fetch
first 1 rows only with ur;
!EOF

Miles

On Aug 30, 2:54 pm, Miles <my_spam_acco...@shaw.ca> wrote:
> On Aug 30, 7:31 am, Peter <mail...@petervannes.nl> wrote:
>
>
>
> > When i execute the following instruction;
>
> > (
> > db2 -p- -w- -s- -tv <<!EOF
> > select u.login, c.email_addr, length(STRIP(TRANSLATE(u.login, ' ',' +-.
> > 0123456789'),B)) from siebel.s_user u, siebel.s_contact c,
> > siebel.s_emp_per ep where
> > u.row_id = c.row_id and ep.par_row_id = c.row_id and
> > (ep.termination_dt IS NULL) and (length(STRIP(TRANSLATE(u.login, ' ','
> > +-.0123456789'),B)) = 0 ) and
> > (round(RAND(SECOND(TIMESTAMP(CURRENT TIMESTAMP))) * 10,0) = 10 ) fetch
> > first 1 rows only with ur;
> > !EOF
> > )|awk ' /^[0-9]{10}/ { print "##" $1 "#!#" $2 "#" }'
>
> > I get the expected output; "##1002502921#!#U.Sern...@Domain.Somwhere#"
>
> > What i want is to redirect this output to a shell variable. Normally i
> > use the $( command or backticks to execute a command and store the
> > output in a variable. When i do this for the previous mentioned
> > instructions, the output variable is empty and no output is sent to
> > the stdout
>
> > output=`
> > (
> > db2 -p- -w- -s- -tv <<!EOF
> > select u.login, c.email_addr, length(STRIP(TRANSLATE(u.login, ' ',' +-.
> > 0123456789'),B)) from siebel.s_user u, siebel.s_contact c,
> > siebel.s_emp_per ep where
> > u.row_id = c.row_id and ep.par_row_id = c.row_id and
> > (ep.termination_dt IS NULL) and (length(STRIP(TRANSLATE(u.login, ' ','
> > +-.0123456789'),B)) = 0 ) and
> > (round(RAND(SECOND(TIMESTAMP(CURRENT TIMESTAMP))) * 10,0) = 10 ) fetch
> > first 1 rows only with ur;
> > !EOF
> > )|awk ' /^[0-9]{10}/ { print "##" $1 "#!#" $2 "#" }'
> > `
> > echo $output
>
> > I must overlook something simple...
>
> > Peter (feeling quite stupid at the moment)
>
> When we use Sybase and want to capture the output into a variable we
> use:
> (ksh on AIX examples)
>
> $ISQL_COMMAND -X -U$SQLUSER -P$(cat "$PASSFILE") -S$SERVER << ! | read
> NUM_LEFT
> <Sybase commands>
> go
> !
>
> To string more commands together, I've done something like:
> unix_command <<! | grep <grep flags> | read VARIABLE
> <Sybase commands>go
> !
>
> So perhaps you could:
> db2 -p- -w- -s- -tv <<!EOF | awk ' /^[0-9]{10}/ { print "##" $1 "#!#"
> $2 "#" }' | read OUTPUT
> select u.login, c.email_addr, length(STRIP(TRANSLATE(u.login, ' ','
> +-.
> 0123456789'),B)) from siebel.s_user u, siebel.s_contact c,
> siebel.s_emp_per ep where
> u.row_id = c.row_id and ep.par_row_id = c.row_id and
> (ep.termination_dt IS NULL) and (length(STRIP(TRANSLATE(u.login, '
> ','
> +-.0123456789'),B)) = 0 ) and
> (round(RAND(SECOND(TIMESTAMP(CURRENT TIMESTAMP))) * 10,0) = 10 )
> fetch
> first 1 rows only with ur;
> !EOF
>
> Miles

This works great ! Thanks !!