create table taba as
select 'a' a_id, 9 ccost from dual union all
select 'b' a_id, 9 ccost from dual union all
select 'c' a_id, 9 ccost from dual union all
select 'd' a_id, 9 ccost from dual;
create table tabb as
select 'ba' b_id, 1 ccost1, 1 ccost2, 'x' a_id from dual union all
select 'bb' b_id, 1 ccost1, 2 ccost2, 'b' a_id from dual union all
select 'bc' b_id, 1 ccost1, 3 ccost2, 'b' a_id from dual union all
select 'bd' b_id, 1 ccost1, 4 ccost2, 'd' a_id from dual;
SELECT a.a_id,
b.b_id,
CASE when ccost1 is null then 'taba'
ELSE 'tabb'
END from_table,
CASE when ccost1 is null then ccost
ELSE ccost1 + ccost2
END the_cost
FROM taba a LEFT OUTER JOIN tabb b ON a.a_id = b.a_id
order by 1,2;
A_ID B_ID FROM_TABLE THE_COST
---- ---- ---------- ----------
a taba 9
b bb tabb 3
b bc tabb 4
c taba 9
d bd tabb 5
5 rows selected.
On Mon, Aug 23, 2010 at 4:29 PM, Mark <markdanielmal...@gmail.com> wrote:
> I need to translate the pseudo following into a SQL statement
>
> 3 tables linked in a one to many relationship
>
> Table A
> a_ID
> cost
>
> Table B
> b_ID
> cost_1
> cost_2
> *a_ID
>
> if Table A is referenced in Table B then
> return the sum (cost_1 + cost_2) from Table B of all the
> referenced records
> otherwise
> return cost from Table A
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
