conditional join?

[Guest]

I need a query to that will combine data from two tables (photos,
proveniences) but the method of record selection needs to vary based upon
whether or not data is present in up to four heirarchically ordered index
fields. There is a one to many relationship between any one index field and
the next lowest field in the hierarchy.

I need to form the most exclusive join possible. So if Table A can be
matched to any records in Table B using all of the 4 index fields then those
are the records I want. If Table can't be matched to any records in Table B
using all of the index fields I need to try to match it with any records
present in Table B using the three highest fields and so on until I have
exhausted all possible matches to table B using the index fields.

I am not sure how best to accomplish this. Any assistance would be
appreciated.

thanks,

Bart

 

[Guest]

Bart,

Can you give us a little more detail about the structure of the two tables,
and a couple of sample rows of data for each. Then provide us with an
example of what you would like your query to return, given the sample data
provided.

Personally, my guess is that if your have 4 fields to descripbe this
"heirarchical" structure, you are doing it wrong. If you want to define a
heirarchy, I recommend two fields (ID, autonumber) and (Parent_ID,
LongInteger). An example of the data for this might be depicted as below
(I've provided the Level field as a reference only). This table structure
would be a way to describe locations:

ID ParentID Description Level
1 NULL Earth 0
2 1 United States 1
3 1 Canada 1
4 2 New York 2
5 2 New Jersey 2
6 4 New York 3
7 4 Albany 3

To give you any more help, I'll need the info requested above.

Dale

 

[Guest]

Bart,

Can you give us a little more detail about the structure of the two tables,
and a couple of sample rows of data for each. Then provide us with an
example of what you would like your query to return, given the sample data
provided.

Personally, my guess is that if your have 4 fields to descripbe this
"heirarchical" structure, you are doing it wrong. If you want to define a
heirarchy, I recommend two fields (ID, autonumber) and (Parent_ID,
LongInteger). An example of the data for this might be depicted as below
(I've provided the Level field as a reference only). This table structure
would be a way to describe locations:

ID ParentID Description Level
1 NULL Earth 0
2 1 United States 1
3 1 Canada 1
4 2 New York 2
5 2 New Jersey 2
6 4 New York 3
7 4 Albany 3

To give you any more help, I'll need the info requested above.

Dale

 

[Guest]

Hi Chris,

I believe I did try what you suggest below and indeed got the result that
you suggest below which is indiscriminate in regard to whether a match was
possible at a deeper level. I am assuming I need to put in some conditional
coding but I don't know where I can do that.

Paul

 

[Chris2]

I need a query to that will combine data from two tables (photos,
proveniences) but the method of record selection needs to vary based upon
whether or not data is present in up to four heirarchically ordered index
fields. There is a one to many relationship between any one index field and
the next lowest field in the hierarchy.

I need to form the most exclusive join possible. So if Table A can be
matched to any records in Table B using all of the 4 index fields then those
are the records I want. If Table can't be matched to any records in Table B
using all of the index fields I need to try to match it with any records
present in Table B using the three highest fields and so on until I have
exhausted all possible matches to table B using the index fields.

I am not sure how best to accomplish this. Any assistance would be
appreciated.

thanks,

Bart

Bart,

Based on your descriptions, it almost seems as if there are several self-referencing index
combinations, "There is a one to many relationship between any one index field and the
next lowest field in the hierarchy."

I have guessed mightily as to how to represent this below.


Tables:

CREATE TABLE TableA
(Index1 INTEGER
,Index2 INTEGER
,Index3 INTEGER
,Index4 INTEGER
,CONSTRAINT pk_TableA
PRIMARY KEY (Index1
,Index2
,Index3
,Index4)
,CONSTRAINT un_TableA_Index1
UNIQUE (Index1)
,CONSTRAINT un_TableA_Index2
UNIQUE (Index2)
,CONSTRAINT un_TableA_Index3
UNIQUE (Index3)
,CONSTRAINT un_TableA_Index4
UNIQUE (Index4)
,CONSTRAINT fk_TableA_TableA_Index2
FOREIGN KEY (Index2)
REFERENCES TableA (Index1)
,CONSTRAINT fk_TableA_TableA_Index3
FOREIGN KEY (Index3)
REFERENCES TableA (Index2)
,CONSTRAINT fk_TableA_TableA_Index4
FOREIGN KEY (Index4)
REFERENCES TableA (Index3)
)

CREATE TABLE TableB
(Index1 INTEGER
,Index2 INTEGER
,Index3 INTEGER
,Index4 INTEGER
,CONSTRAINT pk_TableB
PRIMARY KEY (Index1
,Index2
,Index3
,Index4)
,CONSTRAINT un_TableB_Index1
UNIQUE (Index1)
,CONSTRAINT un_TableB_Index2
UNIQUE (Index2)
,CONSTRAINT un_TableB_Index3
UNIQUE (Index3)
,CONSTRAINT un_TableB_Index4
UNIQUE (Index4)
,CONSTRAINT fk_TableB_TableB_Index2
FOREIGN KEY (Index2)
REFERENCES TableB (Index1)
,CONSTRAINT fk_TableB_TableB_Index3
FOREIGN KEY (Index3)
REFERENCES TableB (Index2)
,CONSTRAINT fk_TableB_TableB_Index4
FOREIGN KEY (Index4)
REFERENCES Tableb (Index3)
)


Query:

SELECT T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2
AND T1.Index3 = T2.Index3
AND T1.Index4 = T2.Index4)
OR (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2
AND T1.Index3 = T2.Index3)
OR (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2)
OR (T1.Index1 = T2.Index1)

I believe that fits the decision structure you described. I did not have any test data or
desired results to test it on, and so I do not know whether it works or not (or even that
I correctly interpreted what you want).


Comments:

As far as I can tell, the above query is logically the same as:

SELECT T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON T1.Index1 = T2.Index1

I would think that the fourth conditional group (in the first query above) would include
any row matches found in the first three conditional groups.


Sincerely,

Chris O.

 

[Chris2]

Hi Chris,

I believe I did try what you suggest below and indeed got the result that
you suggest below which is indiscriminate in regard to whether a match was
possible at a deeper level. I am assuming I need to put in some conditional
coding but I don't know where I can do that.

Paul

Paul,

I am unable to determine what it is you wish to do.

Are you trying to distinguish between what records were found with 4-column matching,
3-column matching, 2-column matching, and 1-column matching?

If so, you could run a UNION with four SELECT statements, with an additional column to
distinguish the groups, like so:

Query:

SELECT 1 As MatchingGroups
,T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2
AND T1.Index3 = T2.Index3
AND T1.Index4 = T2.Index4)
UNION ALL
SELECT 2 As MatchingGroups
,T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2
AND T1.Index3 = T2.Index3)
UNION ALL
SELECT 3 As MatchingGroups
,T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1
AND T1.Index2 = T2.Index2)
UNION ALL
SELECT 4 As MatchingGroups
,T2.Index1
,T2.Index2
,T2.Index3
,T2.Index4
FROM TableA AS T1
INNER JOIN
TableB AS T2
ON (T1.Index1 = T2.Index1)


