Creating a view for updating

I have the following view that I'm creating in a Pervasive database

create view UPS_TRACKING as
SELECT NUMBER,PO_NO AS UPS_TRACK FROM SALES_HISTORY_HEADR
UNION ALL
SELECT NUMBER,REF_NO AS UPS_TRACK FROM PURCHASE_ORDER_HEADR

Open in new window


When I do the following update I get "View cannot be updated"

update UPS_TRACKING SET UPS_TRACK = 'UPS Tracking Number'
where number = '0000100598'

Open in new window


When I create the view without the UNION ALL then it works to update it

create view UPS_TRACKING as
SELECT NUMBER,PO_NO AS UPS_TRACK FROM SALES_HISTORY_HEADR

Open in new window


Any ideas how I can make this work?
LVL 1
GerhardpetAsked:
Who is Participating?
 
Bill BachConnect With a Mentor PresidentCommented:
A simple SP would likely work:

CREATE PROCEDURE UpdateTrack(:TrackNo VARCHAR(30))
AS
BEGIN
    UPDATE SALES_HISTORY_HEADR SET PO_NO = :TrackNo WHERE Number = '0000100598';
    UPDATE PURCHASE_ORDER_HEADR SET REF_NO = :TrackNo WHERE Number = '0000100598';
END;

Open in new window

You may need to alter the data type of the input variable, based on the exact data type of the two fields you are trying to edit.  If the two fields are different types, then you should use the CAST() function to perform an explicit conversion.

Call the function like this:  CALL UpdateTrack('1Z84736352878729');
0
 
Bill BachPresidentCommented:
Pervasive does not support updating a view like this.  From the current (PSQLv11) manual:
The UPDATE statement can update only a single table at a time.
0
 
Bill BachPresidentCommented:
The only way to get this to work would be to write a simple stored procedure that checks to see which table should be updated procedurally, then call the appropriate SQL statement to do the update itself.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
GerhardpetAuthor Commented:
The problem is that I'm on an Sage BusinessVision using the btrieve connection. As far as I know when using a btrieve connection I can't use a stored procedure.

Is that correct?
0
 
Bill BachPresidentCommented:
Which ODBC Driver are you using?  I am not as familiar with BusinessVision, so I am not sure of their limitations.
0
 
GerhardpetAuthor Commented:
I'm using DDF's and then using the Pervasive ODBC Client Interface driver

Is that what you mean?
0
 
Bill BachPresidentCommented:
Yes. If you are using DDFs and the Pervasive ODBC DRiver, then you are definitely using the PSQL "SQL" engine. You should be able to write a short stored procedure to do this.
0
 
GerhardpetAuthor Commented:
I remember that at one point I tried to create a trigger which did not work because of the btrieve connection

So then stored procedure should not be a problem?
0
 
Bill BachPresidentCommented:
Correct.  Triggers are not yet "universal" -- if you create a SQL trigger, then it prevents that same Btrieve operation from functioning.  However, SP's are completely running in SQL, and work just fine.
0
 
GerhardpetAuthor Commented:
I'm integrating with UPS Worldship. They require an ODBC connection to my database and I don't know if they can execute SP's when they write back to PSQL. I have asked them and waiting for an answer.

If they can what would the store procedure look like? I need to have them write the UPS tracking number to the PO table if it is a PO number and SO table if it is a invoice number

I have some experience in SQL but have never written a SP
0
 
GerhardpetAuthor Commented:
I will talk to UPS to see if they can call for a SP and it that works I will give your suggestion a try.

I'll post back once I know. Thanks
0
 
GerhardpetAuthor Commented:
Thanks for your help on this. The stored procedure will work.
0
All Courses

From novice to tech pro — start learning today.