Create dataset from two queries?

[lespaul]

Because our database is so screwy, I'm having major problems getting the information I need to display as a single dataset. Is there a way that I can run two queries (with some common fields) and create a single dataset with all the information that I need?

Thanks,
Leslie

 

[towerbase]

If my interpretation of your question is correct, then what you are asking for is the usual way of getting data out of a database.

If this is the case then your question is really a database / SQL question and not specific to Delphi.

Anyway, here goes.

A database usually consists of a number of tables. If the database has been designed well then these tables will have been "normalised".

Using a small example, suppose we have a customer table containing the following columns:-

customer_number
customer_name
customer_address

an order table containing the following columns:-

order_number
customer_number
product_code
order_date

and a product table containing the following columns:-

product_code
product_description

One advantage of this normalised database is that if the product description needed to be changed for product code 1234 then it would only be necessary to change one row of the product table.

Now suppose you wished to list all the product descriptions and dates ordered by customer number 5678. This information is contained in two tables (customer and product) and you will need the third one (order table) to link them. You would issue an SQL query something along the lines of:

SELECT o.order_date, p.product_description 
FROM product p, customer c, order o 
WHERE c.customer_number = o.customer_number 
AND p.product_code = o.product_code
AND c.customer_number = 5678

So here we are getting information from two tables but actually using just one query.

I suggest that you get hold of a beginners book on SQL and look at the Delphi help. If I have misunderstood your question then I apologise.

Andrew

 

[Zathras]

If you can have ALL (not just some) common fields, then you can use the UNION operator to connect two queries:

SELECT A, B, C from TABLEA
UNION
SELECT D, E, F from TABLEB

For a columns that don't exist in table, use constant expressions, e.g.:

SELECT ' ' AS A, B, C from TABLEA
UNION
SELECT D, E, 0, from TABLEB


Note that Delphi may rename the data columns internally (to something like COLUMN1, COLUMN2, etc.) so you may wish to use column numbers when referencing the dataset.

 

[lespaul]

Unfortunately, my biggest problem is that the database is NOT normalized. Here is the SQL that I've come up with, but it only works for some records. Sometimes it doesn't return anything (even though I know that there is one record in CMPARRST) and sometimes it returns double the number of records:

SELECT CMPARRST."BOOKING#", ARHDAT, ARRTIM, ARROFF, ARROFN,
CMPAGNCY.AGNAME, ARRLOC, BNDSET, BNDPAY, RECVDAT, 'Cash' AS BONDTYPE, 'None' AS POSNAM
FROM CMPARRST
INNER JOIN CMPAGNCY ON CMPARRST.ARRAGC = CMPAGNCY.AGENCY
LEFT OUTER JOIN ACPCBDMF ON CMPARRST."BOOKING#" = ACPCBDMF."BOOKING#"
WHERE CMPARRST.CASPRE = :CASPREC AND CMPARRST.CASNUM = :CASNUMC
AND DEFSEQ = EFSEQC
UNION
SELECT CMPARRST."BOOKING#", ARHDAT, ARRTIM, ARROFF, ARROFN,
CMPAGNCY.AGNAME, ARRLOC, BNDSET, BNDPAY, TRANDATE AS RECVDAT, 'Surety' AS BONDTYPE, POSNAM
FROM CMPARRST
INNER JOIN CMPAGNCY ON CMPARRST.ARRAGC = CMPAGNCY.AGENCY
LEFT OUTER JOIN ACPSBDMF ON CMPARRST."BOOKING#" = ACPSBDMF."BOOKING#"
INNER JOIN ACPSBDTF ON ACPSBDMF.BNDNUM = ACPSBDTF.BNDNUM
WHERE CMPARRST.CASPRE = :CASPRES AND CMPARRST.CASNUM = :CASNUMS
AND DEFSEQ = EFSEQS
ORDER BY ARHDAT

(trust me I don't need a beginner SQL book!)

Is there a way that I can query a query? I'm trying to avoid creating a "custom" table just for this information.

Thanks,
Leslie

 

[Zathras]

Your difficulty in finding records may be due to NULL values in some columns that are used in a WHERE clause. Take a close look at the data.

As for "query a query" that is sort of like what happens when you create a VIEW and query that view. (If your DBMS supports views. Or in the case of Access, you can indeed query a query.)

What DBMS are you using?

 

[lespaul]

It's an AS400 (poorly designed database, no relationships are set up and the data is screwy!!!)

 

[Zathras]

So... what do your users do with the data? Is it read-only? Why does it have to be in one dataset? Can you get the AS400 boys to create one or more logical files to make your life easier?

Sounds like you may have to do everything the hard way, but so be it. You can retrieve the data in parts, load it into a string grid query by query and work with it there.

If you don't want to write your own sort routine for a string grid, there are third party grids available, assuming you would need to do some sorting. (I wrote a routine to sort a string grid just for the fun of it. It is less than 100 lines of code.)

 

[lespaul]

That's a good idea! I'll look into populating the string grid. I'll have to let you know about the sorting.

Thanks!
Leslie