Comments:

I tried entering some test data into the tables I created, and discovered something that
makes me feel certain I did not guess structure correctly.

With the way the rules are set up, all four columns appear to have to have the same
values, rendering columns 2, 3, and 4 irrelevant (which can't be right, I think).

Can you please post your table structures (including all indexes and relationships),
sample data, desired results, and any SQL attempted to date (including why it didn't
work).


Sincerely,

Chris O.

 

[Guest]

Sorry Chris and Dale, I thought I sent some examples yesterday but it seems
nothing got added to my first reply. I hope these samples help but I suspect
they probably won't.

I have been working out the issues in the native database language (this is
a Progress database I am linking to) though I am trying to create a front end
in Access for a special application. It is pretty complex in Progress, at
least the way I have it set up now, so I don't know how likely I am to be
able to achieve this in Access.

Here is a sample of the photo data:
photo.level1 photo.level2 photo.level3 photo.level4 photo.Id
21N 0 21N-2M
21N 3 D 21N-3M
21N 2 21N-31M
21N 26 F 2 21N-675M
21N 29 A 21N-804M
21N 152 G 9 21N-887M
21N 912 D 3 21N-3302M

Here is a sample of some provenience data:
prov.recId prov.level1 prov.level2 prov.level3 prov.level4 prov.Id
2 21N 3 0 21N1
3 21N 2 0 21N1
4 21N 26 0 21N24
5 21N 29 A 0 21N27
6 21N 152 0 21N17
7 21N 912 D 3 21N0


Here is an example of what I am trying to achieve:
photo.Id prov.Id
21N-2M
21N-3M 21N1
21N-31M 21N1
21N-675M 21N24
21N-804M 21N27
21N-887M 21N17
21N-3302M 21N0

The second photo record (21N-3M) has values in the first three levels and
matches the first prov record at the first two levels. Because there is no
prov record that matches to the third level the join is achieved on the
values of the first two levels. The third photo record (21N-31M) has values
in only the first two levels. The join is achieved with the second prov
record using the first two levels. The fourth photo record (21N-675M) has
values in all levels but the lowest level at which a join can be made with
prov is at level two with the third prov record. The join is achieved with
the second prov record using the first two levels. The first photo cannot be
joined to prov because there are no prov records that both match its level
one value and do not have values for lower levels.

The levels can be thought to represent geography with each level describing
a more restricted area within the broader area identified by the level above
it. A photo record identified at the first level would be a general shot of a
broad area. Photographs that identify proveniences to levels 2, 3 or 4 are
indicate more specific locations within the area described the higher levels.
A provenience with a level 1 value of 21N and a level 2 value of 3 lies
within the area described by 21N but adjacent to a provenience with a level 1
value of 21N and a level 2 value of 2. To use the geographic analogy if the
level 1 21N is Europe the level 2 value 3 may be Germany and the level 2
value 2 may be France. To be matched to a prov record, the provenience of the
photo must be completely contained by the provenience. Therefore a photo that
is identified simply as being of Europe cannot be joined to a provenience
that is identified as France. Where a photograph is identified to a lower
level than any available prov record, the photograph can be associated with
the next highest level. To use the analogy it would be correct (if not
precise) to say that a photograph of France is a photograph of Europe.

I am not sure that helped but thanks for your interest.

Paul

 

[Chris2]

I am not sure that helped but thanks for your interest.

Paul

:

Paul,

I just saw this post right now.

I'm leaving a the moment, but I'll be able to look at it later tonight and/or tomorrow
morning.


Sincerely,

Chris O.

 

[Chris2]

Sorry Chris and Dale, I thought I sent some examples yesterday but it seems
nothing got added to my first reply. I hope these samples help but I suspect
they probably won't.

Paul,

I have forgotten to send posts, or had them disappear, myself.

I see that you did not provide a description of your table structures, including indexes
and relationships. That will make it more difficult to give potential solutions.

I have been working out the issues in the native database language (this is
a Progress database I am linking to) though I am trying to create a front end
in Access for a special application. It is pretty complex in Progress, at
least the way I have it set up now, so I don't know how likely I am to be
able to achieve this in Access.

Just be aware, the sample data displayed below does not support your earlier description.

In your original post you wrote: "data is present in up to four heirarchically ordered
index fields."

None of these columns are in any form of hierarchy that I can determine just from looking
at the data.

A hierarchy in a relational database refers to various types of recursive relationships
(self-referencing cardinality), where a table as a foreign key pointing at another column
in itself. There doesn't appear to be anything like that here.

Here is a sample of the photo data:
photo.level1 photo.level2 photo.level3 photo.level4 photo.Id
21N 0 21N-2M
21N 3 D 21N-3M
21N 2 21N-31M
21N 26 F 2 21N-675M
21N 29 A 21N-804M
21N 152 G 9 21N-887M
21N 912 D 3 21N-3302M

Here is a sample of some provenience data:
prov.recId prov.level1 prov.level2 prov.level3 prov.level4 prov.Id
2 21N 3 0 21N1
3 21N 2 0 21N1
4 21N 26 0 21N24
5 21N 29 A 0 21N27
6 21N 152 0 21N17
7 21N 912 D 3 21N0

So, you want to find photo.Id values and prov.Id values based on matching
photo.level1-level4 with prov.level1-level4 (1-4, 1-3, 1-2, 1)? This does match up with
what you were saying earlier.

Here is an example of what I am trying to achieve:
photo.Id prov.Id
21N-2M

I don't understand. Why was no match found? The level1 values in both tables matched.

21N-3M 21N1
21N-31M 21N1
21N-675M 21N24
21N-804M 21N27
21N-887M 21N17
21N-3302M 21N0

The second photo record (21N-3M) has values in the first three levels and
matches the first prov record at the first two levels. Because there is no
prov record that matches to the third level the join is achieved on the
values of the first two levels.

How is this different from finding the same Id values based on matching only the level1
columns in both tables?

Is is that you want to excluce new attempts to match photo.Id with prov.Id with fewer
columns **once** a match has been located?

Hmm, that may be possible.

The first photo cannot be joined to prov because there are no prov
records that both match its level one value and do not have values
for lower levels.

This confuses me.

It appears to state:

WHERE photo.level1 = provenience.level1
AND photo.level2 <> provenience.level2
AND photo.level3 <> provenience.level3
AND photo.level4 <> provenience.level4

This does not match any of the other AND/OR logic that has been discussed so far.

I now have no certain idea what AND/OR logic is required.

Please provide exact AND/OR logic statements.


<snip>


Tables:

I guessed as best as I could on your table structures (again).


CREATE TABLE photo
(level1 TEXT(255)
,level2 TEXT(255)
,level3 TEXT(255)
,level4 TEXT(255)
,Id TEXT(255)
,CONSTRAINT pk_photo
PRIMARY KEY (level1
,level2
,level3
,level4)
)

CREATE TABLE provenience
(recId INTEGER
,level1 TEXT(255)
,level2 TEXT(255)
,level3 TEXT(255)
,level4 TEXT(255)
,Id TEXT(255)
,CONSTRAINT pk_provenience
PRIMARY KEY (recID)
,CONSTRAINT fk_provenience_photo_level1_level2_level3_level4
FOREIGN KEY (level1
,level2
,level3
,level4)
REFERENCES photo (level1
,level2
,level3
,level4)
)


Sample Data:

photo
level1, level2, level3, level4, Id
21N, 0, , , 21N-2M
21N, 3, D, , 21N-3M
21N, 2, , , 21N-31M
21N, 26, F, 2, 21N-675M
21N, 29, A, , 21N-804M
21N, 152, G, 9, 21N-887M
21N, 912, D, 3, 21N-3302M

provenience
recId, level1, level2, level3, level4, Id
2, 21N, 3, , 0, 21N1
3, 21N, 2, , 0, 21N1
4, 21N, 26, , 0, 21N24
5, 21N, 29, A, 0, 21N27
6, 21N, 152, , 0, 21N17
7, 21N, 912, D, 3, 21N0


I ran into a problem attempting to load this sample data (I saw it coming before I
actually attempted it).

The primary keys of the tables cannot contain null values, and because of this I could not
load the data.

I have guessed twice on your table structures, and was wrong on both occasions.

Can you please post your table structures, including indexes and relationships?

I have some ideas about to do this, but until I can build a pair of tables that match
yours that are loaded with the data, I will have no way to find out if they will work.


Sincerely,

Chris O.

 

[Guest]

I have been trying to simplify my descriptions of the issue and the database
structure. My database is a Progress database which I am linking to through
ODBC. I'm not sure how much information you want but I will supply the SQL
translation of the Progress schema for a number of tables below. What I have
been refering to as the prov table is called crmProv and is an interfile
between crmop and the four hierarchical tables of site, operation, suboper
and lot (collectively known as the provenience tables). The crmprov table is
a poor solution to address a problem stemming from a lack of strong rules
around the use of the provenience tables. Crmop has foreign keys for two of
the provenience tables so in order for crmprov to handle alternative linkings
between crmop and provenience duplicate provenience fields had to be created
(with different names) to carry the foreign keys found in crmop.

CREATE TABLE crmprov (
crmsitenum integer not null,
crmsitecode varchar (1) not null,
crmopcode integer not null,
crmresnum integer null,
sitenum integer not null,
sitecode varchar (1) not null,
opcode integer null,
sopcode varchar (1) null,
lotnumber integer null
)
go
CREATE INDEX crmprov##crm ON crmprov (crmsitecode, crmsitenum, crmopcode,
crmresnum)
go
CREATE INDEX crmprov##prov ON crmprov (sitecode, sitenum, opcode, sopcode,
lotnumber)

