Toad World® Forums

Filters not applied to custon Query

Good Afternoon TOAD team,

 I'm sure this used to work; but Saturday at home on TOAD 13.0 it failed.  I exported my setting and imported them into TOAD 13.2.   I removed table alias, when I thought that may be the issue; but no change except making the code bigger.   All I did was join another table in my custom query to use a related table, now the filters do not apply.

 With the default query the custom filters work, and the filter updates the code in the query window.  When I pick the custom query, the filters no longer make any change.  The query stays the same as it was, as does the data displayed; the only change is the check box location on custom filters, even if I add a new custom filter.  All that appears to work is changing the order, but that is not shown in the Query code window.

Default Query:
SELECT
ROWID, AUTHU_ID, USERID, USERID_OID,
AUTHASR_ID, ACCOUNTLOCKDATE, AUTHCHANGE,
AGENCY_ID, INSERTDATE, INSERTUSERID,
UPDATEDATE, UPDATEUSERID, RID
FROM EGRANTS.AUTHUSER

Custom Query:
-- With why changed

SELECT AUTHACCOUNTSTATUSREASONREF.REASON,
AUTHUSER.AUTHU_ID,
AUTHUSER.USERID,
AUTHUSER.USERID_OID,
AUTHUSER.AUTHASR_ID,
AUTHUSER.ACCOUNTLOCKDATE,
AUTHUSER.AUTHCHANGE,
AUTHUSER.AGENCY_ID,
AUTHUSER.INSERTDATE,
AUTHUSER.INSERTUSERID,
AUTHUSER.UPDATEDATE,
AUTHUSER.UPDATEUSERID,
AUTHUSER.RID,
AUTHACCOUNTSTATUSREASONREF.AUTHASRR_ID,
AUTHACCOUNTSTATUSREASONREF.REASON,
AUTHACCOUNTSTATUSREASONREF.REASON_DESC
FROM EGRANTS.AUTHUSER
LEFT OUTER JOIN EGRANTS.AUTHACCOUNTSTATUSREASONREF
ON AUTHACCOUNTSTATUSREASONREF.AUTHASRR_ID = AUTHUSER.RID

Larry

Sorry about the formatting; I forgotten how it happened, but here is without a custom window:

I'm sure this used to work; but Saturday at home on TOAD 13.0 it failed. I exported my setting and imported them into TOAD 13.2. I removed table alias, when I thought that may be the issue; but no change except making the code bigger. All I did was join another table in my custom query to use a related table, now the filters do not apply.

With the default query the custom filters work, and the filter updates the code in the query window. When I pick the custom query, the filters no longer make any change. The query stays the same as it was, as does the data displayed; the only change is the check box location on custom filters, even if I add a new custom filter. All that appears to work is changing the order, but that is not shown in the Query code window.

Larry

Hi Larry,

I am not 100% sure, but it sounds like you are in SB-Tables (or SB-Views) on the RHS Data tab, and then changing the query with the "view/edit query" button, and your saved filters from the 1st toolbar button aren't getting applied.

If so, try changing your query so it uses an Oracle join (not ANSI join). We have some code in there that looks for the "where", "order by", and "partition" keywords because that's how the filter/sort dialog builds the queries when you add a filter.

Good Morning John,

 Thank you for taking a look!

 I changed the query to an Oracle join as follows:

-- With why changed
SELECT AUTHACCOUNTSTATUSREASONREF.REASON,
AUTHUSER.AUTHU_ID,
AUTHUSER.USERID,
AUTHUSER.USERID_OID,
AUTHUSER.AUTHASR_ID,
AUTHUSER.ACCOUNTLOCKDATE,
AUTHUSER.AUTHCHANGE,
AUTHUSER.AGENCY_ID,
AUTHUSER.INSERTDATE,
AUTHUSER.INSERTUSERID,
AUTHUSER.UPDATEDATE,
AUTHUSER.UPDATEUSERID,
AUTHUSER.RID,
AUTHACCOUNTSTATUSREASONREF.AUTHASRR_ID,
AUTHACCOUNTSTATUSREASONREF.REASON,
AUTHACCOUNTSTATUSREASONREF.REASON_DESC
FROM EGRANTS.AUTHUSER, EGRANTS.AUTHACCOUNTSTATUSREASONREF
WHERE AUTHACCOUNTSTATUSREASONREF.AUTHASRR_ID = AUTHUSER.RID

