Category: Query

Changing Records in Queries

This isn’t best practice, but I found a way to switch out a record in a query without redoing the criteria. Here’s the situation: I have a query in which the row-level security is causing problems. It is slowing down the query and also eliminating future-dated rows from the results. So, I created a view of the table which would select all of the rows from the original table. It was basically a copy with the query security view removed. The next challenge was to change the query to use that new record. If I remove the old record and add the new, I would have to redo the whole complicated query just about.

So, I found that I could update two records in the database:

UPDATE PSQRYFIELD
SET RECNAME = '<new record name>'
WHERE QRYNAME = '<query name>'
AND RECNAME = '<old record name>';

UPDATE PSQRYRECORD
SET RECNAME = '<new record name>'
WHERE QRYNAME = '<query name>'
AND RECNAME = '<old record name>';

After that, I found that I needed to open the query online in query manager, make a small change, and save it. After that, it worked fine with no problems.

Warning: use at your own risk. This isn’t the intended way to edit queries.