DataPipeline returns duplicate records when sort/order by is used.
Greetings,
Delphi 5.1
ReportBuilder Pro 6.03
I am trying to create an account acknowledgment report and sort the report
on LAST_NAME then FIRST_NAME. Each account record on this report can have
one or more people assigned to it.
Here is an example of what the report should look like:
Account No: Name(s) Clt Ref No:
...
1057-1-1 Hampton, Emily A. NM152653
1065-1-1 Hardesty Jr., Robert J. NM162512
Williams, Maria W
1007-2-1 Hayden, Michael S. NM112654
...
Here is an example of what the report currently like:
Account No: Name(s) Clt Ref No:
1057-1-1 Hampton, Emily A. NM152653
1065-1-1 Hardesty Jr., Robert J. NM162512
Williams, Maria W
1007-2-1 Hayden, Michael S. NM112654
...
1028-1-1 Williams, David A. NM152632
1065-1-1 Hardesty Jr., Robert J. NM162512
Williams, Maria W
1035-1-1 Wolf, Christine M. NM152263
...
Notice that Account number 1065-1-1 was printed twice.
I looked at the Preview Data for the master pipeline and the 1065-1-1
Account row is in there twice. I have determined that the data is
duplicated only when there is an order by attached to the SQL statement and
futhermore only when the order by is based a a name field form the PERSON
table.
We are using the ppReportExplorer to handle all of our end user reporting
needs.
1) We created a New Report, switch to the Data tab, then selected the Query
Designer for the File | New... menu.
2) On the Tables tab I selected three tables and this is the resulting SQL
statement
SELECT DISTINCT DEBT_1.ACCT_ID, DEBT_1.CASE_ID,
DEBT_1.DEBT_NO,
DEBT_1.CLT_REF_NO,
DEBT_1.ORIG_PRINCIPAL,
DEBT_1.SERVICE_DATE,
DEBT_1.LIST_DATE
FROM DEBT DEBT_1
INNER JOIN DEBTOR_CASE_DEBT DEBTOR_CASE_DEBT_1 ON
(DEBTOR_CASE_DEBT_1.ACCT_ID = DEBT_1.ACCT_ID)
AND (DEBTOR_CASE_DEBT_1.CASE_ID = DEBT_1.CASE_ID)
AND (DEBTOR_CASE_DEBT_1.DEBT_NO = DEBT_1.DEBT_NO)
INNER JOIN PERSON PERSON_1 ON
(PERSON_1.PERSON_ID = DEBTOR_CASE_DEBT_1.PERSON_ID)
When I run the report or look at the data preview for the master
datapipline, all is fine, except that the user wants the report to be sorted
by the person name column. However, if I add a sort field to the SQL then
both the report and the data preview now have a duplicate records showing.
Here is the SQL Statement.
SELECT DISTINCT DEBT_1.ACCT_ID, DEBT_1.CASE_ID,
DEBT_1.DEBT_NO,
DEBT_1.CLT_REF_NO,
DEBT_1.ORIG_PRINCIPAL,
DEBT_1.SERVICE_DATE,
DEBT_1.LIST_DATE
FROM DEBT DEBT_1
INNER JOIN DEBTOR_CASE_DEBT DEBTOR_CASE_DEBT_1 ON
(DEBTOR_CASE_DEBT_1.ACCT_ID = DEBT_1.ACCT_ID)
AND (DEBTOR_CASE_DEBT_1.CASE_ID = DEBT_1.CASE_ID)
AND (DEBTOR_CASE_DEBT_1.DEBT_NO = DEBT_1.DEBT_NO)
INNER JOIN PERSON PERSON_1 ON
(PERSON_1.PERSON_ID = DEBTOR_CASE_DEBT_1.PERSON_ID)
ORDER BY PERSON_1.LAST_NAME
Notice that the only thing that has changed in the SQL statement is that an
ORDER BY field was added. Also notice that NO FIELD from the PERSON table
is returned in the result set.
NOTE: If I copy the above SQL statement with the order by clause and run it
in "Database WorkBench" or "IB Console" It will NOT return a duplicate
record for account number 1065-1-1? Is their a bug in vs. 6.03
DataPipeline?
Any help appeciated.
Michael Tuttle
Software Technologies, Inc.
Topeka, KS
Delphi 5.1
ReportBuilder Pro 6.03
I am trying to create an account acknowledgment report and sort the report
on LAST_NAME then FIRST_NAME. Each account record on this report can have
one or more people assigned to it.
Here is an example of what the report should look like:
Account No: Name(s) Clt Ref No:
...
1057-1-1 Hampton, Emily A. NM152653
1065-1-1 Hardesty Jr., Robert J. NM162512
Williams, Maria W
1007-2-1 Hayden, Michael S. NM112654
...
Here is an example of what the report currently like:
Account No: Name(s) Clt Ref No:
1057-1-1 Hampton, Emily A. NM152653
1065-1-1 Hardesty Jr., Robert J. NM162512
Williams, Maria W
1007-2-1 Hayden, Michael S. NM112654
...
1028-1-1 Williams, David A. NM152632
1065-1-1 Hardesty Jr., Robert J. NM162512
Williams, Maria W
1035-1-1 Wolf, Christine M. NM152263
...
Notice that Account number 1065-1-1 was printed twice.
I looked at the Preview Data for the master pipeline and the 1065-1-1
Account row is in there twice. I have determined that the data is
duplicated only when there is an order by attached to the SQL statement and
futhermore only when the order by is based a a name field form the PERSON
table.
We are using the ppReportExplorer to handle all of our end user reporting
needs.
1) We created a New Report, switch to the Data tab, then selected the Query
Designer for the File | New... menu.
2) On the Tables tab I selected three tables and this is the resulting SQL
statement
SELECT DISTINCT DEBT_1.ACCT_ID, DEBT_1.CASE_ID,
DEBT_1.DEBT_NO,
DEBT_1.CLT_REF_NO,
DEBT_1.ORIG_PRINCIPAL,
DEBT_1.SERVICE_DATE,
DEBT_1.LIST_DATE
FROM DEBT DEBT_1
INNER JOIN DEBTOR_CASE_DEBT DEBTOR_CASE_DEBT_1 ON
(DEBTOR_CASE_DEBT_1.ACCT_ID = DEBT_1.ACCT_ID)
AND (DEBTOR_CASE_DEBT_1.CASE_ID = DEBT_1.CASE_ID)
AND (DEBTOR_CASE_DEBT_1.DEBT_NO = DEBT_1.DEBT_NO)
INNER JOIN PERSON PERSON_1 ON
(PERSON_1.PERSON_ID = DEBTOR_CASE_DEBT_1.PERSON_ID)
When I run the report or look at the data preview for the master
datapipline, all is fine, except that the user wants the report to be sorted
by the person name column. However, if I add a sort field to the SQL then
both the report and the data preview now have a duplicate records showing.
Here is the SQL Statement.
SELECT DISTINCT DEBT_1.ACCT_ID, DEBT_1.CASE_ID,
DEBT_1.DEBT_NO,
DEBT_1.CLT_REF_NO,
DEBT_1.ORIG_PRINCIPAL,
DEBT_1.SERVICE_DATE,
DEBT_1.LIST_DATE
FROM DEBT DEBT_1
INNER JOIN DEBTOR_CASE_DEBT DEBTOR_CASE_DEBT_1 ON
(DEBTOR_CASE_DEBT_1.ACCT_ID = DEBT_1.ACCT_ID)
AND (DEBTOR_CASE_DEBT_1.CASE_ID = DEBT_1.CASE_ID)
AND (DEBTOR_CASE_DEBT_1.DEBT_NO = DEBT_1.DEBT_NO)
INNER JOIN PERSON PERSON_1 ON
(PERSON_1.PERSON_ID = DEBTOR_CASE_DEBT_1.PERSON_ID)
ORDER BY PERSON_1.LAST_NAME
Notice that the only thing that has changed in the SQL statement is that an
ORDER BY field was added. Also notice that NO FIELD from the PERSON table
is returned in the result set.
NOTE: If I copy the above SQL statement with the order by clause and run it
in "Database WorkBench" or "IB Console" It will NOT return a duplicate
record for account number 1065-1-1? Is their a bug in vs. 6.03
DataPipeline?
Any help appeciated.
Michael Tuttle
Software Technologies, Inc.
Topeka, KS
This discussion has been closed.
Comments
1. If the DataView is linked to a master dataview, then some special linking
SQL is generated. To see this SQL, press Ctrl + left mouse button to view
the linking SQL (i.s. Magic SQL).
2. As a test try assigning the SQL to TQuery or TIBQuery depending upon
what type of database access your are using. That is what ReportBuilder is
doing internally.
--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
linking
This looks to be the problem. Why does Magic SQL do this to me?
I created a very simple 1 dataview report.
Here is the SQL statement from this report:
SELECT DISTINCT DEBT_1.ACCT_ID, DEBT_1.CASE_ID,
DEBT_1.DEBT_NO,
DEBT_1.CLT_REF_NO,
DEBT_1.ORIG_PRINCIPAL,
DEBT_1.SERVICE_DATE,
DEBT_1.LIST_DATE
FROM DEBT DEBT_1
INNER JOIN DEBTOR_CASE_DEBT DEBTOR_CASE_DEBT_1 ON
(DEBTOR_CASE_DEBT_1.ACCT_ID = DEBT_1.ACCT_ID)
AND (DEBTOR_CASE_DEBT_1.CASE_ID = DEBT_1.CASE_ID)
AND (DEBTOR_CASE_DEBT_1.DEBT_NO = DEBT_1.DEBT_NO)
INNER JOIN PERSON PERSON_1 ON
(PERSON_1.PERSON_ID = DEBTOR_CASE_DEBT_1.PERSON_ID)
ORDER BY PERSON_1.LAST_NAME
Now here is the Magic SQL statement and notice it added the
PERSON_1.LAST_NAME field, I guess because its in the order by?
SELECT DISTINCT DEBT_1.ACCT_ID, DEBT_1.CASE_ID,
DEBT_1.DEBT_NO,
DEBT_1.CLT_REF_NO,
DEBT_1.ORIG_PRINCIPAL,
DEBT_1.SERVICE_DATE,
DEBT_1.LIST_DATE,
PERSON_1.LAST_NAME
FROM DEBT DEBT_1
INNER JOIN DEBTOR_CASE_DEBT DEBTOR_CASE_DEBT_1 ON
(DEBTOR_CASE_DEBT_1.ACCT_ID = DEBT_1.ACCT_ID)
AND (DEBTOR_CASE_DEBT_1.CASE_ID = DEBT_1.CASE_ID)
AND (DEBTOR_CASE_DEBT_1.DEBT_NO = DEBT_1.DEBT_NO)
INNER JOIN PERSON PERSON_1 ON
(PERSON_1.PERSON_ID = DEBTOR_CASE_DEBT_1.PERSON_ID)
ORDER BY PERSON_1.LAST_NAME
So does this mean there is no way to keep the PERSON_1.LAST_NAME out of the
main SQL? as including this field causes it to return two records instead of
the one main records for the debt. I cannot show two records for the same
debt, I just simply need to show one debt, but all people associated with
this debt.
Any ideas?
report
have
PERSON
reporting
SQL
then
showing.
table
You can try downloading the RB 7 trial and testing to see whether the issue
persists. We have fixed many of these types of issues. There are still some
limitations when using the MagicSQL due to the complexity of supporting all
of the permutations of query linking.
--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
did not. So still looking for suggestion of what I might try to get this
resolved..
Mike
This example shows how to write code to create the links. Using this
technique the MagicSQL will not be generated. However the linking logic
still requires the detail data to be sorted. In the DataWorkspace you can
either manually edit the SQL to specify what you want or try using the query
tools to specify an order by.
http://www.digital-metaphors.com/tips/LinkEditSQLDatviews.zip
--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com