pervasive syntax to link files 1

[MacolaHelp]

I am trying to link imitmidx & iminvloc (one to many) & return selected fields from the file that I can update: specifically prod_cat in both files based on parameters in IMITMIDX.Item_desc_2. Been there? Done that? I got the joins done, but since the item_no is not unique in iminvloc, pervasive returns an error. Am I just being dense? Your help in advance is much appreciated.

 

[stravis]

Please provide the following information.

Version of Pervasive?
Data Access Methodology?
Application Being Used to Update Database?
If you are using code or SQL Statement, please provide.

Thanks,
Scott

 

[MacolaHelp]

Pervasive 2000i workstation engine & server engine: I'm doing it locally first to test & then want to replicate on clients' win2k server running 2000i sp4.

I'm just using the pervasive control center & executing an sql statement there. I've had good success updating single tables, such as make prod_cat = '233' where item_no between 'AAA' and 'ZZZ', but am struggling w/syntax for updating another file based on a value in a related table.

This syntax creates a return of all fields in iminvloc:
SELECT * FROM IMINVLOC_SQL LEFT OUTER JOIN IMITMIDX_SQL on IMINVLOC_SQL.Item_No = IMITMIDX_SQL.Item_No

However, when I try to just capture certain fields from itmimidx & iminvloc (item_desc_2, item_no, prod_cat, loc), I get a message that the records are not unique. This is true, since I have multiple records in iminvloc that contain the same item_no as in the imitmidx_sql. I tried to switch to right outer join w/o success. Pervasive documentation says they use SQL92 as the convention for syntax, but since I am not a real programmer, this is greek to me.

I'd rather not use access to do an update query on these files. My experience has been that the way access formats the fields & the way Macola wants them to look sometimes conflict & create data problems down the road. I have had much better success using the PCC or EM to update files provided I have been diligent enough to change all affected fields in the files when I change a value where other fields are dependent (such as chekcing backorderable if the item is set to PP/pull).

 

[kscheeler]

I would initially forget about the location file for a minute and update the prod cat in imitmidx. Then link the two tables and update the prod cat in iminvloc where the prod cat in iminvloc doesn't equal the prod cat in imitmidx.

Kevin Scheeler

 

[stravis]

As I'm sure you are aware, the SQL statement that you have are using is only going to retrieve records from the database, and not update them. The specific error message you are receiving is not because of duplicate information in a column, but is because you have two columns in your result set with the same name, therefore you must preface these columns with the name of the table. Your modified SQL statement would look like the following.

SELECT IMITMIDX_SQL.Item_no, IMITMIDX_SQL.Item_Desc_2, IMITMIDX_SQL.prod_cat, IMINVLOC_SQL.prod_cat FROM IMINVLOC_SQL LEFT OUTER JOIN IMITMIDX_SQL on IMINVLOC_SQL.Item_No = IMITMIDX_SQL.Item_No

You will need to modify the column list to return the information that you are looking for.

When you are ready to update the database I would suggest that you do as kscheeler suggested and start by updating the IMITMIDX_SQL table and then update the IMINVLOC_SQL table in a separate update. If you need any assistance with the syntax for those two update statements, please post the rules you will be using for the product category assignments.

Scott Travis
NEXO Systems, Inc.

 

[MacolaHelp]

Progress. The table name, column name was the key on the selection criteria. Yes, I would do 2 passes on the update queries & then run one to make sure that the prod_cat matches in both files. I typically do a select query first to make sure I have the correct records before switching to an update query. Too gun shy to try an update w/o testing first. Thanks for your help on this.