• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

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?  
0
Slavak
Asked:
Slavak
1 Solution
 
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now