Developer Forum

Hello,

I have a STOCKTRANSACTION table
like this:

STOCKCODE QTY TRANSTYPE PERIOD
ITEM1 10 0 1
ITEM1 20 0 1
ITEM2 18 3 3
ITEM1 10 0 2
ITEM3 15 1 3
ITEM1 15 1 1
ITEM1 20 1 3
ITEM4 17 2 2
ITEM1 30 2 3
ITEM1 15 3 3


And I want to select the total quantities per transaction
grouped by period to come up with this:

PERIOD TRANS0 TRANS1 TRANS2 TRANS3
1 30 0 0 0
2 10 15 0 0
3 0 20 30 15


can anyone help me with the Interbase SQL?
I can do this in MSSQL since it allows sub-select,
but Interbase doesn't.

cheers and thanks in advance
Deck

InterBase allows subselects.

Perhaps you can clarify what you're trying to do. Show the
MS SQL query as well.


--
With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.yqcomputer.com/

0
15
SQL

something like this:

select DISTINCT a.period,b.total_units TRANS0,c.total_units
TRANS1,c.total_units TRANS2
from STOCKTRANSACTION a
left join (select period,stockcode,sum(qty) total_units from
STOCKTRANSACTION where transtype=0 and stockcode='AIRSUS01' group by
period,stockcode) B on a.period=b.period
left join (select period,stockcode,sum(qty) total_units from
STOCKTRANSACTION where transtype=1 and stockcode='AIRSUS01' group by
period,stockcode) C on a.period=c.period
left join (select period,stockcode,sum(qty) total_units from
STOCKTRANSACTION where transtype=2 and stockcode='AIRSUS01' group by
period,stockcode) D on a.period=d.period

works in MSSQL but not in Interbase : (

InterBase support subqueries but not joining to a SELECT.

--
Bill (TeamB)
TeamB cannot answer questions received via email

> > > I have a STOCKTRANSACTION table
0

Ah, this is commonly called a "derived table".


You might want to create a select-able stored procedure and pass
the "transtype" and "stockcode" to it.

You will get something like:

LEFT JOIN MYPROCEDURE(0, 'AIRSUS01') M ON A.PERIOD = M.PERIOD

And do the query inside your procedure.


--
With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.yqcomputer.com/