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

 

[dnoeth]

I can't follow your explanation...
Could you please post the DDL, some INSERTs and the required result set?

Dieter

 

[ianholmes]

Hi

create table t_aaa(
refno char(8) not null,
curr char(3) not null,
amount numeric(4))
insert into t_aaa values ("AAAAAAAA", "USD", 0001)
insert into t_aaa values ("AAAAAAAA", "GBP", 0002)
insert into t_aaa values ("AAAAAAAA", "EUR", 0003)

create table t_bbb(
refno char(8) not null,
linkrefno char(8) not null)
insert into t_bbb values ("AAAAAAAA", "BBBBBBBB&quot

create table t_ccc(
refno char(8) not null,
curr char(3) not null,
amount numeric(4))
insert into t_ccc values ("BBBBBBBB", "USD", 1111)
insert into t_ccc values ("BBBBBBBB", "GBP", 2222)


My SP, with argument AAAAAAAA, should return

EUR 0003
GBP 2224
USD 1112

Thanks

 

[ianholmes]

hi dnoeth

I think I've got it :-

select "curr" = a.curr,
"amnt" = convert(char(10),
((select isnull(c.amount,0)
from t_ccc c, t_bbb b
where b.refno = a.refno
and c.refno =*b.linkrefno
and c.curr =*a.curr)
+a.amount))
from t_aaa a
where a.refno = "AAAAAAAA"

curr amnt
-------- -----
USD 1112
GBP 2224
EUR 3
(3 rows affected)

Thanks anyway for your interest.

 

[dnoeth]

This is the ANSI SQL group, so you shouldn't use proprietary syntax, because your DBMS (MS SQL?) supports Standard SQL:

"column as alias_name" instead "of alias_name = column"
"cast" instead of "convert"
"coalesce" instead of "isnull"
"left join" instead of "=*"

I'd do it with a UNION:

select
curr,
sum(amount) as amt
from
(select
curr,
amount
from t_aaa
where refno = "AAAAAAAA"

union all

select
curr,
amount
from t_ccc c join t_bbb on c.refno = b.linkrefno
where b.refno = "AAAAAAAA"
) dt
group by curr;

Dieter

 

[r937]

[tt]select A.curr
, A.amount + coalesce(C.amount,0) as amount
from t_aaa A
left outer
join t_bbb B
on A.refno = B.refno
left outer
join t_ccc C
on B.linkrefno = C.refno
and A.curr = C.curr
where A.refno = 'AAAAAAAA'[/tt]

rudy

http://r937.com/