Invalid Column Name
Posted: Mon Mar 29, 2004 11:50 am
Folks,
DataStage is giving me 'ORA-00904 Invalid Column Name' on the statement below. This statement gives no problems in TOAD.
I've defined 4 entries on the Columns tab to receive the 4 columns, with the first two as key values. I'm using the ORAOCI8 stage.
I've tried re-importing the table definitions into DS from Oracle, fiddling with the SQL statement, all to no effect.
This is the first time I've tried to return the results of a 'group by' statement via DS (sorry this version of the SQL actually has the group by removed). Is this what's at issue?
If I've forgotted any relevant details, let me know. Any help would be much appreciated.
Rob Wierdsma
SELECT
GAS_COMMODITY_BILLING_FACT.PREMISE_KEY AS PREMISE_KEY
, GAS_COMMODITY_CONTRACT.CUSTOMER_FLOW_DATE AS CUSTOMER_FLOW_DATE
, GAS_COMMODITY_BILLING_FACT.CYCLE_DATE AS FIRST_CYCLE_DATE
, GAS_COMMODITY_BILLING_FACT.CONSUMPTION_AMOUNT AS ROLLING_CONSUMPTION
FROM
CNADW.GAS_COMMODITY_CONTRACT GAS_COMMODITY_CONTRACT,
CNADW.GAS_COMMODITY_BILLING_FACT GAS_COMMODITY_BILLING_FACT,
CNADW.PREMISE_CONSUMPTION_SNAPSHOT PREMISE_CONSUMPTION_SNAPSHOT
WHERE
GAS_COMMODITY_BILLING_FACT.PREMISE_KEY = :1
AND GAS_COMMODITY_CONTRACT.CUSTOMER_FLOW_DATE = TO_DATE(:2,'YYYYMMDD')
AND PREMISE_CONSUMPTION_SNAPSHOT.PREMISE_KEY=:1
AND PREMISE_CONSUMPTION_SNAPSHOT.CONTRACT_FLOW_DATE=TO_DATE(:2,'YYYYMMDD')
AND PREMISE_CONSUMPTION_SNAPSHOT.COMMODITY_TYPE = 'G'
AND GAS_COMMODITY_CONTRACT.PREMISE_KEY = GAS_COMMODITY_BILLING_FACT.PREMISE_KEY
AND GAS_COMMODITY_CONTRACT.CUSTOMER_KEY = GAS_COMMODITY_BILLING_FACT.CUSTOMER_KEY
AND GAS_COMMODITY_CONTRACT.COMPANY_KEY = GAS_COMMODITY_BILLING_FACT.COMPANY_KEY
AND GAS_COMMODITY_CONTRACT.GAS_BROKER_CONTRACT_KEY = GAS_COMMODITY_BILLING_FACT.GAS_BROKER_CONTRACT_KEY
AND GAS_COMMODITY_CONTRACT.GAS_COMMODITY_CONTRACT_KEY = GAS_COMMODITY_BILLING_FACT.GAS_COMMODITY_CONTRACT_KEY
AND GAS_COMMODITY_BILLING_FACT.CYCLE_DATE BETWEEN ADD_MONTHS(PREMISE_CONSUMPTION_SNAPSHOT.EFFECTIVE_DATE, -12)
AND PREMISE_CONSUMPTION_SNAPSHOT.EFFECTIVE_DATE
DataStage is giving me 'ORA-00904 Invalid Column Name' on the statement below. This statement gives no problems in TOAD.
I've defined 4 entries on the Columns tab to receive the 4 columns, with the first two as key values. I'm using the ORAOCI8 stage.
I've tried re-importing the table definitions into DS from Oracle, fiddling with the SQL statement, all to no effect.
This is the first time I've tried to return the results of a 'group by' statement via DS (sorry this version of the SQL actually has the group by removed). Is this what's at issue?
If I've forgotted any relevant details, let me know. Any help would be much appreciated.
Rob Wierdsma
SELECT
GAS_COMMODITY_BILLING_FACT.PREMISE_KEY AS PREMISE_KEY
, GAS_COMMODITY_CONTRACT.CUSTOMER_FLOW_DATE AS CUSTOMER_FLOW_DATE
, GAS_COMMODITY_BILLING_FACT.CYCLE_DATE AS FIRST_CYCLE_DATE
, GAS_COMMODITY_BILLING_FACT.CONSUMPTION_AMOUNT AS ROLLING_CONSUMPTION
FROM
CNADW.GAS_COMMODITY_CONTRACT GAS_COMMODITY_CONTRACT,
CNADW.GAS_COMMODITY_BILLING_FACT GAS_COMMODITY_BILLING_FACT,
CNADW.PREMISE_CONSUMPTION_SNAPSHOT PREMISE_CONSUMPTION_SNAPSHOT
WHERE
GAS_COMMODITY_BILLING_FACT.PREMISE_KEY = :1
AND GAS_COMMODITY_CONTRACT.CUSTOMER_FLOW_DATE = TO_DATE(:2,'YYYYMMDD')
AND PREMISE_CONSUMPTION_SNAPSHOT.PREMISE_KEY=:1
AND PREMISE_CONSUMPTION_SNAPSHOT.CONTRACT_FLOW_DATE=TO_DATE(:2,'YYYYMMDD')
AND PREMISE_CONSUMPTION_SNAPSHOT.COMMODITY_TYPE = 'G'
AND GAS_COMMODITY_CONTRACT.PREMISE_KEY = GAS_COMMODITY_BILLING_FACT.PREMISE_KEY
AND GAS_COMMODITY_CONTRACT.CUSTOMER_KEY = GAS_COMMODITY_BILLING_FACT.CUSTOMER_KEY
AND GAS_COMMODITY_CONTRACT.COMPANY_KEY = GAS_COMMODITY_BILLING_FACT.COMPANY_KEY
AND GAS_COMMODITY_CONTRACT.GAS_BROKER_CONTRACT_KEY = GAS_COMMODITY_BILLING_FACT.GAS_BROKER_CONTRACT_KEY
AND GAS_COMMODITY_CONTRACT.GAS_COMMODITY_CONTRACT_KEY = GAS_COMMODITY_BILLING_FACT.GAS_COMMODITY_CONTRACT_KEY
AND GAS_COMMODITY_BILLING_FACT.CYCLE_DATE BETWEEN ADD_MONTHS(PREMISE_CONSUMPTION_SNAPSHOT.EFFECTIVE_DATE, -12)
AND PREMISE_CONSUMPTION_SNAPSHOT.EFFECTIVE_DATE