Help with an Oracle Error...

[Thomas DeMaio]

When I run the Querry below, I get the following error...

S0022(904)[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: "DEPOSIT_ITEM_AMT": invalid identifier (0.13 secs)

Can anyone tell me where my error is...

Thanks...


SELECT D.DEPOSIT_ID, D.DEPOSIT_DT, D.EXPECTED_DEPOSIT_AMT,
I.DEPOSIT_ID, I.DEPOSIT_ITEM_DT, I.DEPOSIT_ITEM_AMT
FROM ST2_REINSARAP.DEPOSIT D

FULL JOIN

(SELECT DEPOSIT_ID, DEPOSIT_ITEM_DT, SUM(DEPOSIT_ITEM_AMT)
FROM ST2_REINSARAP.DEPOSIT_ITEM
GROUP BY DEPOSIT_ID, DEPOSIT_ITEM_DT) I

ON D.DEPOSIT_ID = I.DEPOSIT_ID

WHERE

NVL(D.DEPOSIT_ID,0) <> NVL(I.DEPOSIT_ID,0) OR
NVL(DEPOSIT_DT,'0001-01-01') <> NVL(DEPOSIT_ITEM_DT,'0001-01-01') OR
NVL(EXPECTED_DEPOSIT_AMT,0) <> NVL(DEPOSIT_ITEM_AMT,0)

[Rabbit]

You don't have DEPOSIT_ITEM_AMT in your subquery. You have SUM(DEPOSIT_ITEM_AMT).

[Brian Connelly]

I would check the column types again. The error is pointing to the column DEPOSIT_ITEM_AMT. This is caused by either the column does not exist. Im not sure but it could be caused by the joining of the columns where you are using the SUM function for DEPOSIT_ITEM_AMT and your selected join is DEPOSIT_ITEM_AMT. These could be resulting in an alias mistaken. Im not an expert on this though...just finished up a database course.

[Thomas DeMaio]

Thanks!....

I changed SUM(DEPOSIT_ITEM_AMT) to SUM(DEPOSIT_ITEM_AMT) as DEPOSIT_ITEM_AMT and it works...