Re: SQL question

On 27-Jun-2016 09:18 -0500, Bill Howie wrote:

Having an issue with an SQL statement. This is my first attempt at
using the UNION statement but I don't think that's where the problem
is. The error I get is:

"Column OHDTCR or expression in SELECT list not valid"

That is the msg SQL0122 aka sqlcode=-122

Here's the thing.....where this OHDTCR field is being used seemed to
work fine before I added the UNION clause, so I'm not sure how the
adding of it made it become a problem. OHDTCR is a numeric field, by
the way. Any thoughts from the group would be MOST appreciated.
Thanks!

<<SNIPped the query; will give a simplified rendition>>

A simplified version of the original query might be represented as the following query [that removes joins and other selection, as well as ordering, per not being relevant for exhibiting the error, though refers to a column from a joined table as if the field is in the table-reference, and] that is composed to obtain a count of the non-null values of WXIDFM, across each YearsCreated value; for later reference, we can call that query Q1:

select
substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4) as yearcreated
, count(wxidfm) as FamilyTot
from oe_test
group by
substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4)

What was effectively done in the failing UNION query, was to repeat the above query on either side of the UNION, but having first dropped the GROUP BY clause; for later reference, we can call this query Q1M:

select
substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4) as yearcreated
, count(wxidfm) as FamilyTot
from oe_test

Note: the above query is no longer valid, standalone, and fails exactly as the UNION query form the OP failed; i.e. with the SQL0122, for lack of a GROUP BY of the expression in the select-list.

So the UNION query was essentially written as the following, whereby the GROUP BY was coded for the second subquery, but not for the first subquery:

Q1M
union
Q1

Or the UNION query was perceived to be written as the following, whereby the GROUP BY was intended to apply to the UNION result:

Q1M
union
Q1M
group by
substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4)

Note: the above UNION queries are not valid, failing with the sql0122, because the first subquery of the UNION [i.e. the query Q1M] is incomplete for lack of the GROUP BY; each subquery has an aggregate COUNT function and another expression in the select-list, for which the GROUP BY is required.

FWiW: The following syntax would be valid, but not necessarily what is desired:

Q1
union
Q1

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.?