Sybase11 (spurious) GROUP BY !!!

[beltion]

I am converting a Sybase 11 application to an Oracle8 application. There are a number of queries similar to the one below. The columns of in the Group By clause do not match the columns in the Select clause. In Oracle, this is illegal. In fact, in Sybase I have read that it is allowed but produces "spurious" results. Can someone please tell me:
a) whether those "spurious results are at least consistent for a given query
b) what the "logical" result should be whenever a query similar to the one below is run; i.e., what values are simply repeated(?) and which are actually rolled up on. Please also explain (or point to someplace that explains) what the effect of nulls would be.

Thanks much in advance.

SELECT
it_prod_db.dbo.APP_RISK_YW.S_YEAR,
it_prod_db.dbo.APP_RISK_YW.S_MONTH,
0,
it_prod_db.dbo.APP_RISK_YW.APPSYS_NAME,
it_prod_db.dbo.APP_RISK_YW.APSS_NAME,
it_prod_db.dbo.APP_RISK_YW.CLS_NAME,
it_prod_db.dbo.APP_RISK_YW.OBJ_NAME,
it_prod_db.dbo.APP_RISK_YW.MO_NAME,
it_prod_db.dbo.APP_RISK_YW.RISKLVL_ID,
it_prod_db.dbo.APP_RISK_YW.RISK_WEIGHT,
it_prod_db.dbo.APP_RISK_YW.CAT_NAME,
it_prod_db.dbo.APP_RISK_YW.THRESHOP_NAME,
it_prod_db.dbo.APP_RISK_YW.THRESHOLD_VAL,
it_prod_db.dbo.APP_RISK_YW.UNIT_NAME,
it_prod_db.dbo.APP_RISK_YW.USED_PCT,
max(it_prod_db.dbo.APP_RISK_YW.RISKLVL_ID),
it_prod_db.dbo.APP_RISK_YW.S_WEEK
FROM it_prod_db.dbo.APP_RISK_YW
GROUP BY
it_prod_db.dbo.APP_RISK_YW.S_YEAR,
it_prod_db.dbo.APP_RISK_YW.S_MONTH,
it_prod_db.dbo.APP_RISK_YW.S_WEEK,
it_prod_db.dbo.APP_RISK_YW.APPSYS_NAME,
it_prod_db.dbo.APP_RISK_YW.APSS_NAME,
it_prod_db.dbo.APP_RISK_YW.CLS_NAME,
it_prod_db.dbo.APP_RISK_YW.OBJ_NAME
ORDER BY
it_prod_db.dbo.APP_RISK_YW.S_YEAR ASC,
it_prod_db.dbo.APP_RISK_YW.S_MONTH ASC,
it_prod_db.dbo.APP_RISK_YW.S_WEEK ASC,
it_prod_db.dbo.APP_RISK_YW.APPSYS_NAME ASC,
it_prod_db.dbo.APP_RISK_YW.APSS_NAME ASC,
it_prod_db.dbo.APP_RISK_YW.CLS_NAME ASC,
it_prod_db.dbo.APP_RISK_YW.OBJ_NAME ASC,
it_prod_db.dbo.APP_RISK_YW.MO_NAME ASC

 

[somnath74]

Hi beltion ,
The only place they talk about spurious group by in Sybase is when you use group by without using an aggregate function.It might or might not group by and is not dependable.To check out whether your query is giving a warning set fipsflagger on before running the query.As per Sybase 11.5 ASE you can select a cloumn which is not present in the group by clause.I have personally never faced a problem with that.
Hope this helps

Somnath