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"

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!

select
substr(digits(dec(19000000+ohdtcr, 8, 0)), 5, 2) as monthcreated
, substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4) as yearcreated
, ohbrch
, wxidfm
, count(wxidfm) as FamilyTot
from bapdata.oeohdp
join bapdata.oeodtp
on ohbrch = odbrch
and ohordr = odordr
join bapdata.wnfitp
on oditem = wxitem
where ohorty = 'W'
and ohstat = ' '
and odstat = ' '
and ( substring(odfl15, 4, 1) = 'Y'
or ohordr > 1000000 )
and odprgp = 'REG4'
and wxidfm in ('JAWN','JBAY','JCAS','JDHG','JPIC'
,'JSHG','JSLD','JSPC','JTCAS' )
and ohbrch <> 16

group by
substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4)
, substr(digits(dec(19000000+ohdtcr, 8, 0)), 5, 2)
, ohbrch
, wxidfm
/* above group-by [re]inserted to make a functional query */

union
select
, substr(digits(dec(19000000+ohdtcr, 8, 0)), 5, 2) as monthcreated
, substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4) as yearcreated
, ohbrch
, wxidfm
, count(wxidfm) as FamilyTot
from baphst.oeohdp
join baphst.oeodtp
on ohbrch = odbrch
and ohordr = odordr
join bapdata.wnfitp
on oditem = wxitem
where ohorty = 'W'
and ohstat = ' '
and odstat = ' '
and ( substring(odfl15, 4, 1) = 'Y'
or ohordr > 1000000 )
and odprgp = 'REG4'
and wxidfm in ('JAWN','JBAY','JCAS','JDHG','JPIC'
,'JSHG','JSLD','JSPC','JTCAS' )
and ohbrch <> 16
group by
substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4)
, substr(digits(dec(19000000+ohdtcr, 8, 0)), 5, 2)
, ohbrch
, wxidfm

/* already a functional grouping-query */

order by
ohbrch
, wxidfm
, substr(digits(dec(19000000+ohdtcr, 8, 0)), 1, 4)
, substr(digits(dec(19000000+ohdtcr, 8, 0)), 5, 2)

But even with the group-by [re]inserted for the first subquery, I expect the overall UNION query will fail henceforth, instead, with msg SQL0214 rc1 aka sqlcode=-214. The solution for that remaining issue would be to change the ORDER BY to:

order by
ohbrch
, wxidfm
, 2 /* result of the second column-expression in select-list */
, 1 /* result of the first column-expression in select-list */

And although those changes would make a /syntactically correct/ query, the results produced may not be what is intended; and I presume would not be, per lack of data to distinguish the two aggregate result sets [and which per specification of UNION vs UNION ALL could, though unlikely, be pared to just one row]. As I'd already noted in my first reply, supplying a simplified table definition and test-data along with expected report\result can help to explain to a reader what is the intended effect of a query. Given sample DDL, and DML to populate the table(s) with sample data, along with expected output, the reader can propose how such a query could be composed, versus being able only to offer syntax changes to avoid an error, but revisions that may or may not assist in obtaining the desired result-set.