Solved

URGENT HELP!!! creating crosstab views in firebird 1.5

Posted on 2004-08-07
5
625 Views
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:

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<----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
0
Comment
Question by:ZeldaDX
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
NickUpson earned 500 total points
Comment Utility
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)
0
 

Author Comment

by:ZeldaDX
Comment Utility
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:
    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
0
 
LVL 19

Expert Comment

by:NickUpson
Comment Utility
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now