Solved

URGENT HELP!!! creating crosstab views in firebird 1.5

Posted on 2004-08-07
5
632 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
[X]
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
5 Comments
 
LVL 19

Accepted Solution

by:
NickUpson earned 500 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)
0
 

Author Comment

by:ZeldaDX
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:
    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
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
0

Featured Post

Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…

717 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