OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » Ora 00904

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Ora 00904 [message #298865]

Fri, 08 February 2008 01:15

Venkata.Tadinada
Messages: 2
Registered: February 2008

Junior Member

URGENT --- help needed.

I have a report that gets data from primary and archived instances. Both the instances have same table structure. Here is the query for the report

SELECT A.FAC_ID,
A.SHPMNT_ID,
A.CHARGE_NR,
G.RV_MEANING,
/* A.TICKET_TYP, */
A.CNT_ST,
A.CHARGE_TYPE,
B.MATERIAL_CODE,
A.CDE_SHIP_UNIT_ID,
A.FROM_FACILITY,
A.TO_FACILITY,
TRUNC(A.DATE_SHIP),
TRUNC(A.DATE_REC),
TRUNC(A.DATE_REL),
SUM(B.QTY_PICKU_TRSFD)
FROM TTH A,
TTL B,
CG_REF_CODES G
WHERE A.TICKET_NR = B.TTH_TICKET_NR
AND A.CDE_SHIP_UNIT_ID = DECODE(:P_PALLET_ID, '%', A.CDE_SHIP_UNIT_ID, :P_PALLET_ID)
AND A.CHARGE_NR = DECODE(:P_CHARGE_NR,'%', A.CHARGE_NR, :P_CHARGE_NR)
AND nvl(A.SHPMNT_ID, ' ') = DECODE(:P_SHPMNT_ID ,'%', nvl(A.SHPMNT_ID, ' ') , :P_SHPMNT_ID )
AND A.FAC_ID = :P_FACILITY
AND A.CNT_ST != 95
AND DATE_REL BETWEEN DECODE( :P_START_DT, NULL ,DATE_REL,:P_START_DT) AND DECODE(:P_END_DT,NULL , DATE_REL,:P_END_DT)
AND G.RV_DOMAIN = 'TICKET_TYP'
AND A.TICKET_TYP = G.RV_LOW_VALUE
GROUP BY A.FAC_ID,
A.SHPMNT_ID,
A.CHARGE_NR,
G.RV_MEANING,
/* A.TICKET_TYP, */
A.CNT_ST,
A.CHARGE_TYPE,
B.MATERIAL_CODE,
A.CDE_SHIP_UNIT_ID,
A.FROM_FACILITY,
A.TO_FACILITY,
TRUNC(A.DATE_SHIP),
TRUNC(A.DATE_REC),
TRUNC(A.DATE_REL)
UNION
SELECT A.FAC_ID,
A.SHPMNT_ID,
A.CHARGE_NR,
G.RV_MEANING,
/* A.TICKET_TYP, */
A.CNT_ST,
A.CHARGE_TYPE,
B.MATERIAL_CODE,
A.CDE_SHIP_UNIT_ID,
A.FROM_FACILITY,
A.TO_FACILITY,
TRUNC(A.DATE_SHIP),
TRUNC(A.DATE_REC),
TRUNC(A.DATE_REL),
SUM(B.QTY_PICKU_TRSFD)
FROM TTH&P_DBLINK A,
TTL&P_DBLINK B,
CG_REF_CODES G
WHERE A.TICKET_NR = B.TTH_TICKET_NR
AND A.TTH_IDENTIFIER = B.TTL_IDENTIFIER
AND A.CDE_SHIP_UNIT_ID = DECODE(:P_PALLET_ID, '%', A.CDE_SHIP_UNIT_ID, :P_PALLET_ID)
AND A.CHARGE_NR = DECODE(:P_CHARGE_NR,'%', A.CHARGE_NR, :P_CHARGE_NR)
AND nvl(A.SHPMNT_ID, ' ') = DECODE(:P_SHPMNT_ID ,'%', nvl(A.SHPMNT_ID, ' ') , :P_SHPMNT_ID )
AND A.FAC_ID = :P_FACILITY
AND DATE_REL BETWEEN DECODE( :P_START_DT, NULL ,DATE_REL,:P_START_DT) AND DECODE(:P_END_DT,NULL , DATE_REL,:P_END_DT)
AND G.RV_DOMAIN = 'TICKET_TYP'
AND A.TICKET_TYP = G.RV_LOW_VALUE
GROUP BY A.FAC_ID,
A.SHPMNT_ID,
A.CHARGE_NR,
G.RV_MEANING,
/* A.TICKET_TYP, */
A.CNT_ST,
A.CHARGE_TYPE,
B.MATERIAL_CODE,
A.CDE_SHIP_UNIT_ID,
A.FROM_FACILITY,
A.TO_FACILITY,
TRUNC(A.DATE_SHIP),
TRUNC(A.DATE_REC),
TRUNC(A.DATE_REL)
ORDER BY 3,8

I modified the archival database to add an additional column as per the user requirements and I modified the corresponding select query to include an additional condition in the where clause. Change highlighted in the query below

SELECT A.FAC_ID,
A.SHPMNT_ID,
A.CHARGE_NR,
G.RV_MEANING,
/* A.TICKET_TYP, */
A.CNT_ST,
A.CHARGE_TYPE,
B.MATERIAL_CODE,
A.CDE_SHIP_UNIT_ID,
A.FROM_FACILITY,
A.TO_FACILITY,
TRUNC(A.DATE_SHIP),
TRUNC(A.DATE_REC),
TRUNC(A.DATE_REL),
SUM(B.QTY_PICKU_TRSFD)
FROM TTH A,
TTL B,
CG_REF_CODES G
WHERE A.TICKET_NR = B.TTH_TICKET_NR
AND A.CDE_SHIP_UNIT_ID = DECODE(:P_PALLET_ID, '%', A.CDE_SHIP_UNIT_ID, :P_PALLET_ID)
AND A.CHARGE_NR = DECODE(:P_CHARGE_NR,'%', A.CHARGE_NR, :P_CHARGE_NR)
AND nvl(A.SHPMNT_ID, ' ') = DECODE(:P_SHPMNT_ID ,'%', nvl(A.SHPMNT_ID, ' ') , :P_SHPMNT_ID )
AND A.FAC_ID = :P_FACILITY
AND A.CNT_ST != 95
AND DATE_REL BETWEEN DECODE( :P_START_DT, NULL ,DATE_REL,:P_START_DT) AND DECODE(:P_END_DT,NULL , DATE_REL,:P_END_DT)
AND G.RV_DOMAIN = 'TICKET_TYP'
AND A.TICKET_TYP = G.RV_LOW_VALUE
GROUP BY A.FAC_ID,
A.SHPMNT_ID,
A.CHARGE_NR,
G.RV_MEANING,
/* A.TICKET_TYP, */
A.CNT_ST,
A.CHARGE_TYPE,
B.MATERIAL_CODE,
A.CDE_SHIP_UNIT_ID,
A.FROM_FACILITY,
A.TO_FACILITY,
TRUNC(A.DATE_SHIP),
TRUNC(A.DATE_REC),
TRUNC(A.DATE_REL)
UNION
SELECT A.FAC_ID,
A.SHPMNT_ID,
A.CHARGE_NR,
G.RV_MEANING,
/* A.TICKET_TYP, */
A.CNT_ST,
A.CHARGE_TYPE,
B.MATERIAL_CODE,
A.CDE_SHIP_UNIT_ID,
A.FROM_FACILITY,
A.TO_FACILITY,
TRUNC(A.DATE_SHIP),
TRUNC(A.DATE_REC),
TRUNC(A.DATE_REL),
SUM(B.QTY_PICKU_TRSFD)
FROM TTH&P_DBLINK A,
TTL&P_DBLINK B,
CG_REF_CODES G
WHERE A.TICKET_NR = B.TTH_TICKET_NR
AND A.TTH_INDENTIFIER = B.TTL_IDENTIFIER
AND A.TTH_IDENTIFIER = B.TTL_IDENTIFIER
AND A.CDE_SHIP_UNIT_ID = DECODE(:P_PALLET_ID, '%', A.CDE_SHIP_UNIT_ID, :P_PALLET_ID)
AND A.CHARGE_NR = DECODE(:P_CHARGE_NR,'%', A.CHARGE_NR, :P_CHARGE_NR)
AND nvl(A.SHPMNT_ID, ' ') = DECODE(:P_SHPMNT_ID ,'%', nvl(A.SHPMNT_ID, ' ') , :P_SHPMNT_ID )
AND A.FAC_ID = :P_FACILITY
AND DATE_REL BETWEEN DECODE( :P_START_DT, NULL ,DATE_REL,:P_START_DT) AND DECODE(:P_END_DT,NULL , DATE_REL,:P_END_DT)
AND G.RV_DOMAIN = 'TICKET_TYP'
AND A.TICKET_TYP = G.RV_LOW_VALUE
GROUP BY A.FAC_ID,
A.SHPMNT_ID,
A.CHARGE_NR,
G.RV_MEANING,
/* A.TICKET_TYP, */
A.CNT_ST,
A.CHARGE_TYPE,
B.MATERIAL_CODE,
A.CDE_SHIP_UNIT_ID,
A.FROM_FACILITY,
A.TO_FACILITY,
TRUNC(A.DATE_SHIP),
TRUNC(A.DATE_REC),
TRUNC(A.DATE_REL)
ORDER BY 3,8

Now my query will throw a ORA 00904 error with an invalid identifier against the new columns, the same query works fine if I directly use the dblink name instead of the lexical (&P_DBLINK). This lexical is propulated in the Before form trigger.

Any thoughts why this is happening?

Report message to a moderator

Re: Ora 00904 [message #298958 is a reply to message #298865]

Fri, 08 February 2008 06:23

Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland

Senior Member

Hi,

I guess its a typo problem

AND A.TTH_INDENTIFIER = B.TTL_IDENTIFIER
AND A.TTH_IDENTIFIER = B.TTL_IDENTIFIER

But why you want to have this AND two times ? It is the same as the line below it

regards
Uwe

[Updated on: Fri, 08 February 2008 06:24]

Report message to a moderator

Re: Ora 00904 [message #298983 is a reply to message #298958]

Fri, 08 February 2008 07:17

Venkata.Tadinada
Messages: 2
Registered: February 2008

Junior Member

I'm sorry. The typo happened in this topic.

The actual query has only " AND A.TTH_IDENTIFIER = B.TTL_IDENTIFIER " only once.

Report message to a moderator

Re: Ora 00904 [message #300146 is a reply to message #298983]

Thu, 14 February 2008 07:24

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

A wild guess: the database link is pointing to another database then you think it is (or the interpretation of &dblink works different from what you expect it does), and your quering another database then you think you are, where these new columns not (yet) exist. Hence the invalid identifier error.

This can be checked by incorporating something like select db_name from v$parameter or something in you query.

Report message to a moderator

Previous Topic:	Problem in printing rectangles (barcode) in Internet Explorer
Next Topic:	Report Scheduling

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 08:52:23 CDT 2024