SQL and ODBC Query Help

Hello:
I am using a shipping program that scans our Order Number and populates the shipping fields from a database file using an ODBC connection. Once the fields are populated, the user then prints out a shipping label and we writeback information to another database file using a different ODBC connection. We are trying to prevent the user from accidentally scanning in the same Order Number twice and we need to do this independent of the shipping software. Here is the database structure:

RETRIEVAL DB FIELDS (ODBC1): OrderNumber, Name, Address1, Address2, City, State, Zip

WRITEBACK DB FIELDS (ODBC2): OrderNumber, TrackingNumber, Weight, ShippingPrice

We would like to avoid using the same file for both Retrieval and Writeback as this would trigger a host of other changes to the backend. Is there a way in SQL to scan the Order Number from the Retrieval DB and have it check to see if that Order Number has been written to the Writeback DB before it populates the shipping fields?

Thank you.
ctsuhakoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
If I understand you correctly Retrieval and writeback are tables (not databases), so you can try the below

insert Writeback
select a.OrderNumber, blah .... --please complete
from retrieval a
left join writeback b on a.OrderNumber = b.OrderNumber
where b.OrderNumber is null and a.OrderNumber = .... -- here goes the current order you're working on
0
LowfatspreadCommented:
please confirm what you mean by "database file"

a database consists of many tables...

you can have many separate databases...

do your ODBC connections use the same database server...

Is there a way in SQL to scan the Order Number from the Retrieval DB and have it check to see if that Order Number has been written to the Writeback DB before it populates the shipping fields?

not easily if you are using third party software... potentially you could add a TRIGGER to the database to STOP a Second
attempt to add the data to the "writeback DB" but that wouldn't produce a "clean / meaningful user warning message"

you need to explain the system design in more detail... for instance what is the primary key of the "writeback db"...
(why isn't it the order number... <to allow for split shipment of orders???>)
could you add a Unique constraint to the order no on the "writeback db" (again split shipments etc ...)

without a fuller description of the business processing and requirements of your system its very difficult to know what to propose...

please also note that your software license may impede what you are actually allowed to do to the system...

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ctsuhakoAuthor Commented:

Thank you for the replies.
Yes, they are tables. Sorry for the confusion.
Previously, there were two ODBC connections, one for each table. I have consolidated that into one ODBC connection and use that one connection to access both the Retrieval and Writeback tables. We initially thought a single connection would cause some speed issues, but that doesn't seem to be the case.
The primary key to the Writeback Table is the Order Number and it is also the Primary Key for the Retrieval Table.
The software is from Endicia and they don't seem to have a problem with us changing the SQL (however, they have not been a great help in actually pointing us in the right direction).
I was hoping to add a field to the Writeback Table to mark the order as shipped and then have that table checked before populating the fields from the Retrieval Table.

Scan order number---Retrieve data from Retrieval Table---Print label--Write data to Writeback Table (including Shipped flag).
Scan order number---Check Writeback Table for Order Number and Shipped flag---If not present, Retrieve data from Retrieval Table---Print label--Write data to Writeback Table (including Shipped flag).

Another possible scenario would be to just check if the Order Number is present in the Writeback Table. If it is there, then the order has been Shipped.
0
LowfatspreadCommented:
if order number is the primary key then you cant insert more than 1 row with it into the writeback table...

what actually is the problem?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.