We help IT Professionals succeed at work.

how to join tables in oracle apps

sreekanth_mysore
on
10,429 Views
Last Modified: 2013-12-07
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

Comment
Watch Question

Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
what error are you getting ?
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
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
awking00Information Technology Specialist
CERTIFIED EXPERT

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

Author

Commented:
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"
Production Manager / Application Support Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks a lot it is working fine!!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.