Articles   Members Online: 3
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
How to use Oracle and master-detail queries Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
22-Nov-02
Category
Database-SQL
Language
Delphi 3.x
Views
137
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			Author: Jonas Bilinkevicius 

Oracle and master-detail queries

Answer:

Just the other day I was writing an application that had master-detail queries, 
using the TQuery component. The queries were made against an Oracle 8 database 
using BDE and Delphi 3, but the problem was also present on Oracle 7 and in 
different oracle set ups. The picture was the following:

QUERY1 (master):

SELECT
   A.NAME, A.CODE, B.AREA
FROM
   CLIENTS A, AREAS B
WHERE
   (A.AREACODE = B.CODE)

Note: CODE is CHAR(10) 

QUERY2 (set as detail of QUERY1):

SELECT
   A.ORDERNUM, A.DATE, A.VALUE
FROM
   ORDERS A
WHERE
   (A.CLIENTCODE = :CODE)

Note: CLIENTCODE is CHAR(10)

The problem is that even 'though all the clients with a valid AREACODE would be 
listed, no order for that client would be listed, even if there were data in the 
ORDERS table for all the customers. The QUERY2 dataset was always empty (yes, it 
was Active).

FIXED QUERY2 (set as detail of QUERY1):

SELECT
   A.ORDERNUM, A.DATE, A.VALUE
FROM
   ORDERS A
WHERE
   (RTRIM(A.CLIENTCODE) = RTRIM(:CODE))

This fixed once and for all the problem, and all the orders for each customer were correctly returned by the query. It seemed that when the parameter was passed, it's data type was changed, or some padding was added to the field. Anyway, trimming both fields and comparing only the data part worked.

			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC