Invalid Column Name

[rwierdsm]

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

[kcbland]

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.

[rwierdsm]

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.

[kcbland]

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.

[rwierdsm]

Here's the statement that just now gave the error:

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  

This statement DOES anaylze with no errors in TOAD.


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
)

Here's the create for GAS_COMMODITY_CONTRACT[/b]

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
)

[kcbland]

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.

[rwierdsm]

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

[kcbland]

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.