URGENT HELP!!! creating crosstab views in firebird 1.5

Posted on 2004-08-07
Medium Priority
Last Modified: 2013-12-09
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:

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<----Routes table rows
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
Question by:ZeldaDX
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 19

Accepted Solution

NickUpson earned 2000 total points
ID: 11745659
You can't do this in the way you describe, the SQL statement needs to know how many columns
to return and it can't until it retreived them.

The best Way I've found to do this is to do this select

Select r.id, p.id, d.qty
from routes r, products p, detail d
where r.id = d.routeid and p.id = d.productid

into a non-visible grid, then you can construct your final result table in the client ( I think, I'm no expect on Delphi)

Author Comment

ID: 11767434
Hello NickUpson

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:
        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
LVL 19

Expert Comment

ID: 11767744
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

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question