Group by is part of the sub-Select and must be secified with each
sub-select.
Order By is outside the sub-Select and can be specified at the and after all
sub-Selects.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Bill
Howie
Gesendet: Monday, 27.6 2016 16:19
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL question
Hello all,
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!
Bill
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','JSP
C','JTCAS') and ohbrch <> 16
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','JSP
C','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 order by
ohbrch, wxidfm, substr(digits(dec(19000000+ohdtcr,8,0)),1,4),
substr(digits(dec(19000000+ohdtcr,8,0)),5,2)
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
