Re: Simple use of substr problem

On 05-Dec-2013 07:31 -0800, rob@xxxxxxxxx wrote:

On 05-Dec-2013 07:13 -0800, Hoteltravelfundotcom wrote:

How would you do that in SQL then?
I just want to total according to year and month

With stupiddate defined as a decimal 8.0 field:

With SQL:

with T1 as (
select substr(char(stupiddate),1,6) as yearmonth
from rob/htf)
select yearmonth, count(*) as nbrper
from t1
group by yearmonth
order by yearmonth

You could also do it this way in SQL:
select substr(char(stupiddate),1,6) as yearmonth,
count(*) as nbrper
from rob/htf
group by substr(char(stupiddate),1,6)
order by substr(char(stupiddate),1,6)

Not all utilization of Query/400 "Report Breaks" translate directly into the GROUP BY of the SQL.

Also the ORDER BY can utilize either the identifier specified in the select-list with the AS-identifier, or the effective column-number that reflects the position of the expression that should be utilized for the collating; i.e. either of the following ORDER BY would have sufficed in the second query above:

ORDER BY yearmonth

ORDER BY 1

And FWiW, when not including a blank after the comma for the arguments in the expressions, someone using a decimal separator of comma will get a syntax error if they try to copy\paste the given. They would have to reformat the query to make it functional. Having specified the expression as follows, would avoid that difficulty, making the example more generally usable without modification:

substr(char(stupiddate), 1, 6)

With Query/400:

Define Result Fields
Field Expression
STUPIDCHAR digits(stupiddate)
YEARMONTH substr(stupidchar,1,6)
Select Sort Fields
Sort
Prty A/D Field Text
1 A YEARMONTH substr(stupidchar,1,6)
Select Report Summary Functions
---Options--- Field Text
5 YEARMONTH substr(stupidchar,1,6)
Define Report Breaks
Break Sort
Level Prty Field Text
1 10 YEARMONTH substr(stupidchar,1,6)

That would mimic GROUP BY [but not necessarily other nuances]. However the "total according to year and month" might validly be desirable to appear in the report within other /report breaks/ rather than solely within "year and month" groups as the first and only /break/ in the data for which aggregates are calculated [and optionally presented in the report].