CREATE TABLE photo (
sitenum integer not null,
sitecode varchar (1) not null,
filmtype varchar (1) not null,
photonumber integer not null,
opcode integer not null,
sopcode varchar (1) null,
lotnumber integer null,
personcode integer null,
regioncode varchar (1) not null,
shotwhen datetime null,
tagnum integer null,
raphoto tinyint null,
doccode varchar (15) null,
loccode varchar (50) null,
duploccode varchar (20) null,
aspect varchar (10) null,
caption3 varchar (150) null
)
go
CREATE INDEX photo##document ON photo (doccode)
go
CREATE INDEX photo##lot ON photo (sitecode, sitenum, opcode, sopcode,
lotnumber)
go
CREATE UNIQUE INDEX photo##negativenum ON photo (sitecode, sitenum,
filmtype, regioncode, photonumber)
go
CREATE INDEX photo##ranum ON photo (regioncode, filmtype, photonumber)
go
CREATE INDEX photo##raphoto ON photo (raphoto)
go
CREATE INDEX photo##timber ON photo (sitecode, sitenum, tagnum)
go
CREATE INDEX photo##worker ON photo (regioncode, personcode)

CREATE TABLE crmop (
sitenum integer not null,
sitecode varchar (1) not null,
opcode integer null,
ntsnum varchar (6) null,
sitetype varchar (40) null,
latitude varchar (6) null,
longitude varchar (7) null,
utmgrid varchar (5) null,
utmeast decimal(4,1) null,
utmnorth decimal(4,1) null,
legal varchar (30) null,
othermap varchar (30) null,
airphotos varchar (15) null,
access_##1 varchar (50) null,
access_##2 varchar (50) null,
access_##3 varchar (50) null,
access_##4 varchar (50) null,
access_##5 varchar (50) null,
owner varchar (50) null,
siteslope integer null,
elevasl integer null,
areasqm integer null,
aspect varchar (10) null,
bordennum varchar (30) null,
sitename varchar (40) null,
nsdimens integer null,
ewdimens integer null,
majordrain varchar (30) null,
minordrain varchar (30) null,
comments##1 varchar (50) null,
comments##2 varchar (50) null,
comments##3 varchar (50) null,
comments##4 varchar (50) null,
comments##5 varchar (50) null,
comments##6 varchar (50) null,
comments##7 varchar (50) null,
comments##8 varchar (50) null,
comments##9 varchar (50) null,
comments##10 varchar (50) null,
comments##11 varchar (50) null,
comments##12 varchar (50) null,
comments##13 varchar (50) null,
comments##14 varchar (50) null,
comments##15 varchar (50) null,
opentext##1 varchar (30) null,
opentext##2 varchar (30) null,
opentext##3 varchar (30) null,
opentext##4 varchar (30) null,
opentext##5 varchar (30) null,
opennum##1 integer null,
opennum##2 integer null,
opennum##3 integer null,
opennum##4 integer null,
opennum##5 integer null,
parkcode varchar (3) not null,
diameter integer null,
manageflag tinyint null,
ntsyear integer null
)
go
CREATE UNIQUE INDEX crmop##crmop ON crmop (sitecode, sitenum, opcode)
go
CREATE INDEX crmop##park ON crmop (parkcode)

