CODEWITHSUNDEEP
sqloracle11g
Shaves
Shaves

Reputation: 930

Error message / troubleshooting my code

I'm getting the following error message when I run my query:

ORA-00904: "ACCNT": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error at Line: 33 Column: 29

Here is the code I'm running:

select 
pdl.business_unit as BU,
pdl.deptid as BR,
pb.in_service_dt as IN_SERVICE_DATE,
pdl.asset_id as ASSET_ID,
pa.descr as DESCRIPTION,
pa.serial_id as SERIAL_NUMBER,
pdl.account as ACCNT,

case when pdl.trans_in_out = 'I' then 'TRANSFERRED TO' else pdl.deptid end as DEPTID,

MAX(CASE WHEN (pdl.trans_in_out = 'O') then pdl.deptid END) as ACCT_FROM,
MAX(CASE WHEN (pdl.trans_in_out = 'I') then pdl.deptid END) as ACCT_TO

From ps_dist_ln pdl,
ps_book pb,
ps_asset pa

where 
pdl.business_unit = 'A0465'
and pdl.fiscal_year = '2014'
and pdl.asset_id = '000000000151'
and pdl.accounting_period = '12'
and pdl.trans_type = 'RCT'
and pdl.distribution_type = 'FA'
and pdl.book = 'PERFORM'
and pdl.asset_id = pb.asset_id and pdl.business_unit = pb.business_unit
and pdl.asset_id = pa.asset_id and pdl.business_unit = pa.business_unit

GROUP BY
BU,
BR,
IN_SERVICE_DATE,
ASSET_ID,
DESCRIPTION,
SERIAL_NUMBER,
ACCNT,
DEPTID

I can't figure out why I am getting this error. All of the field names and aliases look correct. Any help would be greatly appreciated. Thank You for your help.

Upvotes: 0

Views: 103

Answers (1)

AHiggins
AHiggins

Reputation: 7219

GROUP BY does not recognize aliases defined in the SELECT clause.

If your SELECT statement says this:

SELECT
    pdl.business_unit as BU,
    pdl.deptid as BR,
    pb.in_service_dt as IN_SERVICE_DATE,
    pdl.asset_id as ASSET_ID,
    pa.descr as DESCRIPTION,
    pa.serial_id as SERIAL_NUMBER,
    pdl.account as ACCNT,
    case when pdl.trans_in_out = 'I' then 'TRANSFERRED TO' else pdl.deptid end AS DEPTID

Then your GROUP BY statement should say this:

GROUP BY 
    pdl.business_unit,
    pdl.deptid,
    pb.in_service_dt,
    pdl.asset_id,
    pa.descr,
    pa.serial_id,
    pdl.account,
    case when pdl.trans_in_out = 'I' then 'TRANSFERRED TO' else pdl.deptid end

Upvotes: 3

Related Questions