Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Location
    Brooklyn, NY
    Posts
    5

    Unanswered: Sybase (spurious) Group By

    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




  2. #2
    Join Date
    May 2002
    Posts
    11
    Thumb rule of SQL is:

    Select <col1>, <col2> .....<coln>, grouping functions
    from <table>
    group by <col1>, <col2> .....<coln>

    Whoever has written the SQL that you sent, (s)he have to go for basic level of SQL training.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •