7.7.04 -- This code throws an error coming out of SQL*Server:

TABLE FILE WEEKLY_SUMM
SUM AVG_WAIT_TEXT
BY UNIT_NAME AS ''
ACROSS SERVICE_WEEK_END_DT/MDY AS ''
WHERE WEEKLY_SUMM.SEG01.OFFICE_SIZE EQ 'Large Offices';
WHERE DATEDIF(SERVICE_WEEK_END_DT, '&YYMD', 'D') LE 120
END


From SQL*Server --

(FOC1400) SQLCODE IS 169 (HEX: 000000A9) XOPEN: 42000
: Microsoft SQL Server Native Client 10.0: [42000] A column has been speci
: fied more than once in the order by list. Columns in the order by list m
: ust be unique. [42000] Statement(s) could not be prepared. [] Deferred p
: repare could not be completed.


This code does not throw the error:

DEFINE FILE WEEKLY_SUMM
SERVICE_WEEK_END_DT_MDY/MDY = SERVICE_WEEK_END_DT;
END
TABLE FILE WEEKLY_SUMM
SUM AVG_WAIT_TEXT
BY UNIT_NAME AS ''
ACROSS SERVICE_WEEK_END_DT_MDY AS ''
WHERE WEEKLY_SUMM.SEG01.OFFICE_SIZE EQ 'Medium Offices';
WHERE DATEDIF(SERVICE_WEEK_END_DT, '&YYMD', 'D') LE 120
END

I'll almost assuredly forget this and come to the forums to look it up, so I may as well put it here now so I can find it quickly later.

J.

Hi John,

Adding a format to a sort column never used to supported (I think!) so I'm surprised that this runs through to the SQL parsing stage.

T

It ran in 7.7.03M on the same server. Our upgrade appears to have invalidated the practice.

I'll be honest -- the older versions were far more forgiving than they should have been and it wouldn't surprise me if this is plugging a hole. But it's an odd error to get for the condition, so I wanted to get it up where it could be found with a search.

J.

The error is not odd because when you use the option to change the format of a column, WF creates a COMPUTE so in fact you have 2 columns, the original and the formatted one.

What IS ODD is that this passed the WF parser!

As you said, WF is quite forgiving, in fact too much so, and when a "hole gets plugged", customers become vocal...