CREATE TABLE site (
sitenum integer not null,
sitecode varchar (1) not null,
sitename varchar (40) null,
bordennum varchar (30) null,
parkcode varchar (3) null,
nexttimber integer null,
regioncode varchar (1) not null,
nextagg integer null,
print_ tinyint null,
datumloc varchar (50) null
)
go
CREATE INDEX site##park ON site (parkcode)
go
CREATE INDEX site##print_ ON site (print_, sitecode, sitenum)
go
CREATE INDEX site##region ON site (regioncode, sitecode, sitenum)
go
CREATE UNIQUE INDEX site##site ON site (sitecode, sitenum)

CREATE TABLE operation (
sitenum integer not null,
sitecode varchar (1) not null,
opcode integer not null,
bordennum varchar (30) null,
opname varchar (40) null,
opdesc varchar (40) null,
print_ tinyint null
)
go
CREATE UNIQUE INDEX operation##operation ON operation (sitecode, sitenum,
opcode)
go
CREATE INDEX operation##print_ ON operation (print_, sitecode, sitenum,
opcode)

CREATE TABLE suboper (
sitenum integer not null,
sitecode varchar (1) not null,
opcode integer not null,
sopcode varchar (1) not null,
soplocation varchar (50) null,
sopdimens varchar (25) null,
exdesc varchar (25) null,
numoflots integer null,
print_ tinyint null
)
go
CREATE INDEX suboper##print_ ON suboper (print_, sitecode, sitenum, opcode,
sopcode)
go
CREATE UNIQUE INDEX suboper##suboper ON suboper (sitecode, sitenum, opcode,
sopcode)

