ZeldaDX
asked on
URGENT HELP!!! creating crosstab views in firebird 1.5
Hello guys:
I have 3 tables the first one, for example, this is a routes table:
ID Name Description
1 Route1 First destination
2 Route2 Second destination
3 Route3 thirth destination
4 Route4 fourth destination
I have another table called Products
ID Name Description
A Product1 First product
B Product2 Second Product
C Product3 thirth Product
D Product4 fourth Product
and a detail table:
PRODUCTID ROUTEID QTY
A 1 12
B 2 56
C 3 89
D 4 109
And i want to print a report with this information:
Product name Route1 Route2 Route3 Route4........RouteN<----R
Product1 1 0 0 0 N
Product2 0 2 0 0 M
Product3 0 0 3 0 O
Product4 0 0 0 4 P
. . . . . .
. . . . . .
. . . . . .
ProductM A B C D Z
the problem is that i don't know how to put a dinamic columns in interbase...I'm using delphi 5, firebird 1.5 and reportbuilder... I was trying several ways to make it but i haven't the result i want yet... can somebody help me?....I don't care if i use stored procedures, triggers,delphi code or whatever you think. I only want to make a view to print the report i mentioned above...
Thanks a lot
Zelda DX
I have 3 tables the first one, for example, this is a routes table:
ID Name Description
1 Route1 First destination
2 Route2 Second destination
3 Route3 thirth destination
4 Route4 fourth destination
I have another table called Products
ID Name Description
A Product1 First product
B Product2 Second Product
C Product3 thirth Product
D Product4 fourth Product
and a detail table:
PRODUCTID ROUTEID QTY
A 1 12
B 2 56
C 3 89
D 4 109
And i want to print a report with this information:
Product name Route1 Route2 Route3 Route4........RouteN<----R
Product1 1 0 0 0 N
Product2 0 2 0 0 M
Product3 0 0 3 0 O
Product4 0 0 0 4 P
. . . . . .
. . . . . .
. . . . . .
ProductM A B C D Z
the problem is that i don't know how to put a dinamic columns in interbase...I'm using delphi 5, firebird 1.5 and reportbuilder... I was trying several ways to make it but i haven't the result i want yet... can somebody help me?....I don't care if i use stored procedures, triggers,delphi code or whatever you think. I only want to make a view to print the report i mentioned above...
Thanks a lot
Zelda DX
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you will only get a row where a joining row exists on the detail table, the SQL won't work quite like that anyway.
select p.name,r.name, sum(d.qty) as XYZ
from routes r, products p,detail d
where r.id=d.routeid and p.id=d.productid
group by p.name,r.name
will give
product route XYZ
product1 route1 12
rouduct2 route2 56
the XYZ column will only contain zero if there is a row in the detail table with a qty of zero
select p.name,r.name, sum(d.qty) as XYZ
from routes r, products p,detail d
where r.id=d.routeid and p.id=d.productid
group by p.name,r.name
will give
product route XYZ
product1 route1 12
rouduct2 route2 56
the XYZ column will only contain zero if there is a row in the detail table with a qty of zero
ASKER
The problem with that sentence is that i cannot retrieve the values i want. My target is reduce the use of paper. I can solve that situation with this:
select p.name,r.name,d.qty
from routes r, products p,detail d
where r.id=d.routeid and p.id=d.productid
group by p.name,r.name,d.qty
And make a report with this format:
Product1
Route1 1
Route2 0
Route3 0
.....
The problem is that i have to print this info in a half page. I need to print at least 15 products in one report and print 2 reports in one letter size page. With this option i can print only 2 or 3 products per page (if the route doesnt increment). My goal is use the less of paper sheets as posible.
Thanks a lot
ZeldaDX