Jump to content
Henry Olive

FB3.0 SQL

Recommended Posts

I wish everyone a healthy day.

 

ITEMS Table

ITEMNO

AAA

 

ITEMMOVEMENTS View  ( WHNO = Ware House No, and i dont know how many )
ITEMNO...WHNO...INCOME...OUTGO

AAA.............1................10.................0
AAA.............2................20.................0
AAA.............3................30.................0
AAA.............3..................0...............10

 

How can get below result ?
ITEMNO...TOTAL_STOCK.....1.......2.......3
AAA...............50......................10.....20.....20
 

Thank You

 

Share this post


Link to post

Well there is LIST but, I am not sure you agree the result (even if you can work with via a TStringList)

PIVOT, I don't think it's supported even in FB4.

I see that it is a view, don't you think that using the original table should be better?

 

NOTE : Please for SQL questions, furnish a script for creating table, and filling it. Something like 
 

CREATE TABLE TESTHO(
ITEMNO VARCHAR(10),
WHNO INTEGER,
INCOME INTEGER,
OUTGO INTEGER);
COMMIT;
INSERT INTO TESTHO VALUES('AAA',1,10,0);
INSERT INTO TESTHO VALUES('AAA',2,20,0);
INSERT INTO TESTHO VALUES('AAA',3,30,0);
INSERT INTO TESTHO VALUES('AAA',3,0,10);

this to avoid us the "coding" for the test.  

 

With LIST you can do something like

SET TERM !! ;

EXECUTE BLOCK
RETURNS ( 
    ITEMNO VARCHAR(10),
    TOTAL INTEGER, 
    WHNOS VARCHAR(250), 
    QTYS VARCHAR(250)
)
AS
BEGIN
    SELECT ITEMNO,SUM(INCOME-OUTGO) FROM TESTHO GROUP BY ITEMNO INTO ITEMNO,TOTAL;
    WITH C AS (SELECT ITEMNO,WHNO, SUM(a.INCOME-a.OUTGO) Q FROM TESTHO a GROUP BY ITEMNO,WHNO)
          SELECT LIST(WHNO,';'),LIST(Q,';')  FROM C a GROUP BY ITEMNO
          INTO WHNOS,QTYS; 
    SUSPEND;
END!!

SET TERM ; !!

And obtain

ITEMNO    TOTAL    WHNOS    QTYS
AAA               50    1;2;3         10;20;20

but your test data is too small and don't expect order in both lists always correct

 

 

Edited by Serge_G

Share this post


Link to post

As far as I know, Firebird does not support PIVOT.

If you really need a column for each WHNO, you can first use a query to get distinct WHNO values and then use these values to create second query, which would compute the values.

Share this post


Link to post

Thank you so much  Attila, Serge, Vandrovnik, Stano
 

Serge, your solution is very interesting, not exactly like Pivot but it is very very simple and fast, i'll keep your codes for future

Stano, Your five methods... is also very very useful

Thank you so so much
 

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×