Data fields...

I have 2 tables in database with one-to-many relationship (actually one-to-two). I built crosstab query on the tables.

Now when I want to show the query in DBGrid I have a problem:I cannot define columns.

If I define "crosstab" columns then I get error if query empty (not have these fields).

If I not define the fields I cannot see it after that.

Any idea?  
LVL 3
SlavakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phoenix_sCommented:
what database??
0
kifahCommented:
There is not Database methology says one to two, but as you said previus One to Many.

this is very simple query

SELECT C.NAME, Ctry.Country
FROM "customer.db" C
   INNER JOIN "Country.db" Ctry
   ON  (C.CountryID = Ctry.CountryID)  
Where XXXXXXX
0
kretzschmarCommented:
hi slavak,

> I built crosstab query on the tables.
can you show the query?

meikl
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

SlavakAuthor Commented:
Sorry for reject answer, but question is not built query, question is how to show it.

Database is MS-ACCESS.

0
SlavakAuthor Commented:
Example of database:

Table "Sites" - geographic places

Fields:

SiteID   : Integer
SiteName : String

Table "Cables" - cables connected the sites

Fields:

CableID   : Integer
CableName : String

Table "CableSites" - every cable related to 2 rows in the table

Fields:

CableID : Integer
SiteID  : Integer
Order   : Integer  - need to built crosstab query


Query SQL :

TRANSFORM Min(Sites.SiteName) AS SiteName
SELECT Cables.CableName
FROM (Sites INNER JOIN CableSites ON Sites.SiteID = CableSites.SiteID) INNER JOIN Cables ON CableSites.CableID = Cables.CableID
GROUP BY Cables.CableName
PIVOT CableSites.Order;
0
kretzschmarCommented:
hi slavak,

thats easy
(i guess you use the native driver)

drop TStoredProc-Component on your form

select the alias
select the StoredProcName (your crosstab-query)
set active to true

drop a tdatasource on your form
- connect it to the TStoredProc

drop a tdbgrid on your form
- connect it to the tdatasource

ready

meikl
0
SlavakAuthor Commented:
Ok, meikl.

Now add to query some fields that you need, but want NOT see in DBGrid.

Like this, in my example add to all tables Field "StateID" (just means that may be different states).

New SQL:

TRANSFORM Min(Sites.SiteName) AS SiteName
SELECT Cables.StateID, Cables.CableName
FROM (Sites INNER JOIN CableSites ON Sites.SiteID = CableSites.SiteID) INNER JOIN Cables ON CableSites.CableID = Cables.CableID
GROUP BY Cables.StateID, Cables.CableName
PIVOT CableSites.Order;

But I doun't want to see StateID in DBGrid.

I want to say that I need define DBGrid columns, but I cannot know columns number before opening query.
0
kretzschmarCommented:
hi slavak,

just doubleclick on the storedproc-comp
(fieldeditor comes up)
rightclick on it
add all fields
adjust the visible-property of the fields, you don't want to see

meikl
0
SlavakAuthor Commented:
yes, that a probled:

When you define fields by fields-editor, after that you can see
only fields you defined.

In crosstab query you cannot know how many fields contains query before you open (execute) it.
0
kretzschmarCommented:
well slavak,

thats true,a crosstab-query is dynamic,
but you can set it also at runtime
with (after activating)
StoredProc1.FieldByName('TheNameIDontWantToSee').Visible := False;

meikl
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.