Is this actually the query you're running?
This should result in a syntax error, because ORDER BY must be after PARTITON BY?
And if you submit that "remaining window" without final ORDER BY the result set will be sorted ascending, because it's the same result as a "cumulative window" with reversed sort and the optimizer always applies that logic.
Dieter
Dieter
Try amending the query to move the rolling window order to be after the partition statement and add and order by at the end of the query for the display order - something like:
SELECT
department_number AS Dept
,emp_name AS Emp
,salary_amount AS Sal
,AVE(Sal)OVER(
PARTITION BY Dept
ORDER BY Sal DESC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING
)
WHERE Dept IN (1360,4080,8496)
FROM RTWDEV_WORK.mb_employee
order by dept,sal desc;
As shown by martinbarrow, this is the accepted solution.
Hi All,
While trying to import date into a coloumn,the following error has occured.plz look into this n help us.
Error:*** Growing buffer to 65473
*** Failure 2673 The source parcel length does not match data that was defi
ned.
The BTEQ Import script used is below:
.LOGON TDDEV/usr,pwd;
CREATE VOLATILE TABLE ARCH_WEEK
(Week Varchar(100) NOT NULL)
UNIQUE PRIMARY INDEX (Week)
ON COMMIT PRESERVE ROWS
;
.IF ERRORCODE > 0 THEN .QUIT ERRORCODE;
CREATE VOLATILE TABLE ARCH_ID
(ID INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (ID)
ON COMMIT PRESERVE ROWS
;
.IF ERRORCODE > 0 THEN .QUIT ERRORCODE;
.IMPORT report FILE = C:\DATA\IMPORT.TXT
.REPEAT 2;
USING (WEEK Varchar(100))
INSERT INTO ARCH_WEEK(WEEK)
VALUES (:WEEK);
INSERT INTO ARCH_ID(ID)
SELECT
DISTINCT ID
FROM DB3.Purchase_details
WHERE PUR_DATE >= (Sel min(week)(date,format 'YYYY-MM-DD') from ARCH_WEEK)
AND PUR_DATE <= (Sel max(week)(date,format 'YYYY-MM-DD') from ARCH_WEEK)
;
.IF ERRORCODE > 0 THEN .QUIT ERRORCODE;
INSERT INTO DB1.Revenue_details
SELECT *
FROM DB3.Revenue_details
WHERE ID IN (SELECT ID FROM ARCH_ID)
;
.IF ERRORCODE > 0 THEN .QUIT ERRORCODE;
INSERT INTO DB1.sales_income
SELECT *
FROM DB3.sales_income
WHERE ID IN (SELECT ID FROM ARCH_ID)
;
.IF ERRORCODE > 0 THEN .QUIT ERRORCODE;
.QUIT 0;
And the Import file has below records:
2010-11-01
2010-11-02
Thanks in Advance.
RAMS.
Using the T12 express demo DB "retail" I was looking at how the "Window Remaining Function" works. I was using the following query:
SELECT
deptNo AS Dept
,name AS Emp
,salary AS Sal
,AVE(Sal)OVER(
ORDER BY Sal DESC
PARTITION BY Dept
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING
)
WHERE Dept IN (1360,4080,8496)
FROM retail.employee;
and got the following dataset:
Dept Emp Sal Remaining Avg(Sal)
1360 Jim Sink 80548.54 80548.54
1360 Meg O'Connel 78069.83 79309.19
1360 Nathan Mautz 60611.6 73076.66
1360 Nona Balk 59682.22 69728.05
1360 Yoseph Carro 44049.44 64592.33
4080 Sheri Gordon 85847.71 85847.71
4080 Lena Caciopp 42555.92 64201.82
4080 Paul Prost 27842.59 52082.07
8496 Naresj Patel 100655.56 100655.56
8496 Deanra Eno 74164.16 87409.86
8496 Frank Olsen 53409.15 76076.29
Strange though, I was thinking that the result would be:
64592.33,60603.27, 54781.09, 51865.83, 44049.44 respectively for department 1360.
eg:
(80548.54+78069.83+60611.6+59682.22+44049.44)/ 5=64592.33
(78069.83+60611.6+59682.22+44049.44)/4=60603.27
(60611.6+59682.22+44049.44)/3=54781.09
Sounds silly to say this but the current derivation of the above seems er... broken? As the actual numbers created by the query are:
(80548.54)/1=80548.54
(80548.54+78069.83)/2=79309.19
(80548.54+78069.83+60611.6)/3=73076.66
This seems not in keeping with the "look forward window" concept. Is Teradata broken?