CREATE TABLE lot (
sitenum integer not null,
sitecode varchar (1) not null,
opcode integer not null,
sopcode varchar (1) not null,
lotnumber integer not null,
coordinates##1 decimal(6,2) null,
coordinates##2 decimal(6,2) null,
coordinates##3 decimal(6,2) null,
coordinates##4 decimal(6,2) null,
coordinates##5 decimal(6,2) null,
coordinates##6 decimal(6,2) null,
coordinates##7 decimal(6,2) null,
coordinates##8 decimal(6,2) null,
coordinates##9 decimal(6,2) null,
coordinates##10 decimal(6,2) null,
coordinates##11 decimal(6,2) null,
nextfind integer null,
lotfeature integer null,
lotdate datetime null,
print_ tinyint null,
comments##1 varchar (40) null,
comments##2 varchar (40) null,
comments##3 varchar (40) null,
comments##4 varchar (40) null,
comments##5 varchar (40) null
)
go
CREATE UNIQUE INDEX lot##lotindex ON lot (sitecode, sitenum, opcode,
sopcode, lotnumber)
go
CREATE INDEX lot##print_ ON lot (print_, sitecode, sitenum, opcode, sopcode,
lotnumber)

I will include the Progress procedure I am using to achieve the results.
While you may not be familiar with Progress the language is quite easy to
understand and you should probably be able figure out more easily what it is
I am after from reading the code. It may not be the best implementation but
it is working.

In SQL is it possible to include some conditional statements within Select
(e.g. IIF, CASE) to determine the level of provenience recorded in the photo
record and to direct the join to one of four instances of the crmProv table
each with different join critera?

Here is the Progress procedure that outputs the data I need:

OUTPUT TO u:\datafile\crmPhoto1.txt.

