ANSI- query to Access query, Help.

[javanic]

Hi.

I've got this Oracle query that I need to duplicate in Access (or Excel). Can someone tell me how to duplicate this functionality?

SELECT DISTINCT
A.SUB_TYPE, A.USAG_LOT_NUMBER
FROM TBL.TRACES A
WHERE A.SUB_TYPE='TUBE'
START WITH A.TO_MATL_ID =
(SELECT MATL_ID FROM TBL.PRODUCTS
WHERE MATL_SUB_TYPE = 'ROD' AND
MATL_SERIAL_NUMBER='4468')
CONNECT BY PRIOR A.FROM_MATL_ID =
A.TO_MATL_ID


It's the START WITH and CONNECT BY PRIOR that I haven't figured out in particular.

Thanks,


- javanic -

 

[scriverb]

I think this handles the first part but I am not an ORACLE programmer. Just taking a shot at it:

SELECT DISTINCT
A.SUB_TYPE, A.USAG_LOT_NUMBER
FROM TBL.TRACES A
WHERE A.SUB_TYPE='TUBE'
AND A.TO_MATL_ID =
(SELECT MATL_ID FROM TBL.PRODUCTS
WHERE MATL_SUB_TYPE = 'ROD' AND
MATL_SERIAL_NUMBER='4468')

As for the following why don't you describe in plain words what this does in ORACLE and let me see if we can find a comparable way to do it with ACCESS SQL?

CONNECT BY PRIOR A.FROM_MATL_ID =
A.TO_MATL_ID

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[cheerio]

I am not an ORACLE user but I think the START WITH ... CONNECT BY PRIOR is used to work through a hierarchy defined in a self referencing table eg field Boss has a pointer back into an employee table to the employee record of the boss so you can work through a chain of command. So an Access replacement is non-trivial.

Ken

 

[javanic]

Thanks for your responses. I've been doing some digging in an Oracle book although I still need to duplicate this somehow in Access.

scriverb, Sorry. I couldn't have told you in plain words what 'connect by prior' meant when I posted because I had no idea myself. Cheerio is on to something though. My 20lb, barbel-replacing Oracle book uses an example of cows and bulls and offspring to demonstrate 'start with' (the cow to start the search with) and 'connect by prior' (connecting the offspring to the mother).

In my case, I'm tracking an original metal ingot as it gets formed into multiple metal rods. What I have is an individual metal rod serial number, then from that I must find all the other rods made from the same "mother" ingot.

My hope is fading that I'll be able to duplicate this in MS Query...


- javanic -

 

[scriverb]

Fear not we can figure this out. Let's start by identifying the fields and their meanings. List the key fields in the tables and relationship to each other.

We have two tables here tblTraces and tblProducts. Be very specific as to how you want to match which records to the other.

Then we will try to duplicate it.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[javanic]

OK. Let's give it a shot. Let me know if this is more confusing; it is to me. I'm not very familiar with the database structure.

tblTraces has information about metal ingots, TUBEs, and RODs, and probably other products as well. Each ingot, TUBE and ROD has a FROM_MATL_ID which refers to the "mother" material that made it and a TO_MATL_ID that refers to the "child" product that came from it. An ingot makes one or more TUBEs and a TUBE makes one or more RODs. tblProducts has specific information about the final product that goes out the manufacturing door.

First the original query and then my understanding in plain English:

SELECT DISTINCT
   A.SUB_TYPE, A.USAG_LOT_NUMBER
FROM TBL.TRACES A
WHERE A.SUB_TYPE = 'TUBE'
START WITH A.TO_MATL_ID = (SELECT MATL_ID
   FROM TBL.PRODUCTS
   WHERE MATL_SUB_TYPE = 'ROD' AND
         MATL_SERIAL_NUMBER = '4468')
CONNECT BY PRIOR A.FROM_MATL_ID = A.TO_MATL_ID

In plain English, I *think* what is happening is as follows:
Show me a lineage type output (ergo the START WITH and CONNECTY BY and PRIOR). Start with a known ROD (identified by known serial number) in tblProducts and find its "parent" TUBE in tblTraces. Then show all the child products coming from that same parent TUBE (root to leaf, because PRIOR is with the parent).

If PRIOR were with the child (TO_MATL_ID), then the report would be a child to parent (leaf to root).

<Gulp>. %)

- javanic -