• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Paweł Baczyński
  • Piet Souris
  • Vijitha Kumara

How can I write a subquery for the given queries?

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Please help by giving the subquery for the below queries...

1)select CUSTOMER_ID,source_header_id from WSH.WSH_DELIVERY_DETAILS where SERIAL_NUMBER like 'AAX04140025SA' and source_header_number like '1281003'

say example the above query returns CUSTOMER_ID as "xxxxxx" and Soutce_header_id as "yyyyyy"

2)select account_number from ar.hz_cust_accounts where cust_account_ID like 'xxxxxx'


3)select attribute5 from ONT.OE_ORDER_HEADERS_ALL where header_id=yyyyy


My actual output will be "account_number " from second query and "attribute5" from third query.

I want as a single query which returns "account_number" and "attribute5".

Please help me out...

Thanks,
Mohan
[ September 22, 2005: Message edited by: Mohan Ganesan ]
 
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
in general subquery look like this
select column from table where column2 = (select c3 from t2)

but subquery must return only one value, instead use IN.
 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mohan,
Here is the sub query solution - may or may not require minor syntax changes based on the sql brand you are using


select account_number,attribute5
from
hz_cust_accounts,OE_ORDER_HEADERS_ALL
where
hz_cust_accounts.cust_account_ID like (select CUSTOMER_ID from WSH.WSH_DELIVERY_DETAILS where SERIAL_NUMBER like 'AAX04140025SA' and source_header_number like '1281003')
and
OE_ORDER_HEADERS_ALL.header_id = (select CUSTOMER_ID,source_header_id from WSH.WSH_DELIVERY_DETAILS where SERIAL_NUMBER like 'AAX04140025SA' and source_header_number like '1281003')

Cheers,
Gary
 
Paper beats rock. Scissors beats tiny ad.
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!