FIND park WHERE parkcode = "H56" NO-LOCK.
FOR EACH site OF park no-lock, EACH photo OF site NO-LOCK
BREAK BY photo.siteCode BY photo.siteNum BY photo.filmType BY photo.photoNum:
FIND FIRST crmProv WHERE
crmProv.siteNum = photo.siteNum AND
crmProv.siteCode = photo.siteCode AND
crmProv.opCode = photo.opCode AND
crmProv.sopCode = photo.sopCode AND
crmProv.lotNum = photo.lotNum
AND crmResNum = 0
NO-LOCK NO-ERROR.
IF AVAILABLE crmprov THEN DO:
FOR EACH crmProv WHERE
crmProv.siteNum = photo.siteNum AND
crmProv.siteCode = photo.siteCode AND
crmProv.opCode = photo.opCode AND
crmProv.sopCode = photo.sopCode AND
crmProv.lotNum = photo.lotNum
AND crmResNum = 0
:
PUT UNFORMATTED photo.siteNum "~t" photo.siteCode "~t"
photo.opCode "~t"
photo.sopCode "~t" photo.lotNum "~t"
string(photo.siteNum) + photo.siteCode + "-" +
string(photo.photoNum) +
filmType "~t" crmProv.siteNum "~t" crmProv.siteCode "~t"
crmProv.opCode "~t" crmProv.sopCode "~t" crmProv.lotNum "~t".
IF crmProv.crmResNum > 0 THEN PUT UNFORMATTED
string(crmProv.crmsiteNum) + crmProv.crmsiteCode +
string(crmProv.crmopCode) + "-" + string(crmProv.crmresNum) "~n".
ELSE PUT UNFORMATTED
string(crmProv.crmsiteNum) + crmProv.crmsiteCode +
string(crmProv.crmopCode) "~n".
END.
NEXT.
END.
ELSE FIND FIRST crmProv WHERE
crmProv.siteNum = photo.siteNum AND
crmProv.siteCode = photo.siteCode AND
crmProv.opCode = photo.opCode AND
crmProv.sopCode = photo.sopCode AND
crmProv.lotNum = 0
AND crmResNum = 0
NO-LOCK NO-ERROR.
IF AVAILABLE crmprov THEN DO:
FOR EACH crmProv WHERE
crmProv.siteNum = photo.siteNum AND
crmProv.siteCode = photo.siteCode AND
crmProv.opCode = photo.opCode AND
crmProv.sopCode = photo.sopCode AND
crmProv.lotNum = 0
AND crmResNum = 0
:
PUT UNFORMATTED photo.siteNum "~t" photo.siteCode "~t"
photo.opCode "~t"
photo.sopCode "~t" photo.lotNum "~t"
string(photo.siteNum) + photo.siteCode + "-" +
string(photo.photoNum) +
filmType "~t" crmProv.siteNum "~t" crmProv.siteCode "~t"
crmProv.opCode "~t" crmProv.sopCode "~t" crmProv.lotNum "~t".
IF crmProv.crmResNum > 0 THEN PUT UNFORMATTED
string(crmProv.crmsiteNum) + crmProv.crmsiteCode +
string(crmProv.crmopCode) + "-" + string(crmProv.crmresNum)
"~n".
ELSE PUT UNFORMATTED
string(crmProv.crmsiteNum) + crmProv.crmsiteCode +
string(crmProv.crmopCode) "~n".
END.
NEXT.
END.
ELSE FIND FIRST crmProv WHERE
crmProv.siteNum = photo.siteNum AND
crmProv.siteCode = photo.siteCode AND
crmProv.opCode = photo.opCode AND
crmProv.sopCode = "" AND
crmProv.lotNum = 0
AND crmResNum = 0
NO-LOCK NO-ERROR.
IF AVAILABLE crmprov THEN DO:
FOR EACH crmProv WHERE
crmProv.siteNum = photo.siteNum AND
crmProv.siteCode = photo.siteCode AND
crmProv.opCode = photo.opCode AND
crmProv.sopCode = "" AND
crmProv.lotNum = 0
AND crmResNum = 0
:
PUT UNFORMATTED photo.siteNum "~t" photo.siteCode "~t"
photo.opCode "~t"
photo.sopCode "~t" photo.lotNum "~t"
string(photo.siteNum) + photo.siteCode + "-" +
string(photo.photoNum) +
filmType "~t" crmProv.siteNum "~t" crmProv.siteCode "~t"
crmProv.opCode "~t" crmProv.sopCode "~t" crmProv.lotNum "~t".
IF crmProv.crmResNum > 0 THEN PUT UNFORMATTED
string(crmProv.crmsiteNum) + crmProv.crmsiteCode +
string(crmProv.crmopCode) + "-" + string(crmProv.crmresNum)
"~n".
ELSE PUT UNFORMATTED
string(crmProv.crmsiteNum) + crmProv.crmsiteCode +
string(crmProv.crmopCode) "~n".
END.
NEXT.
END.
ELSE FIND FIRST crmProv WHERE
crmProv.siteNum = photo.siteNum AND
crmProv.siteCode = photo.siteCode AND
crmProv.opCode = 0 AND
crmProv.sopCode = "" AND
crmProv.lotNum = 0
AND crmResNum = 0
NO-LOCK NO-ERROR.
IF AVAILABLE crmprov THEN DO:
FOR EACH crmProv WHERE
crmProv.siteNum = photo.siteNum AND
crmProv.siteCode = photo.siteCode AND
crmProv.opCode = 0 AND
crmProv.sopCode = "" AND
crmProv.lotNum = 0
AND crmResNum = 0
:
PUT UNFORMATTED photo.siteNum "~t" photo.siteCode "~t"
photo.opCode "~t"
photo.sopCode "~t" photo.lotNum "~t"
string(photo.siteNum) + photo.siteCode + "-" +
string(photo.photoNum) +
filmType "~t" crmProv.siteNum "~t" crmProv.siteCode "~t"
crmProv.opCode "~t" crmProv.sopCode "~t" crmProv.lotNum "~t".
IF crmProv.crmResNum > 0 THEN PUT UNFORMATTED
string(crmProv.crmsiteNum) + crmProv.crmsiteCode +
string(crmProv.crmopCode) + "-" + string(crmProv.crmresNum)
"~n".
ELSE PUT UNFORMATTED
string(crmProv.crmsiteNum) + crmProv.crmsiteCode +
string(crmProv.crmopCode) "~n".
END.
NEXT.
END.
ELSE PUT UNFORMATTED photo.siteNum "~t" photo.siteCode "~t" photo.opCode
"~t"
photo.sopCode "~t" photo.lotNum "~t"
string(photo.siteNum) + photo.siteCode + "-" + string(photo.photoNum) +
filmType "~t~t~t~t~t~t" string(photo.siteNum) + photo.siteCode +
string(photo.opCode) "~n".
END.


thanks,

Paul

 

[Chris2]

I'm not sure how much information you want but I will supply the SQL
translation of the Progress schema for a number of tables below.

Ok, two things are still missing.

I need a mini-chart that translates the previously supplied level1-4 column names into the
real column names so that I know what sample data goes where.

No primary keys or foreign keys were shown. Please add those declarations (just those, no
need to repost the whole set of table descriptions).


Note for future consideration: When supplying DDL SQL, non-relevant columns (those that
aren't going to be used in the query/process, or those that do not describe primary or
foreign keys), may be omitted in a newsgroup posting.

I will include the Progress procedure I am using to achieve the results.
While you may not be familiar with Progress the language is quite easy to
understand and you should probably be able figure out more easily what it is
I am after from reading the code. It may not be the best implementation but
it is working.

I am glad it is working. While I can guess about what some of the Progress code does,
guessing isn't what you need and me doing that won't do you any good. (I would just be
stumbling through one interpretation after another, with you correcting me most of the
time.)

