Home » Developer & Programmer » Reports & Discoverer » Ora 00904
Ora 00904 [message #298865] Fri, 08 February 2008 01:15 Go to next message
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?

Re: Ora 00904 [message #298958 is a reply to message #298865] Fri, 08 February 2008 06:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Ora 00904 [message #300146 is a reply to message #298983] Thu, 14 February 2008 07:24 Go to previous message
skooman
Messages: 912
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.
Previous Topic: Problem in printing rectangles (barcode) in Internet Explorer
Next Topic: Report Scheduling
Goto Forum:
  


Current Time: Tue Dec 06 00:13:25 CST 2016

Total time taken to generate the page: 0.12050 seconds