| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-23-10, 15:28
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 5
|
|
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
|
|
08-23-10, 15:55
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
Originally Posted by mjstamper
Any advice is welcomed.
|
Post the error message.
|
|
08-23-10, 15:56
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 5
|
|
|
|
Sorry,
SQL0119N
SQLSTATE = 42803
|
|
08-23-10, 16:08
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
That's not a message; this looks like an error code.
|
|
08-23-10, 16:13
|
|
Registered User
|
|
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."
|
|
08-23-10, 16:17
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
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.
|
|
08-23-10, 16:21
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 5
|
|
Hi n_i,
I tried that and I am still getting the same error.
|
|
08-23-10, 16:52
|
|
Registered User
|
|
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.
|
|
08-26-10, 09:06
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 3
|
|
It's gr8 learning. Thanks.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
DB2 SQL Error