In SQL is it possible to include some conditional statements within Select
(e.g. IIF, CASE) to determine the level of provenience recorded in the photo
record and to direct the join to one of four instances of the crmProv table
each with different join critera?

I suppose you could try to use IIF and SWITCH, but I wasn't planning on that.

I was going to try querying for all 4-column matches, and then doing a query for all
3-column matches where NOT EXISTS all the 4-column matches, and then doing a query for all
2-column matches where NOT EXISTS all the 4-column and 3-column matches, etc.

Does that even remotely sound like what you are trying to achieve? (It does to me, but
that doesn't mean I am right.)


Sincerely,

Chris O.

 

[Guest]

Thanks for the lead Chris, that sounds promising. I will have to look into
the EXISTS condition syntax when I get back after the weekend. I have added
the information about the correspondence between the hypothetical "level"
fields and the actual fields, and have identified the keys. See below.
Multi-field keys are very common in our database, not so common in Access
databases I believe.

I am just about to head off for the weekend so have a good one and we'll
connect later.

Paul

Ok, two things are still missing.

I need a mini-chart that translates the previously supplied level1-4 column names into the
real column names so that I know what sample data goes where.

photo.level1 = photo.siteNum + photo.siteCode
photo.level2 = photo.opCode
photo.level3 = photo.sopCode
photo.level4 = photo.lotNum

prov.level1 = prov.siteNum + prov.siteCode
prov.level2 = prov.opCode
prov.level3 = prov.sopCode
prov.level4 = prov.lotNum

No primary keys or foreign keys were shown. Please add those declarations (just those, no
need to repost the whole set of table descriptions).

The crmProv table doesn't really have a primary key (unique index) though
all of its fields are indexed and are foreign keys.

The primary key(s) for the photo table are (collectively) siteNum, siteCode,
filmType, photoNumber and regionCode (though regionCode is really a non-issue
as it is always the same in our database). The foreign keys in photo are
siteNum, siteCode, opCode, sopCode, and lotNumber.

The site table has two primary keys siteNum and siteCode and one foreign key
parkCode.
The operation table has three primary keys, siteNum, siteCode and opCode and
no foreign keys.
The subOper table has four primary keys, siteNum, siteCode, opCode and
sopCode and no foreign keys.
The lot table has five primary keys, siteNum, siteCode, opCode, sopCode and
lotNumber and no foreign keys.

Note for future consideration: When supplying DDL SQL, non-relevant columns (those that
aren't going to be used in the query/process, or those that do not describe primary or
foreign keys), may be omitted in a newsgroup posting.

Sorry. I will watch that next time.

I am glad it is working. While I can guess about what some of the Progress code does,
guessing isn't what you need and me doing that won't do you any good. (I would just be
stumbling through one interpretation after another, with you correcting me most of the
time.)



I suppose you could try to use IIF and SWITCH, but I wasn't planning on that.

I was going to try querying for all 4-column matches, and then doing a query for all
3-column matches where NOT EXISTS all the 4-column matches, and then doing a query for all
2-column matches where NOT EXISTS all the 4-column and 3-column matches, etc.

Does that even remotely sound like what you are trying to achieve? (It does to me, but
that doesn't mean I am right.)

I think that does sound like it could work, and may be just what I was
looking for. I will take a closer look at that. Thanks, Paul.

 

[Chris2]

Thanks for the lead Chris, that sounds promising. I will have to look into
the EXISTS condition syntax when I get back after the weekend. I have added
the information about the correspondence between the hypothetical "level"
fields and the actual fields, and have identified the keys. See below.
Multi-field keys are very common in our database, not so common in Access
databases I believe.

I am just about to head off for the weekend so have a good one and we'll
connect later.

Paul

Paul,

Ok, I have been working at it for a while now.

It isn't perfect, as one row of the actual results does not match the given desired
results.

You wanted:

photo.Id prov.Id
21N-2M
21N-3M 21N1
21N-31M 21N1
21N-675M 21N24
21N-804M 21N27
21N-887M 21N17
21N-3302M 21N0

This series of queries produces:

photoID crmprovID
21N-2M 21N27
21N-3M 21N1
21N-31M 21N1
21N-675M 21N24
21N-804M 21N27
21N-887M 21N17
21N-3302M 21N0

(I moved a couple of rows around to appear in the same order as the originally given
desired results.)

As can be seen, the first row is not producing the desired results.

The 21N-2M value from photo is **going** to match via the Id column in crmprov unless
something matches it earlier, and in this case, nothing does. I do not see how the
originally given sample data how that row of the desired results is possible.


Why don't you take a look at this.

Tables:

CREATE TABLE photo
(siteNum INTEGER NOT NULL
,siteCode TEXT(1) NOT NULL
,opCode INTEGER
,sopCode TEXT(1)
,lotNum INTEGER
,photoId TEXT(255)
)

CREATE TABLE crmprov
(siteNum INTEGER NOT NULL
,siteCode TEXT(1) NOT NULL
,opCode INTEGER
,sopCode TEXT(1)
,lotNum INTEGER
,crmprovId TEXT(255)
)

(I left all off all keys for the time being.)

I loaded the sample data originally given.


FourColumnMatches:

SELECT P1.photoID
,CP1.crmprovID
FROM photo AS P1
INNER JOIN
crmprov as CP1
ON (P1.siteNum & P1.siteCode
= CP1.siteNum & CP1.siteCode
AND P1.opCode = CP1.opCode
AND P1.sopCode = CP1.sopCode
AND P1.lotNum = CP1.lotNum)

ThreeColumnMatches:

SELECT DISTINCT
P1.photoID
,CP1.crmprovID
FROM photo AS P1
INNER JOIN
crmprov AS CP1
ON (P1.siteNum & P1.siteCode
= CP1.siteNum & CP1.siteCode)
AND (P1.opCode=CP1.opCode)
AND (P1.sopCode=CP1.sopCode)
WHERE P1.photoID NOT IN
(SELECT F1.photoID
FROM FourColumnMatches AS F1)
AND CP1.crmprovId NOT IN
(SELECT F2.crmprovId
FROM FourColumnMatches AS F2);

TwoColumnMatches:

SELECT DISTINCT P1.photoId
,CP1.crmprovId
FROM photo AS P1
INNER JOIN
crmprov AS CP1
ON (P1.siteNum & P1.siteCode
= CP1.siteNum & CP1.siteCode)
AND (P1.opCode = CP1.opCode)
WHERE P1.PhotoId NOT IN
(SELECT F1.photoId
FROM ThreeColumnMatches AS F1)
AND CP1.crmprovId NOT IN
(SELECT F2.crmprovId
FROM ThreeColumnMatches AS F2);


OneColumnMatches:

SELECT DISTINCT P1.photoId
,CP1.crmprovId
FROM photo AS P1
INNER JOIN
crmprov AS CP1
ON (P1.siteNum & P1.siteCode
= CP1.siteNum & CP1.siteCode)
WHERE P1.PhotoId NOT IN
(SELECT F1.photoId
FROM TwoColumnMatches AS F1)
AND CP1.crmprovId NOT IN
(SELECT F2.crmprovId
FROM TwoColumnMatches AS F2);


UnionAllMatches

SELECT F4.photoID
,F4.crmprovID
FROM FourColumnMatches AS F4
UNION
SELECT F3.photoID
,F3.crmprovID
FROM ThreeColumnMatches AS F3
UNION
SELECT F2.photoID
,F2.crmprovID
FROM TwoColumnMatches AS F2
UNION
SELECT F1.photoID
,F1.crmprovID
FROM OneColumnMatches AS F1
ORDER BY F4.photoID
,F4.crmprovID


This will definitely require testing on larger amounts of data.


It is also looks like a resource hog.


Sincerely,

Chris O

 

[Guest]

Hi Chris,

I just got back to work today and haven't had time to look closely at the
query you have developed but I will do that and get back to you. It looks
promising but you are right in that it will probably be a resource hog. I'm
not sure the SELECT statement needs the distinct condition and I'm also not
sure about the WHERE clause referencing photoID. It seems to me it should
reference the provenience fields but maybe it is accomplishing the same thing
more simply.

thanks,

Paul

 

[Guest]

Hi Chris,

It looks like I may have to rethink the whole approach as it is hanging on
the twoColumnMatches query with just over 4000 photo records and using
imported (not linked) tables.

Paul

 

[Chris2]

Hi Chris,

It looks like I may have to rethink the whole approach as it is hanging on
the twoColumnMatches query with just over 4000 photo records and using
imported (not linked) tables.

Paul

:

Paul,

I would like to say you should multi-post (a post that is in two groups in one combined
posting that is seen by both groups at once) a code-translation question to
microsoft.public.access.modulesdaovba and comp.databases.progress, but the problem is it
is such a large block of code, I am afraid it might not get answered.

Still, if there is no other way:

Take your two table definitions (photo and crmprov). Pare them down to only the six
relevant columns, plus keys and index definitions.

Produce a slightly larger set of sample data (say 10-12 rows in each table), and the same
for the desired results.

Then add the Progress code.

Post an entirely new question entitled:

Progress-DB Code Translation into MS Access VBA Needed.

Multi-Post that to the groups noted above.

Put "This is a multi-post" in the first line of the body of the message, before anything
else.

"I do not have control of my db schema." (To hopefully deflect commentary regarding the
design of the table.)

"I have a set of Progress procedural code that I need translate into VBA."

"If anyone has the time available to do this, I would appreciate it."

You might also include in the body, "I would really like this in SQL, but have not been
able to determine how, despite many attempts with multiple queries, NOT EXISTS, NOT IN, <>
ANY, etc." Toss in the group of five queries I've already provided and point out that
when attempted on even small amounts of data, they are too slow for your computer.

Your hope would be that there is at least one newsgroup helper in either of the groups
familiar with both systems who can translate the Progress procedural code for you and who
has the time to do it.

-------------------------------

If no one answers your question:

And if I were you, and understood the Progress code, I would take a shot at learning
VBA/DAO and translating the code directly.

The problem with this, I'm afraid, is that some of that Progress code looked like it
encapsulated a lot of functionality in very little syntax (vaguely 4GLish), and doing the
same things in VBA will probably require more coding than was necessary in Progress.


Sincerely,

Chris O.

 

[Guest]

Thanks for all your help Chris. I think I will put this aside for awhile and
consider whether something else can be done to make this easier.

Paul