RE: [sqr-users] SQR - SQL question

["Vallabhu, Bindu" <Bindu.Vallabhu@Staples.com>]

Hi Jim,
This is a DB2 database and i used "coalesce" - and it worked.

select
coalesce(a.ty_day1_sls,0) + coalesce(b.ty_day1_sls,0)

Thanks a lot.
Bindu


-----Original Message-----
From: James Womeldorf [mailto:jwomeldo@fastenal.com]
Sent: 08/01/2003 11:48 AM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] SQR - SQL question


Hi Bindu,

I think you have pointed out the problem.  I think you need to do an outer
join and may have to decode the nulls.

In Oracle I believe it would be:

BEGIN-SQL
insert into temp_prodtyp_allprods_v
select 
(a.ty_day1_sls + decode(b.ty_day1_sls,null,0,b.ty_day1_sls)),
(a.ty_day2_sls + decode(b.ty_day2_sls,null,0,b.ty_day2_sls)),
(a.ty_day3_sls + decode(b.ty_day3_sls,null,0,b.ty_day3_sls)),
(a.ty_day4_sls + decode(b.ty_day4_sls,null,0,b.ty_day4_sls))

from temp_prodtyp_stpls_v a, 
temp_prodtyp_whslr_v b
where a.class_number = b.class_number(+)
and a.dept_number = b.dept_number(+)
END-SQL

Jim

-----Original Message-----
From: Vallabhu, Bindu [mailto:Bindu.Vallabhu@staples.com]
Sent: Friday, August 01, 2003 10:36 AM
To: 'sqr-users@sqrug.org'
Subject: [sqr-users] SQR - SQL question


Hi Everybody,
Is it possible to do this in a SQL statement in an SQR program.

BEGIN-SQL
insert into temp_prodtyp_allprods_v
select 
(a.ty_day1_sls + b.ty_day1_sls),
(a.ty_day2_sls + b.ty_day2_sls),
(a.ty_day3_sls + b.ty_day3_sls),
(a.ty_day4_sls + b.ty_day4_sls)

from temp_prodtyp_stpls_v a, 
temp_prodtyp_whslr_v b
where a.class_number = b.class_number
and a.dept_number = b.dept_number
END-SQL

It does not give me a syntax error when i run the program but it gets zeros
into the fields. When i do 

BEGIN-SQL
insert into temp_prodtyp_allprods_v
select 
a.ty_day1_sls,
a.ty_day2_sls,
a.ty_day3_sls,
a.ty_day4_sls

from temp_prodtyp_stpls_v a, 
temp_prodtyp_whslr_v b
where a.class_number = b.class_number
and a.dept_number = b.dept_number
END-SQL

it gets me values. 

There are values in 

a.ty_day1_sls,
a.ty_day2_sls,
a.ty_day3_sls,
a.ty_day4_sls

but not in

b.ty_day1_sls,
b.ty_day2_sls,
b.ty_day3_sls,
b.ty_day4_sls

Does that make a difference.

Thank you,
Bindu

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users