MAJOR PROBLEM 1

[tsunami100]

Hi,

I have a table that is linked to a cobol-table.
There's something i really don't understand.
Each record has a invoicenr, customername, IDnr, deliveryplace, and articlenr.
BUT these fields are not stored in one record !!!
The invoicenr, ID customernr and deliveryplace are stored in one record.
Then the next record has the same invoicenr and starts with IDnr 0 and articlenr.
For every article there is an new record that's how it should be but it doens't repeat the other information only invoicenr ID end articlenr.
Here an example of a table :

Invoicenr   ID  Customernr.  Deliveryplace  artnr
100         0   212          London          
100         1                               52
100         2                               53 
100         3                               54  
200         0   213          Brussels       
200         1                               53  
200         2                               51

In fact these are seven records but how can i retrieve let's say al the records for customer 212.
Thanks



I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[dk87]

select * from CobolTable a, CobolTable b
where a.Customernr = 212
and b.Invoicenr = a.Invoicenr
and b.ID <> 0

 

[SQLSister]

Yep that's the way old nonrelational databases stored data.

select * from CobolTable a inner join CobolTable b
on  b.Invoicenr = a.Invoicenr
where a.Customernr = 212
and b.ID <> 0

If you have any records which might have the first line but no related records on follwing lines, you would want to query like this:

select * from CobolTable a left join CobolTable b
on  b.Invoicenr = a.Invoicenr
where a.Customernr = 212
and b.ID <> 0

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[tsunami100]

Hi,

Thanks so much for your reply dk87 and SQLSister.
Can you tell me wich sql-statement i need to display al the customers with their deliveryplaces and articles using a GROUP BY statement.
Another issue :
Am i wrong when i think that this is a bad way of storing data because most of the customers order just one article but the cobol-application inserts two records.
Last issue :
I'm using ACUodbc the establish the connection between sql-server and the cobol-datafile but it's very very slow.
It seems to be a real problem.
I've read a lot about this but nobody seems to have a solution.
Thanks again for your reply !!!

I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[SQLSister]

Tsumani, the older nonrelational databases don't have an option except to store data this way. It is inefficent which is why they have been largely replaced with relational databases.

In answer to your group by question, just add the group by to any of the queries we gave you.

If you don't need real-time data, I would suggest setting up related tables in SQL server and periodically moving the data to them rather than connecting to the older table for select queries to improve performance. You can then convert the data during the import to a relational format. That's what I do woth the data I get from a flat file database. But our data is only updated every 56 days which makes this practical. If you need the data in real-time, it becomes less practical especially if there is no simple way to identify data updates like a DateModified field.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[tsunami100]

Thanks again SQLSister,

The problem is that i need real-time data that is coming from another department but at the COBOL-forum somebody told me to use passthrough queries in the access-database so i don't have to retrieve all the records.
I'll try that tommorrow.

Thanks again SQLsister for your help!!!!

I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[k5tm]

^{tsumani100 linked to this thread from the COBOL Forum thread209-995706.}

tsunami100,

I finally had a chance to read this thread. Now it makes some sense!

I can tell you how I would do this in Relativity, so perhaps this would be useful on AcuODBC.

BTW, are you allowed to change anything about the AcuODBC table definitions?

In Relativity this would best be defined as two tables, one for the InvoiceHeader (which contains the customer number) and one for InvoiceDetail (which contains a row for each item ordered). The single table that you have had defined for you is going to be somewhat clumsy to use, because it promotes details of the underlying physical schema to the logical level -- meaning you have to code some strange SQL predicates to overcome the clumsy table definition.

Here is a modification of SQLSister's example that probably will work better:

select * from CobolTable InvoiceHeader inner join CobolTable InvoiceDetail
on  InvoiceDetail.Invoicenr = InvoiceHeader.Invoicenr
where InvoiceHeader.Customernr = 212
and InvoiceHeader.ID = 0
and InvoiceDetail.ID <> 0

This almost certainly will specify completely the underlying index value (InvoiceHeader.customernr = 212 and InvoiceHeader.ID = 0), thereby obviating the need for a full table scan. This is the clumsiness caused by the poor table definition (i.e. mapping the physical schema to the relational schema).

You ask:

Am i wrong when i think that this is a bad way of storing data because most of the customers order just one article but the cobol-application inserts two records?

Yes you are probably wrong. The information in the InvoiceHeader must be recorded somewhere, and most order entry systems built on relational database systems would have to insert a row for the header information and a row for each detail line in the invoice.

Now for a slightly different matter.

Yep that's the way old nonrelational databases stored data.

Clearly from the breadth of your involvement in Tek-Tips you are open to new concepts and approaches. It was heartening to see you refer to this problem as at least involving a "database" but I hope you will also see that the database is relational, at least in the sense that it can be manipulated by SQL. Relativity (and presumably AcuODBC) is based on this concept, which is the subject of this patent .

Tom Morrison

http://www.liant.com

 

[donutman]

You can turn the table scan into a clustered index seek by making the invoicenr and ID the PK (clustered of course). Whereupon the performance is excellent.
Unless it's an extremely large table, a crosstab query with 7 groups would turn it into a flat table with reasonable performance.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]