RE: [sqr-users] SQR - SQL question

["the dragon" <ceprn@hotmail.com>]

If you are using Oracle, the following should work.

BEGIN-SQL

insert into temp_prodtyp_allprods_v
select
(a.ty_day1_sls + nvl(b.ty_day1_sls,0)),
(a.ty_day2_sls + nvl(b.ty_day2_sls,0)),
(a.ty_day3_sls + nvl(b.ty_day3_sls,0)),
(a.ty_day4_sls + nvl(b.ty_day4_sls,0))

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

clark 'the dragon' willis


PSA: Salary <> Slavery.  If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul.  Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent.  Be careful lest you let other people 
spend it for you."

Carl Sandburg
(1878 - 1967)


----Original Message Follows----

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

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online  
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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