Why would a sql query with an orderby statement produce a different sort order than using a proc sort and a by statement identical to the order by sql statement?
I am using the following procedure to read 3 different teradata tables (same structure just different tables).
create View HISTORY_PART_&i._Vw as
select PROF_SEGM_ID
,MKT_SEGM_IDFR
,CMDY_LINE_ID
,BUS_GRP_ID
,BUS_CATG_CODE
,ORIG_PROF_REG
,DEST_PROF_REG
,MGRL_CAR_KIND
,F990_RPTG_DT_YRMO as yrmo
,sum(TOTL_NET_REV) as rev
,sum(TOTL_CWT/20)as tons
,sum(CAR_CNT) as units
from prodvp20.&TABLE_NAME
where f990_rptg_dt_yrmo Between &&Start_YRMO&i and &&END_YRMO&i
GROUP by BUS_GRP_ID
,CMDY_LINE_ID
,PROF_SEGM_ID
,MKT_SEGM_IDFR
,BUS_CATG_CODE
,ORIG_PROF_REG
,DEST_PROF_REG
,MGRL_CAR_KIND
,F990_RPTG_DT_YRMO
ORDER by PROF_SEGM_ID
,BUS_GRP_ID
,CMDY_LINE_ID
,MKT_SEGM_IDFR
,ORIG_PROF_REG
,DEST_PROF_REG
,MGRL_CAR_KIND
,BUS_CATG_CODE
,F990_RPTG_DT_YRMO;
I then interleave the data sets with in a data step as follows
Data all;
set HISTORY_PART_1_Vw
HISTORY_PART_2_Vw
HISTORY_PART_3_Vw ;
by PROF_SEGM_ID BUS_GRP_ID CMDY_LINE_ID MKT_SEGM_IDFR
ORIG_PROF_REG DEST_PROF_REG MGRL_CAR_KIND
BUS_CATG_CODE ;
ERROR: BY variables are not properly sorted on data set WORK.HISTORY_PART_2_VW.
However, if I sort the results from the sql queries before I do the data step. The order changes (from the sql query with order by statement) and the by statement in the data step does not produce an error.
Adding a sort after the sql with an order by seems is inefficient especially since each of these data sets are quite large.
I should add that the sql statement (with order by) has been in production for the past 18months with no problems, but the data today had some bad observations where MKT_SEGM_IDFR = C148l and C148L and the order in the order by was different than the order when I sorted the query so the interleave step failed.
Any thoughts? Is there something that I am missing here.
Thanks