SP query

[ianholmes]

I have 3 tables (a, b, AND c) being accessed in an SP :-

A ref
amt 10 20 30
curr USD GBP EUR

B ref
linkref

C ref
amt 20 40
curr USD EUR

There may be lots of different currency records for each A.ref
Similarly for C.

If A.ref = B.ref, then B.linkref matches to C.ref ,
to get associated records.
However, there might be currencies in A which don’t exist in C. (see examples above)

How do I get a sum of all amounts and associated amounts for the differeing A currencies,
without getting NULLs in my returns ?

I have tried something like (greatly simplified) :-

create procedure X ……… @REF

select "currency"=a.curr,
"value" = a.amt
from a
where a.ref = @REF
and not exists (select * from b where a.ref = b.ref)
union
select "currency"=a.curr.
"value" = convert(char(18),
((select isnull(c.amt,0)
from c, b
where b.ref = a.ref
and c.ref = b.linkref
and c.curr = a.curr) +a.amt))
from a
where a.ref = @REF
and exists (select * from b where A.ref = B.ref)
order by 1


This works OK for USD EUR, but I get Nulls coming back at me for GBP, which doesn’t exist in C

 

[PHV]

I'm afraid you post in a wrong forum.

 

[tdatgod]

Hi,
you can use any one of the large number of database forums on tek-tips based upon your database.

oracle has 10 - 15 forums alone.

Just go to the serach for a forum box and type in database and pick the forum best suited to your database vendor.