A SQL purist would point out that comparing a field to a value is not a join and doesn't belong in joins. The distinction between join and filter was made because some flavors of SQL may process a join in a different way than a test.

Allowing filtering by a field seems like a very appropriate use, though.

(Not being a SQL purist myself, of course, just filling in until he gets back.)

Back in the real world, I think adding value tests on the Joins tab would probably lead to user confusion. E.G. if you put CATEGORY = '' on the Joins tab, and CATEGORY = 'M' on the Filters tab. It'd be troublesome to check both places to understand all your criteria.

Some confusion will still happen by allowing field comparisons on the Filters tab but is unavoidable.

-- Bruce

Actually, comparing a field to a value can be a valid (and required) part of a join; see the SMR I referenced for an example. There is no other way to write that query so that the results are as desired. Filtering the value does not produce the same values and doesn't have the same semantics. Lacking this capability means that there are valid and desirable SQL statements that IQA cannot produce; it's a shortcoming of the tool, and if we want this tool to be the underlying tool for all system queries, reports, etc (which we do, as far as I know), then it's something we may need to address (whether in the 15.0.1 timeframe or not). Unfortunately, there's no way that IQA can predict whether a given condition needs to be a join or filter condition, so we have to allow the user to specify it if we want to support those queries.

Also, the join syntax IQA produces is ANSI standard SQL, which does allow joining that way.

Adding value tests on the Joins tab could be hidden unless you were in Advanced mode, if we felt that users are likely to be confused by the option. Alternately, we could (assuming we start allowing field-field comparisons on the Filters tab) put a checkbox (again, in advanced mode) indicating whether the condition is a join condition or a filter, although personally I feel that would be more confusing; join conditions should be on the source tab IMO.

Also, being 100% ANSI compatible isn't particularly pressing anyway, given that we explicitly only support SQL Server 2000 and 2005. In fact, some components (IBO for .Net for one) are using the native SQL Server providers, so we already are excluding other SQL dialects.

And just to be clear, I think that both field-value filters on the Source tab, and field-field filters on the Filters tab are necessary for full functionality.

I can't see SMR's I didn't submit, so I can't really see your example. Could you paste some of it here, or email me directly?

Clearly, ASI is committed to the MSSQL platform, so cross-dialect SQL support really is a moot point. As I tried to humorously point out, it's more a matter of theoretical purity than of a real benefit.

The perception issues are real, however, so I would hope that any solution would be clearly expressed both on screen and in documentation -- that old adage about never surprising the user.

-- Bruce

When you have two sources and you relate them with a Left Join and then add a filter on the right source of the join. The filter has to be placed with the Join on statment NOT the where clause..
when the filter is applied to the where clause it turns the join into an INNER JOIN

This has caused us alot of time and testing and there is no work around I can find and my staff are losing trust in the whole IQA experience, since their querys are not returning the correct rows

Here is the SQL statement that IQA produces

SELECT vBOCsContact.FirstName,
vBOCsContact.ID
FROM vBoCsContact
LEFT JOIN vBoCsAddress
ON vBoCsContact.ID = vBoCsAddress.ID
LEFT JOIN vBoCsAddress vBoCsAddress1
ON vBoCsContact.ID = vBoCsAddress1.ID
WHERE (vBoCsContact.MajorKey LIKE '202%'
AND vBoCsAddress1.AddressPurpose = 'WORK'
AND vBoCsAddress.AddressPurpose = 'HOME' )

-----
This is what is suppose to be
-----
SELECT vBOCsContact.FirstName,
vBOCsContact.ID
FROM vBoCsContact
LEFT JOIN vBoCsAddress
ON vBoCsContact.ID = vBoCsAddress.ID
And vBoCsAddress.AddressPurpose = 'HOME'
LEFT JOIN vBoCsAddress vBoCsAddress1
ON vBoCsContact.ID = vBoCsAddress1.ID
AND vBoCsAddress1.AddressPurpose = 'WORK'
WHERE (vBoCsContact.MajorKey LIKE '202%' )

Looking at it, I think it actually is possible to figure this out programmatically (without requiring input from the user as to whether this is a join filter or a where filter). Which would obviate the need to allow filter entry on the Sources tab, although I'm not 100% sure that that wouldn't still be useful.

This SMR definitely sounds like a case of "surprising the user".

Filtering on the outer side of an outer join can be tricky, though I blame some of that on the fact that outer joins are tricky to begin with. The filter-in-join syntax is the easiest, but that is also a fairly hard concept to teach. (We teach a "SQL Boot Camp" class that gets into outer joins and such, and they always give me that topic.)

It is sometimes possible to achieve the same thing in the "where" clause with creative use of isnull(), but that can also fall down depending on the situation.