When I apply my "Me filter", TOAD replaces the where clause for the join and 

removes the second table which causes an error since the second table's fields
are still in the columns to be returned:

-- With why changed
SELECT AUTHACCOUNTSTATUSREASONREF.REASON,
AUTHUSER.AUTHU_ID,
AUTHUSER.USERID,
AUTHUSER.USERID_OID,
AUTHUSER.AUTHASR_ID,
AUTHUSER.ACCOUNTLOCKDATE,
AUTHUSER.AUTHCHANGE,
AUTHUSER.AGENCY_ID,
AUTHUSER.INSERTDATE,
AUTHUSER.INSERTUSERID,
AUTHUSER.UPDATEDATE,
AUTHUSER.UPDATEUSERID,
AUTHUSER.RID,
AUTHACCOUNTSTATUSREASONREF.AUTHASRR_ID,
AUTHACCOUNTSTATUSREASONREF.REASON,
AUTHACCOUNTSTATUSREASONREF.REASON_DESC
FROM EGRANTS.AUTHUSER
WHERE
USERID = 'wickliffe.larry@dol.gov'

Larry

Hm, yeah. I see. I should have realized that before giving my prior response.

The filter/sort dialog (and named filters) swaps out "where" and "order by" clauses. I am not expecting part of the where clause to be in support of a join. I am just expecting the query that Toad created for the data tab, with no join and no "where" clause.

I'll log it. Maybe I can get it to work with an ansi join after all, then we can go back to adding/removing a "where" clause. In the meantime, my only suggestion is making a view and look at that instead of this table.

I just discovered that if you don't mind removing rowid, you can push the joined query to a WITH clause, and then filters start working.

with data as 
  (SELECT 
    e.EMPNO, e.ENAME, e.JOB, 
    e.MGR, e.HIREDATE, e.SAL, 
    e.COMM, e.DEPTNO, d.dname
 FROM JDORLON.EMP e
 LEFT OUTER JOIN dept d
 ON d.DEPTNO = e.DEPTNO)
select * 
from data

Good Morning John,

With your work-around, filters work fine!   :smile:

 I like your work around, however the SELECT *  created an Oracle ORA-00918

category:41 error; the revised fixed query follows:

-- With why changed fixed
with data as
(SELECT AUTHACCOUNTSTATUSREASONREF.REASON,
AUTHUSER.AUTHU_ID,
AUTHUSER.USERID,
AUTHUSER.USERID_OID,
AUTHUSER.AUTHASR_ID,
AUTHUSER.ACCOUNTLOCKDATE,
AUTHUSER.AUTHCHANGE,
AUTHUSER.AGENCY_ID,
AUTHUSER.INSERTDATE,
AUTHUSER.INSERTUSERID,
AUTHUSER.UPDATEDATE,
AUTHUSER.UPDATEUSERID,
AUTHUSER.RID,
AUTHACCOUNTSTATUSREASONREF.AUTHASRR_ID,
--AUTHACCOUNTSTATUSREASONREF.REASON,
AUTHACCOUNTSTATUSREASONREF.REASON_DESC
FROM EGRANTS.AUTHUSER
LEFT OUTER JOIN EGRANTS.AUTHACCOUNTSTATUSREASONREF
ON AUTHACCOUNTSTATUSREASONREF.AUTHASRR_ID = AUTHUSER.RID)
select REASON,
AUTHU_ID,
USERID,
USERID_OID,
AUTHASR_ID,
ACCOUNTLOCKDATE,
AUTHCHANGE,
AGENCY_ID,
INSERTDATE,
INSERTUSERID,
UPDATEDATE,
UPDATEUSERID,
RID,
AUTHASRR_ID,
--REASON_1,
REASON_DESC
from data

Larry

Oracle ORA-00918

Even with one of the "REASON" columns removed? Must be something about the columns being selected. Select * worked for me in the emp/dept example.