Re: SQL question

On 27-Jun-2016 12:13 -0500, John Yeung wrote:

On Mon, Jun 27, 2016 at 12:34 PM, CRPence wrote:

<<SNIP>>
Note: What is the desired effect, can not be divined solely from
the query shown in the OP. With some example data and the desired
effect presented as a report\result-set, the intention might be
made clear; an example with some data for the above simplified
query Q1 might even be sufficient to elucidate, offering some DDL
like "create table oe_test (ohdtcr int, ohbrch int, wxidfm char)"
as the initial setup.?

Well, the SELECT portions of both sides of the UNION are identical,
except for the libraries. In one, he's got

from bapdata.oeohdp
join bapdata.oeodtp on ohbrch =

whereas in the other, it's

from baphst.oeohdp
join baphst.oeodtp on ohbrch =

So, while we can't be *sure*, it definitely seems to me he's using
UNION in an attempt to pull in both the "live" and "historical" data
for the files in question, after already having successfully pulled
the data for just one or the other, without UNION.

OK, thanks, I did not notice that, even having used a side-by-side visual comparison of the /beautified/ queries from either side of the UNION; just another conspicuous hint that my eyesight is failing. So, I should have represented the effect as Q1 UNION Q2, which is only sensible, given Q1 UNION Q1 is the /same as/ requesting just Q1; I had incorrectly deduced that the UNION query was contrived, having been derived from one alluded-to originally-functional grouping-query, such that the OP was concerned only with the failing syntax rather than what would be the result set.

Regardless, a presentation of DDL and data [ideally in a greatly simplified scenario, e.g. as I suggested] along with the expected result\report using that test-case setup, is generally valuable to help explain better, what is the intention. Just knowing that some of the row-data comes from a history-table does not fully elucidate the intentions. Essentially, although a reader can explain that the query is an /incorrectly coded query/, indeed as the error sql0122 had diagnosed, and although that same reader can suggest modifications to avoid the error, that reader can not know intuitively what the OP intended their query to effect, nor explain what would suffice as a /correctly coded query/ to achieve that effect. Does the OP want for example, just Q1 UNION Q2 [as UNION of two aggregate queries; seems unlikely per apparent lack of any field\expression distinguishing current data vs historical data], or perhaps do they want an aggregate query performed against the UNIONed results of both the detail rows of Q1 and the details rows of Q2 [such that neither Q1 nor Q2 should be composed as a summary query; I suspect, probable], or perhaps something else even less easily guessed?