Invalid Column Name
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Invalid Column Name
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
Your reference lookup query looks syntactically correct. However, you did not paste the SQL that is giving you the problem. Could the missing GROUP-BY clause you left out have the issue?
There error message indicates that you are referencing a column that does not exist in one of the tables.
There error message indicates that you are referencing a column that does not exist in one of the tables.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Hi Kenneth,
I've been paring down the SQL to eliminate anything 'fancy' in hopes of finding a SQL statement that DS is happy with, intending to build it up again piece by piece to find where it gets unhappy.
This is about as far down as I can pare the SQL and still have it resemble what I'm trying to do.
The SQL listed above still gives me the error.
Here's the exact error message:
EWDM576PremConsGasRolling..GetRollingConsumption: ORA-00904: invalid column name
EWDM576PremConsGasRolling..GetRollingConsumption.DSLink1: DSP.Open GCI $DSP.Open error -2.
I've been paring down the SQL to eliminate anything 'fancy' in hopes of finding a SQL statement that DS is happy with, intending to build it up again piece by piece to find where it gets unhappy.
This is about as far down as I can pare the SQL and still have it resemble what I'm trying to do.
The SQL listed above still gives me the error.
Here's the exact error message:
EWDM576PremConsGasRolling..GetRollingConsumption: ORA-00904: invalid column name
EWDM576PremConsGasRolling..GetRollingConsumption.DSLink1: DSP.Open GCI $DSP.Open error -2.
Please post the exact SQL, as well as a describe of the tables involved. Please also use the code formatting so that it's easy to read. I'll bet dollars to donuts you have a column misnamed or mis-aliased.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Re: Invalid Column Name
Here's the statement that just now gave the error:
This statement DOES anaylze with no errors in TOAD.
Here's the create for GAS_COMMODITY_BILLING_FACT
Here's the create for GAS_COMMODITY_CONTRACT[/b]
Here's the create for PREMISE_CONSUMPTION_SNAPSHOT
Code: Select all
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
Here's the create for GAS_COMMODITY_BILLING_FACT
Code: Select all
CREATE TABLE GAS_COMMODITY_BILLING_FACT
(
COMPANY_KEY NUMBER NOT NULL,
GEOGRAPHY_KEY NUMBER(9) NOT NULL,
CUSTOMER_KEY NUMBER NOT NULL,
PREMISE_KEY NUMBER NOT NULL,
DEMOGRAPHICS_KEY NUMBER(8) NOT NULL,
GAS_BROKER_CONTRACT_KEY NUMBER NOT NULL,
GAS_COMMODITY_CONTRACT_KEY NUMBER NOT NULL,
BILLED_DATE NUMBER(8) NOT NULL,
FROM_BILLING_PERIOD NUMBER(8) NOT NULL,
TO_BILLING_PERIOD NUMBER(8) NOT NULL,
UTILITY_KEY NUMBER(10) NOT NULL,
TRANSACTION_TYPE_KEY NUMBER NOT NULL,
BILLED_AMOUNT_REVENUE NUMBER(9,2) NOT NULL,
CONSUMPTION_AMOUNT NUMBER NOT NULL,
CYCLE_DATE DATE NOT NULL,
DW_CREATE_DATE DATE DEFAULT sysdate,
GAS_ACCOUNT_NUMBER VARCHAR2(20),
COMMODITY_BILLED_RATE NUMBER,
BILLING_PERIOD_DAYS NUMBER(3),
AVG_VOLUME_PER_DAY NUMBER,
AVG_REVENUE_PER_DAY NUMBER,
GAS_SAVINGS_AMOUNT NUMBER,
DW_CREATE_BATCH_KEY NUMBER,
DW_UPDATE_BATCH_KEY NUMBER,
DW_UPDATE_DATE DATE
)
Code: Select all
CREATE TABLE GAS_COMMODITY_CONTRACT
(
COMPANY_KEY NUMBER NOT NULL,
GAS_BROKER_CONTRACT_KEY NUMBER NOT NULL,
GAS_COMMODITY_CONTRACT_KEY NUMBER NOT NULL,
CUSTOMER_KEY NUMBER,
PREMISE_KEY NUMBER,
STATUS_KEY NUMBER(10),
STATUS_CHANGE_DATE_KEY NUMBER(8),
UTILITY_KEY NUMBER(10) NOT NULL,
UTILITY_STATUS VARCHAR2(20),
UTIL_BROKER_AGREEMENT_NUMBER VARCHAR2(40),
UTIL_BROKER_PRICE_POINT VARCHAR2(40),
SALES_CHANNEL_KEY NUMBER(10),
SALES_OFFICE_KEY NUMBER(10),
CONTRACT_NUMBER VARCHAR2(70) NOT NULL,
APPLICATION_NUMBER VARCHAR2(20),
AGENT_NUMBER VARCHAR2(20),
CONTRACT_START_DATE DATE NOT NULL,
CONTRACT_END_DATE DATE NOT NULL,
CONTRACT_CANCEL_DATE DATE,
CONTRACT_APPLICATION_DATE DATE,
GAS_ACCOUNT_NUMBER VARCHAR2(20) NOT NULL,
USAGE_AT_SIGNUP_12MONTHS NUMBER,
AIR_MILES_NUMBER VARCHAR2(20),
EXTRACT_DATE DATE DEFAULT sysdate NOT NULL,
MARKETING_STATUS_KEY NUMBER(10),
MARKETING_SUB_STATUS_KEY NUMBER(10),
MARKETING_MISC_STATUS_KEY NUMBER(10),
CONTRACT_FINALIZED_FLAG VARCHAR2(1),
FIRST_BILLED_DATE DATE,
LAST_BILLED_DATE DATE,
PROGRAM_CODE VARCHAR2(20),
PROGRAM_FLOW_DATE DATE,
PROGRAM_EXPIRY_DATE DATE,
PROGRAM_PRICE NUMBER,
PROGRAM_UNIT_OF_MEASURE VARCHAR2(10),
RENEWAL_DATE DATE,
COMMERCIAL_RESIDENTIAL_FLAG VARCHAR2(20),
CONTRACT_ACTION_TYPE VARCHAR2(20),
CANCELLATION_REASON VARCHAR2(40),
ELEC_B2B_FLAG CHAR(10) DEFAULT '-',
GAS_B2B_FLAG CHAR(10) DEFAULT '-',
UTIL_BROKER_RENEWAL_DATE DATE,
PROGRAM_TERM NUMBER(5),
PROGRAM_TYPE VARCHAR2(2),
CUSTOMER_FLOW_DATE DATE,
DW_PROCESSING_FLAG VARCHAR2(3),
CURRENT_RECORD_INDICATOR VARCHAR2(1),
DW_CREATE_BATCH_KEY NUMBER,
DW_CREATE_DATE DATE,
DW_UPDATE_BATCH_KEY NUMBER,
DW_UPDATE_DATE DATE
)
Here's the create for PREMISE_CONSUMPTION_SNAPSHOT
Code: Select all
CREATE TABLE PREMISE_CONSUMPTION_SNAPSHOT
(
PREMISE_KEY NUMBER NOT NULL,
COMMODITY_TYPE VARCHAR2(2) NOT NULL,
CONTRACT_FLOW_DATE DATE NOT NULL,
CURRENT_REVENUE NUMBER NOT NULL,
CURRENT_CONSUMPTION NUMBER NOT NULL,
CURRENT_CYCLE_DATE DATE NOT NULL,
ATD_REVENUE NUMBER NOT NULL,
ATD_CONSUMPTION NUMBER NOT NULL,
ANNIVERSARY_BILLED_MONTHS NUMBER NOT NULL,
CTD_REVENUE NUMBER NOT NULL,
CTD_CONSUMPTION NUMBER NOT NULL,
NUM_MONTHS_BILLED NUMBER NOT NULL,
AVG_REVENUE_PER_DAY NUMBER NOT NULL,
AVG_CONSUMPTION_PER_DAY NUMBER NOT NULL,
UTILITY_CODE VARCHAR2(4) NOT NULL,
EFFECTIVE_DATE DATE NOT NULL,
CURRENT_PRICE NUMBER NOT NULL,
CURRENT_MARKET_RATE NUMBER NOT NULL,
PROGRAM_CODE VARCHAR2(20) NOT NULL,
UTIL_AGREEMENT_NUMBER VARCHAR2(40) NOT NULL,
CONTRACT_EXPIRY_DATE DATE NOT NULL,
MONTHS_REMAINING NUMBER NOT NULL,
ROLLING_CONSUMPTION NUMBER NOT NULL,
CURRENT_LD NUMBER NOT NULL,
CURRENT_GS NUMBER NOT NULL,
DW_CREATE_DATE DATE NOT NULL,
DW_CREATE_BATCH_KEY NUMBER NOT NULL,
DW_UPDATE_DATE DATE NOT NULL,
DW_UPDATE_BATCH_KEY NUMBER NOT NULL
)
Any chance your schema name is wrong? I created your tables and verified your SQL. The schema name messed with me at first (CNADW) but it works when removed.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
We do generally remove the schema names, however, cnadw is correct.
Further testing has revealed that, even though I still receive these errors, I can get the statement to run. Initial observations indicate that the data is correct.
This is quite bizarre.
Kenneth, I appreciate your efforts on my behalf.
Rob Wierdsma
Further testing has revealed that, even though I still receive these errors, I can get the statement to run. Initial observations indicate that the data is correct.
This is quite bizarre.
Kenneth, I appreciate your efforts on my behalf.
Rob Wierdsma
Hey, no problem. I was thinking that maybe you had a local db object in your schema, but the full qualification was sending the query elsewhere.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle