SQL problem

Can anyone suggest a syntax equivalent to the following but avoiding temporary tables: select sum(qty) plus from st_stmove where stockhash = "AAAACV" and inoutind = "+" into temp a; select sum(qty) minus from st_stmove where stockhash = "AAAACV" and inoutind = "-" into temp b; select sum(plus - minus) from a, b Thanks. Graeme Muirhead g.muirhead@freeuk.com

Graeme Muirhead wrote: > > Can anyone suggest a syntax equivalent to the following but avoiding > temporary tables: > > select sum(qty) plus from st_stmove > where stockhash = "AAAACV" and inoutind = "+" > into temp a; > > select sum(qty) minus from st_stmove > where stockhash = "AAAACV" and inoutind = "-" > into temp b; > > select sum(plus - minus) from a, b > > Thanks. > > Graeme Muirhead > g.muirhead@freeuk.com Something like: select (sum(plus.ty) - sum(minus.qty)) from st_stmove plus, st_stmove minus where plus.stockhash = "AAAACV" and plus.stockhash = minus.stockhash and plus.inoutind = "+" and minus.inoutind = "-" -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

Graeme Muirhead a écrit: > > Can anyone suggest a syntax equivalent to the following but avoiding > temporary tables: > > select sum(qty) plus from st_stmove > where stockhash = "AAAACV" and inoutind = "+" > into temp a; > > select sum(qty) minus from st_stmove > where stockhash = "AAAACV" and inoutind = "-" > into temp b; > > select sum(plus - minus) from a, b > > Thanks. > > Graeme Muirhead > g.muirhead@freeuk.com you can do it with 1 temp table (i don't think it's possible without): select sum(qty) total from st_stmove where stockhash = "AAAACV" and inoutind = "+" union select sum(qty) * -1 total from st_stmove where stockhash = "AAAACV" and inoutind = "-" into temp mytemptable; select sum(total) from mytemptable; -- Daniel Racine Analyste-programmeur Ville de Sherbrooke

I think you can do it in one statement with no temp tables in a couple of different ways. Graeme's version using CASE is one such, but only works with 7.3x and possibly 9.2x. Another fairly contorted one is: SELECT SUM(qty * (inoutind[1] || '1')) FROM st_stmove WHERE stockhash = 'AAACV" AND (inoutind = "+" OR inoutind = "-"); Obviously, if inoutind is a single-character column, you don't need the subscript in the SUM argument list. Further, if the only valid values in inoutind are "+" and "-", you don't need to use the OR term in the WHERE clause. Finally (from me), you can write: SELECT (SELECT SUM(qty) FROM st_stmove WHERE stockhash = "AAACV" AND inoutind = "+") - (SELECT SUM(qty) FROM st_stmove WHERE stockhash = "AAACV" AND inoutind = "-") FROM SysTables WHERE TabID = 1; I'm not certain that the main FROM clause can simply list a criterion which selects a single row of data -- it might have to cite st_stmove. Of course, there's another, more radical solution; store the sign in the quantity value, so if inoutind = "-", the quantity is negative. Then you just write: SELECT SUM(qty) FROM st_stmove WHERE stockhash = "AAACV" AND (intoutind = "+" OR inoutind = "-'); Again, you'd be able to simplify this with detailed knowledge of the possible values in inoutind. Graeme Muirhead wrote: > Can anyone suggest a syntax equivalent to the following but avoiding > temporary tables: > > select sum(qty) plus from st_stmove > where stockhash = "AAAACV" and inoutind = "+" > into temp a; > > select sum(qty) minus from st_stmove > where stockhash = "AAAACV" and inoutind = "-" > into temp b; > > select sum(plus - minus) from a, b > > Thanks. > > Graeme Muirhead > g.muirhead@freeuk.com -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Carlson@WHSmith wrote: > > Graeme Muirhead wrote: > > > > Can anyone suggest a syntax equivalent to the following but avoiding > > temporary tables: > > > > select sum(qty) plus from st_stmove > > where stockhash = "AAAACV" and inoutind = "+" > > into temp a; > > > > select sum(qty) minus from st_stmove > > where stockhash = "AAAACV" and inoutind = "-" > > into temp b; > > > > select sum(plus - minus) from a, b > > > > Thanks. > > > > Graeme Muirhead > > g.muirhead@freeuk.com > > Something like: > > select (sum(plus.ty) - sum(minus.qty)) > from st_stmove plus, st_stmove minus > where plus.stockhash = "AAAACV" > and plus.stockhash = minus.stockhash > and plus.inoutind = "+" > and minus.inoutind = "-" No, this will produce a cartesian product between all the +'s and all the -'s in st_stmove. June -- june_t@hotmail.com Living on Snickers bars in San Mateo