asked on

how to join tables in oracle apps

the following code gives error

select TRX.TRX_NUMBER INVOICE_NUMBER
		,TRX.TRX_DATE INVOICE_DATE
		,HZA.ACCOUNT_NUMBER CUSTOMER_NUMBER
		,RAT.NAME TERM_NAME
		,ARPS.DUE_DATE INVOICE_DUE_DATE
		from RA_CUSTOMER_TRX_ALL TRX,
		     oe_order_headers_all OOH,
			 HZ_CUST_ACCOUNTS HZA,
			 RA_TERMS RAT,
			 AR_PAYMENT_SCHEDULES_ALL ARPS
			 where TRX.ct_reference=OOH.order_number
			 AND OOH.SOLD_TO_ORG_ID=HZA.CUST_ACCOUNT_ID
			 AND RAT.TERM_ID=TRX.TERM_ID
			 AND ARPS.CUSTOMER_TRX_ID(+)=TRX.CUSTOMER_TRX_ID
 
the above code works fine but if i pass order number it is giving error
 
select TRX.TRX_NUMBER INVOICE_NUMBER
		,TRX.TRX_DATE INVOICE_DATE
		,HZA.ACCOUNT_NUMBER CUSTOMER_NUMBER
		,RAT.NAME TERM_NAME
		,ARPS.DUE_DATE INVOICE_DUE_DATE
		from RA_CUSTOMER_TRX_ALL TRX,
		     oe_order_headers_all OOH,
			 HZ_CUST_ACCOUNTS HZA,
			 RA_TERMS RAT,
			 AR_PAYMENT_SCHEDULES_ALL ARPS
			 where TRX.ct_reference=OOH.order_number
			 AND OOH.SOLD_TO_ORG_ID=HZA.CUST_ACCOUNT_ID
			 AND RAT.TERM_ID=TRX.TERM_ID
			 AND ARPS.CUSTOMER_TRX_ID(+)=TRX.CUSTOMER_TRX_ID
             and ooh.order_number=62598

Open in new window

Naveen Kumar

what error are you getting ?

Naveen Kumar

without you providing me the error, the only thing i can think of is ...

and ooh.order_number='62598' -- if order_number is varchar2 data type....i have added quotes

awking00

... and to_number(trim(ooh.order_number)) = 62598

sreekanth_mysore

ASKER

order_number is number data type where as ct_reference is of varchar. i have joined these columns. if i try to execute it will give error as" invalid number"

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sreekanth_mysore

ASKER

thanks a lot it is working fine!!