Here's the scenario.

We have a table called Catalogue. Important fields are

Tot_1
Tot_2
Tot_3
Tot_4

We have a table called appl_status. Important Fields are
Status_num
Status

Thirs and final table we'll call data_file. Important Fields are
Status

Okay; so here's the deal. I want to get a count on data_file for how many of each status exist. So let's say...

select count(*), status from data_file group by status.

These counts need to be put in the tot_# fields from the Catalogue field. The status field, however, is not a number, it's a character.

That's where appl_status comes into play. Appl_status maps the Status character to the Status_num which as I explained related to the Tot_# fields in the Catalogue table.

Any thoughts on how I can get the Count(*), status from the data_file table, into the Catalogue Tot_# fields usiing appl_status as a translation?

 

Any key linkage?

 

I'm not familiar with the term. But I'm farily confident the answer is No.
There are no other fields which can help relate this data.

Ever used Informix as your main database?

 

Ok so is Tot_1 the total count for status 1
Tot_2 for status 2, etc?

oh and for appl table is status_num the count for each status that you will be updating the cat table?

 

Yea Basically.

data_file will have like 5+ status codes (A, N, X, BD, F)

Catalogue has several Tot_# fields that represent these status codes.

Appl_Status translate. Status_num = 1; status = "A"
Status_num = 2; status="N"

EDIT: Status_Num is NOT the count. It's simply a number... 1 through like 10. The count of the status codes will be in the hundreds or thousands.

 

select count(*), status from data_file group by status

Results:
1209 A
39 X
9889 N

Data from appl_status...

status_num, status
1, A
2, X
3, N

Catalogue table needs updated.
Tot_1 = 1209
Tot_2 = 39
Tot_3 = 9889

See?

 

Yea I'm trying to figure out the insert or update statement without using keys that link the tables

 

Our vendor for this system has a program that does this but it takes 15 minutes to do this one time and we need to do it 1200 times over night. I have no doubt this can be done Much much faster with a bit of SQL. Worse case scenario; I have to do it one status at a time (hard coded) rather than bulk. But it should still take less than a minute each run.

 

I think I've got the select part down just trying to figure out the updating part..

SELECT Count(data_file.Status) AS CountOfStatus, data_file.Status, appl_status.status_num
FROM data_file, appl_status
where data_file.status = appl_status.status
GROUP BY data_file.Status, appl_status.status_num;

This returns

Count of Status.........Status...............Status_num
3................................a................ .........1
2................................b................ .........2
4................................c................ .........3
6................................d................ .........4

etc

 

I dont think this is useful information but here it is.

The 1200 entries I mentioned are 1200 different data_file tables. If I can do this with one; I can easily modify my script to do all 1200. Anyway; the true name of data_file is something like dfs_323984

There is a field in Catalogue called dfs_name
The dfs_name field contains the name of the data_file table.

That's a link....sorta.... but I dont think that's useful.

UC

 

Well I think I have it but it is hokey...there is a better way but I should study some....

Unless I'm misunderstanding the problem.

I created a table called temptable.

I ran the following which updates the temptable with the data shown in an above post.




Then I run the following:

This would need to be run for each status_num.

Like I said there is a way to make this more streamline by pulling the status num, creating a variable and running it for each unique status_num but I'd do that in VB.

This is very hokey but it will theoretically work. If I understandthe problem correctly.

End results of a once empty table:

 

Looks good. Unfortunately Access is not allowing me to do a Count on the text field from data_file. I get an ODBC Call failure on the linked table. ARGH.

Edit: Kinda irrelevant I guess; I won't be using Access in final implementation but rather Informix via shell script. Let me see...

 

Doh; Informix says
298: COUNT(DISTINCT ...) may be used only with a simple column.

 

depending upon the size of the data I'd copy it out work on it in SQL server or Access and the import the results back in....

 

7 Million records a night.