I would lean toward having IQA recognize any filter on the outer side of an outer join and express it within the join clause automatically, rather than having the user try to understand and do the right thing.

This brings up the next hard thing: nested outer joins. Does IQA know how to handle "a outer join b outer join c" situations? I can't recall the real-life scenario where I hit that, but I could probably invent a scenario that demonstrates it.

-- Bruce

Obviously we're never going to arrive at a situation where we can provide perfect SQL syntax in all situations (at least, not without scrapping our current model and going to a bare text field...), but I think these are good suggestions.

It is sometimes possible to achieve the same thing in the "where" clause with creative use of isnull(), but that can also fall down depending on the situation.

Unfortunately that doesn't work in many cases because there's no way to control nesting of IQA filters, as far as I can tell. e.g. if you click the "Add Filter" button 3 times, there's no way to (Filter1 OR Filter2) AND (Filter3 OR Filter4), which would be the workaround for the query in this instance. Of course, we could provide the capability to organize the filters (possibly using a model like DevTrack does, or some other model).

I would lean toward having IQA recognize any filter on the outer side of an outer join and express it within the join clause automatically, rather than having the user try to understand and do the right thing.

That was the solution I came to as I was writing the post. :)

This brings up the next hard thing: nested outer joins. Does IQA know how to handle "a outer join b outer join c" situations? I can't recall the real-life scenario where I hit that, but I could probably invent a scenario that demonstrates it.

As far as I know, it just does a simple

SELECT ...
FROM Table1
LEFT JOIN Table2
  ON ...
LEFT JOIN Table3
  ON ...

and lets the SQL engine figure it out. In most cases this should (I think) work fine; where I expect it would break down is if you were to mix outer and inner joins, which should be rare. If you do know of any other situations where it results in incorrect results, please let me know -- I want to start cataloguing the situations in which IQA doesn't do the right thing, so that we can document that. If users know the limitations, they're less likely to be angry about them, plus we can start planning to resolve them if possible.

There is a simple way around this, thoug it is far from elegant and will process slower: Use a dual-layer query.

In other words, make one IQA query with the built in "Home" purpose filter, a second with a "Work" purpose filter and a third query that links these previous queries through left outer joins into Contact. It use ugly and time-consuming, but it will work.

 In several clients, we have built this functionality into a custom View and Object to avoid the whole IQA problem and speed up the results.

-K

Kevin Blouin - enSYNC Corp.

Your answer got me thinking about another aspect of these screens. I didn't want to muddy up my last post with this, so here's my second reply.

We're in the throes of our first big Opp^H^H^H Process Manager and IQA implementation, and we've already discovered an interesting wrinkle about IQA.

While it's great to think that most users will live in "basic" land and the advanced users and IT folks will be the only ones to use "advanced", that doesn't always play out in real life. We've found many cases where the client's specific business rules can only be expressed in Advanced mode. As a result, we may need to teach everyone who can create IQA queries how to use Advanced mode, just so they can do their basic work. At that point, nobody in the building has much reason to use Basic, and the distinction is lost.

Given this situation, "putting something on Advanced" doesn't mean that basic users won't run into it and have to be aware of the dangers.

I don't know that there's a good solution for this. Making "Basic", "Intermediate", "Advanced" may help, but who decides what is good enough for Intermediate? We may still have cases where less-trained Basic users need something only available from Advanced.

User/field-level security on the capabilities of IQA is another way, but very icky (that's a technical term).

This isn't a new problem just for IQA, either. We've had this situation arise in all versions of iMIS. A good example is the security levels in AR/Cash module. It's very common that to give someone the access they need to do their job, we have to grant them access to more than they need and tell them "don't touch that" to the other parts.

-- Bruce

Is it generally a situation where we could just reshuffle what shows up in Basic (i.e. I often find myself turning Advanced on just for Prompt ability), or is it very case-by-case?

There is a definite tradeoff between power and simplicity, unfortunately; I just feel like if IQA is going to do all the things we ask it to do, it's going to need these additional capabilities.

I think we can probably get closer than it is (prompt is a good one), but there will always be situations where it doesn't fit.

-- Bruce

Are there specific things other than Prompt that even Basic customers use consistently? An RSE or even just a list of them would help get the ball rolling.

We're going to be training a client on IQA very soon, and we also will be having an internal session to set up a collection of IQA queries for them. From those two experiences, I should be able to give you some specific feedback. It'll be a couple of weeks, though.

Ping me if you're ready to work on this and haven't heard back yet.

-- Bruce

Sounds good to me.

I would be very interested in seeing these queries. If you get this together, can I get a copy?

mroques@advsol.com