Results 1 to 9 of 9

Thread: DB2 SQL Error

  1. #1
    Join Date
    Aug 2010
    Posts
    5

    Exclamation Unanswered: DB2 SQL Error

    Hi Everyone,

    I have tried multiple solutions but to no avail. I am trying to update someones code to start working again but I am getting a select/group by error.

    <code>
    select pftctr.PROFIT_CENTER_CD,
    terr.TERR_LVL4_CD, terr.TERR_LVL4_DE, terr.TERR_LVL5_CD, terr.TERR_LVL5_DE,
    acct.ACCOUNT_LVL1_CD, acct.ACCOUNT_LVL1_DE,
    acct.ACCOUNT_LVL2_CD, acct.ACCOUNT_LVL2_DE, acct.ACCOUNT_LVL3_CD, acct.ACCOUNT_LVL3_DE, acct.ACCOUNT_LVL4_CD, acct.ACCOUNT_LVL4_DE,
    acct.ACCOUNT_LVL5_CD, acct.ACCOUNT_LVL5_DE, acct.ACCOUNT_LVL6_CD, acct.ACCOUNT_LVL6_DE, acct.ACCOUNT_LVL7_CD, acct.ACCOUNT_LVL7_DE,
    fiscal.YEAR, fiscal.QUARTER, burden.BURDEN_DE,
    SUM(vcf.UNITS) AS QUANTITY, SUM(vcf.USD_AMOUNT) AS AMOUNT_GROUP, SUM(vcf.LOCAL_AMOUNT) AS AMOUNT_LOCAL, SUM(vcf.TRANS_AMOUNT) AS TRANS_AMOUNT
    from FP_WDSHE3.SHE_F_PL vcf,
    FP_WDSHE3.SHE_D_TERRITORY terr,
    FP_WDSHE3.SHE_D_ACCOUNT acct,
    FP_WDSHE3.SHE_D_FISCAL_CALENDAR fiscal,
    FP_WDSHE3.SHE_D_SCENARIO scenario,
    FP_WDSHE3.SHE_D_PROFIT_CENTER pftctr,
    FP_WDSHE3.SHE_D_BURDEN burden
    WHERE vcf.ACCOUNT_KEY = acct.ACCOUNT_KEY
    AND vcf.TERRITORY_KEY = terr.TERRITORY_KEY
    AND vcf.FISCAL_CAL_KEY = fiscal.FISCAL_CAL_KEY
    AND vcf.plan_YEAR = 2010
    AND fiscal.QUARTER in (1,2,3)
    AND vcf.SCENARIO_KEY = scenario.SCENARIO_KEY
    AND scenario.SCENARIO_CD = 'ACT'
    --AND scenario.VERSION_CD = 'SM1'
    AND vcf.PROFIT_CTR_KEY = pftctr.PROFIT_CTR_KEY
    AND vcf.BURDEN_KEY = burden.BURDEN_KEY
    AND burden.BURDEN_cd in ('UBD','BGE')
    GROUP BY pftctr.PROFIT_CENTER_CD,
    terr.TERR_LVL4_CD, terr.TERR_LVL4_DE, terr.TERR_LVL5_CD, terr.TERR_LVL5_DE,
    acct.ACCOUNT_LVL1_CD, acct.ACCOUNT_LVL1_DE,
    acct.ACCOUNT_LVL2_CD, acct.ACCOUNT_LVL2_DE, acct.ACCOUNT_LVL3_CD, acct.ACCOUNT_LVL3_DE, acct.ACCOUNT_LVL4_CD, acct.ACCOUNT_LVL4_DE,
    acct.ACCOUNT_LVL5_CD, acct.ACCOUNT_LVL5_DE, acct.ACCOUNT_LVL6_CD, acct.ACCOUNT_LVL6_DE, acct.ACCOUNT_LVL7_CD, acct.ACCOUNT_LVL7_DE,
    fiscal.YEAR, fiscal.QUARTER, burden.BURDEN_DE
    </code>

    The error says it is the BURDEN_DE, so I tried removing the BURDEN_DE field but then it says it has an issue with the QUARTER field.

    Any ideas on what I am missing here? Any advice is welcomed.

    Thanks in advance.
    mjstamper

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mjstamper View Post
    Any advice is welcomed.
    Post the error message.

  3. #3
    Join Date
    Aug 2010
    Posts
    5
    Sorry,

    SQL0119N
    SQLSTATE = 42803

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That's not a message; this looks like an error code.

  5. #5
    Join Date
    Aug 2010
    Posts
    5
    "An expression starting with "BURDEN_DE" specified in a SELECT clause, HAVING clause, or ORDER BY Clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY Clause with a column function and no GROUP BY clause is specified."

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You may want to try quoting the name of the year column in FP_WDSHE3.SHE_D_FISCAL_CALENDAR. YEAR is a reserved word in DB2 and the compiler may have trouble with that.

  7. #7
    Join Date
    Aug 2010
    Posts
    5
    Hi n_i,

    I tried that and I am still getting the same error.

  8. #8
    Join Date
    Aug 2010
    Posts
    5
    Figured it out.

    <code>
    select pftctr.PROFIT_CENTER_CD,
    terr.TERR_LVL4_CD, terr.TERR_LVL4_DE, terr.TERR_LVL5_CD, terr.TERR_LVL5_DE,
    acct.ACCOUNT_LVL1_CD, acct.ACCOUNT_LVL1_DE,
    acct.ACCOUNT_LVL2_CD, acct.ACCOUNT_LVL2_DE, acct.ACCOUNT_LVL3_CD, acct.ACCOUNT_LVL3_DE, acct.ACCOUNT_LVL4_CD, acct.ACCOUNT_LVL4_DE,
    acct.ACCOUNT_LVL5_CD, acct.ACCOUNT_LVL5_DE, acct.ACCOUNT_LVL6_CD, acct.ACCOUNT_LVL6_DE, acct.ACCOUNT_LVL7_CD, acct.ACCOUNT_LVL7_DE,
    fiscal.YEAR, fiscal.QUARTER, brdn.BURDEN_DE,
    SUM(vcf.UNITS), SUM(vcf.USD_AMOUNT), SUM(vcf.LOCAL_AMOUNT), SUM(vcf.TRANS_AMOUNT)

    from FP_WDSHE3.SHE_F_PL vcf,
    FP_WDSHE3.SHE_D_TERRITORY terr,
    FP_WDSHE3.SHE_D_ACCOUNT acct,
    FP_WDSHE3.SHE_D_FISCAL_CALENDAR fiscal,
    FP_WDSHE3.SHE_D_SCENARIO scenario,
    FP_WDSHE3.SHE_D_PROFIT_CENTER pftctr,
    FP_WDSHE3.SHE_D_BURDEN brdn
    WHERE vcf.ACCOUNT_KEY = acct.ACCOUNT_KEY
    AND vcf.TERRITORY_KEY = terr.TERRITORY_KEY
    AND vcf.FISCAL_CAL_KEY = fiscal.FISCAL_CAL_KEY
    AND vcf.SCENARIO_KEY = scenario.SCENARIO_KEY
    AND vcf.PROFIT_CTR_KEY = pftctr.PROFIT_CTR_KEY
    AND vcf.BURDEN_KEY = brdn.BURDEN_KEY
    AND vcf.PLAN_YEAR = 2010
    AND fiscal.QUARTER in (1,2,3)
    AND scenario.SCENARIO_CD = 'ACT'
    AND brdn.BURDEN_CD in ('UBD','BGE')
    GROUP BY pftctr.PROFIT_CENTER_CD,
    terr.TERR_LVL4_CD, terr.TERR_LVL4_DE, terr.TERR_LVL5_CD, terr.TERR_LVL5_DE,
    acct.ACCOUNT_LVL1_CD, acct.ACCOUNT_LVL1_DE,
    acct.ACCOUNT_LVL2_CD, acct.ACCOUNT_LVL2_DE, acct.ACCOUNT_LVL3_CD, acct.ACCOUNT_LVL3_DE, acct.ACCOUNT_LVL4_CD, acct.ACCOUNT_LVL4_DE,
    acct.ACCOUNT_LVL5_CD, acct.ACCOUNT_LVL5_DE, acct.ACCOUNT_LVL6_CD, acct.ACCOUNT_LVL6_DE, acct.ACCOUNT_LVL7_CD, acct.ACCOUNT_LVL7_DE,
    fiscal.YEAR, fiscal.QUARTER, brdn.BURDEN_DE
    </code>

    I removed the AS in the select statement and didn't receive the error.

  9. #9
    Join Date
    Aug 2010
    Posts
    3
    It's gr8 learning. Thanks.

Tags for this Thread

Posting Permissions

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