help with correlated subquery
Don wanted a single SQL statement returning per-key sums of negative and positive amounts, but only rows where both sums exist (his correlated subqueries returned NULLs). Art suggested a self-join with GROUP BY/HAVING, but on IDS 9.4 it rejected IS NOT NULL on aggregates and double-counted (returned -18/18 instead of -6/6). Gary's derived-table (FROM-clause subquery) approach isn't supported before IDS 10. Don solved it with SUM(CASE WHEN amount<0 ...) / SUM(CASE WHEN amount>0 ...), GROUP BY, plus HAVING clauses to exclude zero sums. Curtis noted the CASE/ELSE 0 version can wrongly return rows with no negatives and offered a view or a TABLE(MULTISET(...)) inline-view alternative.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All,
I have a need for a correlated subquery but can't quite figure out the final
piece. I'm hoping somebody can help with some ideas. Keep in mind that I am
limited to 1 pass of SQL.
Put as simply as I can, I need to return 2 Sums but only if both are not zero.
So, I have 3 columns. The first 2 are identifiers. The 3rd contains a numeric
value (a dollar amount). This value can be negative or positive. I need to add
the neg values to get 1 sum and then the pos values to get the 2nd sum.
What I have so far is:
SELECT distinct T1.COL1, T1.COL2,
(select SUM(T2.Amount) from Table T2
where T1.COL1=T2.COL1 and T1.COL1=T2.COL2
and T2.Amount < 0) neg_sum,
(select SUM(T3.Amount) from Table T3
where T1.COL1=T3.COL1 and T1.COL2=T3.COL2
and T3.Amount > 0) pos_sum
FROM Table T1
The sums are coming up correctly, but now comes the trick. If there is no
qualifying data the returned sum values are NULL. So I want to weed out those
results where either sum is NULL (i.e. both sums must have a value <> 0). I
know I could do this with temp tables but I want to use this in an external
report writer that only allows for a single SQL pass.
Any thoughts, ideas, suggestions would be greatly appreciated (and yes I will
accept a response that I am off my rocker for trying this).
Don
DON CHADWICK wrote:
> Hello All,
You don't need a correlated sub-query at all but a self-join:
SELECT t1.col1, t1.col2, sum(t1.col3), sum(t2.col3)
FROM table as t1, table as t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND t1.col3 < 0
AND t2.col3 > 0
GROUP BY 1,2
HAVING sum(t1.col3) > 0
AND sum(t1.col3) IS NOT NULL
AND sum(t2.col3) > 0
AND sum(t2.col3) IS NOT NULL;
Art S. Kagel
Oninit
> I have a need for a correlated subquery but can't quite figure out the final
> piece. I'm hoping somebody can help with some ideas. Keep in mind that I am
> limited to 1 pass of SQL.
>
> Put as simply as I can, I need to return 2 Sums but only if both are not
zero.
> So, I have 3 columns. The first 2 are identifiers. The 3rd contains a numeric
> value (a dollar amount). This value can be negative or positive. I need to
add
> the neg values to get 1 sum and then the pos values to get the 2nd sum.
>
> What I have so far is:
> SELECT distinct T1.COL1, T1.COL2,>
> (select SUM(T2.Amount) from Table T2
>
> where T1.COL1=T2.COL1 and T1.COL1=T2.COL2
>
> and T2.Amount < 0) neg_sum,
>
> (select SUM(T3.Amount) from Table T3
>
> where T1.COL1=T3.COL1 and T1.COL2=T3.COL2
>
> and T3.Amount > 0) pos_sum
> FROM Table T1
>
> The sums are coming up correctly, but now comes the trick. If there is no
> qualifying data the returned sum values are NULL. So I want to weed out those
> results where either sum is NULL (i.e. both sums must have a value <> 0). I
> know I could do this with temp tables but I want to use this in an external
> report writer that only allows for a single SQL pass.
>
> Any thoughts, ideas, suggestions would be greatly appreciated (and yes I will
> accept a response that I am off my rocker for trying this).
>
> Don
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
>
>
Hi,
Correlated subquery is a new feature since ids10? but I only have ids7.3.
Worked in oracle10g, please try and see if it works in your release:
select a.col1,a.col2,a.amt neg_amt,b.amt pos_amt
from
(select col1,col2,sum(amt) amt
from tt
where amt < 0 group by col1,col2) a,
(select col1,col2,sum(amt) amt
from tt
where amt > 0 group by col1,col2) b
where a.col1 = b.col1
and a.col2 = b.col2;
GARY GU wrote:
Correlated sub-queries aren't new, but sub-queries in the FROM clause
are. Not supported in 7.xx or even 9.xx.
Art S. Kagel
Oninit
> Hi,
>
> Correlated subquery is a new feature since ids10? but I only have ids7.3.
> Worked in oracle10g, please try and see if it works in your release:
>
> select a.col1,a.col2,a.amt neg_amt,b.amt pos_amt
> from
> (select col1,col2,sum(amt) amt
> from tt
> where amt < 0 group by col1,col2) a,
> (select col1,col2,sum(amt) amt
> from tt
> where amt > 0 group by col1,col2) b
> where a.col1 = b.col1
> and a.col2 = b.col2> ;
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
>
>
Thanks for the quick responses.
Art, I tried your query but a couple of things happened. First it didn't let
me use the 'is not NULL' in the having clause, something about not being able
to use NULL with an aggregate (we're on IDS 9.4). Taking those out it ran the
query but was giving me some duplication in the sums. I setup a quick test
like this:
create table test1 (col1 int, col2 char(4), amount int);
insert into test1 values (1,'test',1);
insert into test1 values (1,'test',2);
insert into test1 values (1,'test',3);
insert into test1 values (1,'test',0);
insert into test1 values (1,'test',0);
insert into test1 values (1,'test',0);
insert into test1 values (1,'test',0);
insert into test1 values (1,'test',-1);
insert into test1 values (1,'test',-2);
insert into test1 values (1,'test',-3);
Your query returned -18 and 18 where it should return -6 and 6. So I changed
the query a little and actually was able to get it without a join.
SELECT distinct t.col1, t.col2,sum(Case when t.amount < 0
then t.amount else 0
END) neg_sum,
sum(Case when t.amount > 0
then t.amount else 0
END) pos_sum
FROM test1 t
WHERE t.amount <> 0
group by 1,2
The testing I did seemed to be valid so unless anyone sees any gotchas with
this I can go from here.
Gary, since we're on 9.4 I wasn't able to check out your approach. I actually
did just for s's and giggles try it but got a syntax error.
Thanks again for your assistance.
Don
Spoke a little too soon. I had to add a couple of Having statements at the end to get rid of the zero value sums. Having sum(CASE when t1.amount < 0 THEN t1.amount ELSE 0 END) < 0 and sum(CASE when t1.amount > 0 THEN t1.amount ELSE 0 END) > 0 Thanks again.
This query I don't think is right based on your initial requirement that both
values have to be not null. Let me give you an example:
create table test1 (col1 int, col2 char(4), amount int);
insert into test1 values (1,'dept',1);
insert into test1 values (1,'dept',2);
insert into test1 values (1,'dept',3);
insert into test1 values (1,'check',0);
insert into test1 values (1,'check',0);
insert into test1 values (1,'check',0);
insert into test1 values (1,'check',0);
insert into test1 values (1,'with',-1);
insert into test1 values (1,'with',-2);
insert into test1 values (1,'with',-3);
-- Your query returned -18 and 18 where it should return -6 and 6. So I
changed the query a little and actually was able to get it without a join.
SELECT
t.col1,
t.col2,
sum(Case when t.amount < 0
then t.amount else 0
END
) neg_sum,
sum(Case when t.amount > 0
then t.amount else 0
END) pos_sum
FROM
test1 t
WHERE
t.amount <> 0 and col2="dept"
group by
1,2
;
Now you shouldn't get any records if I understood your requirements because
there are no negative dept's but with your query you will get:
1 dept 0 6
The simplest way to do this with nine is to use my favorite whipping boy
views. If you have to do it inline then so be it but I would just create a
view:
It doesn't violate your requirements because you only do it on the server side
one time and then use it on the reporting side. And if you are using a limited
capability reporting tool like this views can open up a world of possibilities
that you aren't able to imagine today.
create view my_sums(
col1,
col2,
neg_sum,pos_sum
) as
SELECT
t.col1,
t.col2,
sum(Case when t.amount < 0
then t.amount
END
) neg_sum,
sum(Case when t.amount > 0
then t.amount
END) pos_sum
FROM
test1 t
WHERE
t.amount <> 0
group by
1,2
;
select
*
from
my_sums
where
col2 = "dept" and neg_sum is not null and pos_sum is not null
;
will give you the appropriate result which is empty because we didn't make any
negative deposits.
Here is the correct inline view query:
Add this data to my dataset, your dataset will probably work unchanged.
insert into test1 values (1,'unk',1);
insert into test1 values (1,'unk',2);
insert into test1 values (1,'unk',3);
insert into test1 values (1,'unk',0);
insert into test1 values (1,'unk',0);
insert into test1 values (1,'unk',0);
insert into test1 values (1,'unk',0);
insert into test1 values (1,'unk',-1);
insert into test1 values (1,'unk',-2);
insert into test1 values (1,'unk',-3);
SELECT
p.COL1,
p.COL2,
n.neg_sum,
p.pos_sum
from
table(
multiset(
select
t.col1,
t.col2,
SUM(T.Amount)
from
test1 t
where
t.amount < 0
group by 1, 2
)
) as n(col1, col2, neg_sum),
table(
multiset(
select
t.col1,
t.col2,
SUM(T.Amount)
from
test1 t
where
t.amount > 0
group by 1, 2
)
) as p(col1, col2, pos_sum)
where
p.col1 = n.col1 and
p.col2 = n.col2 and
p.pos_sum is not null and
n.neg_sum is not null
;
Complete table statement:
create table test1 (col1 int, col2 char(4), amount int);
insert into test1 values (1,'dept',1);
insert into test1 values (1,'dept',2);
insert into test1 values (1,'dept',3);
insert into test1 values (1,'check',0);
insert into test1 values (1,'check',0);
insert into test1 values (1,'check',0);
insert into test1 values (1,'check',0);
insert into test1 values (1,'with',-1);
insert into test1 values (1,'with',-2);
insert into test1 values (1,'with',-3);
insert into test1 values (1,'unk',1);
insert into test1 values (1,'unk',2);
insert into test1 values (1,'unk',3);
insert into test1 values (1,'unk',0);
insert into test1 values (1,'unk',0);
insert into test1 values (1,'unk',0);
insert into test1 values (1,'unk',0);
insert into test1 values (1,'unk',-1);
insert into test1 values (1,'unk',-2);
insert into test1 values (1,'unk',-3);
I still prefer the good old fashioned regular view.
Oh, and the distinct in your original query hides some problems with your
query that would cause performance issue later on. You are basically sort of
doing a cross product. I don't believe in using distinct unless it is really
necessary.