Strange Problem with Joined Report ('c'<>'c')
Hi All,
Using an old version of RB. I think it's 6.03. I have an end user created
report and there is an inner join between two tables. There is nothing else
involved in this report other than some search criteria. There is no code
added.
We have joined these two tables before in other reports with out any
problems.
I ran it with the SQL Monitor on and noticed that it is running the
following statement, several times on each table:
select * from members where 'c' <> 'c'
select * from transactions where 'c' <> 'c'
Seems to be running it 4 times for each table. This is where the slowdown
is, but I don't know where those select statements are coming from.
Here is the text on the SQL tab, any suggestions would be appreciated.
SELECT MEMBERS_1.FIRST_NAME,
MEMBERS_1.FIRST_LAST, MEMBERS_1.ADDRESS,
MEMBERS_1.CITY_STATE_ZIP,
MEMBERS_1.RETURN_DATE,
MEMBERS_1.CURRENT_BALANCE,
TRANSACTIONS_1.DATE_TO_APPLY,
MEMBERS_1.HOME_PHONE,
MEMBERS_1.CELL_BEEPER,
TRANSACTIONS_1.NO_CHANGE_AMOUNT,
MEMBERS_1.CASH_OUT,
MEMBERS_1.MEMBER_NUMBER,
MEMBERS_1.NEXT_BILLING_DATE
FROM MEMBERS MEMBERS_1
INNER JOIN TRANSACTIONS TRANSACTIONS_1 ON
(TRANSACTIONS_1.MEMBER_ID = MEMBERS_1.UID)
WHERE
( TRANSACTIONS_1.DATE_TO_APPLY BETWEEN '03/08/2008 00:00:00' AND '04/07/2008
00:00:00' )
AND ( MEMBERS_1.CURRENT_BALANCE >= 1 )
AND ( TRANSACTIONS_1.ITEM_NUMBER IN ('10010','10011') )
AND ( MEMBERS_1.HOME_CLUB = 'LPA' )
AND ( MEMBERS_1.DELETION_DATE IS NULL )
ORDER BY MEMBERS_1.LAST_NAME, MEMBERS_1.FIRST_NAME
Using an old version of RB. I think it's 6.03. I have an end user created
report and there is an inner join between two tables. There is nothing else
involved in this report other than some search criteria. There is no code
added.
We have joined these two tables before in other reports with out any
problems.
I ran it with the SQL Monitor on and noticed that it is running the
following statement, several times on each table:
select * from members where 'c' <> 'c'
select * from transactions where 'c' <> 'c'
Seems to be running it 4 times for each table. This is where the slowdown
is, but I don't know where those select statements are coming from.
Here is the text on the SQL tab, any suggestions would be appreciated.
SELECT MEMBERS_1.FIRST_NAME,
MEMBERS_1.FIRST_LAST, MEMBERS_1.ADDRESS,
MEMBERS_1.CITY_STATE_ZIP,
MEMBERS_1.RETURN_DATE,
MEMBERS_1.CURRENT_BALANCE,
TRANSACTIONS_1.DATE_TO_APPLY,
MEMBERS_1.HOME_PHONE,
MEMBERS_1.CELL_BEEPER,
TRANSACTIONS_1.NO_CHANGE_AMOUNT,
MEMBERS_1.CASH_OUT,
MEMBERS_1.MEMBER_NUMBER,
MEMBERS_1.NEXT_BILLING_DATE
FROM MEMBERS MEMBERS_1
INNER JOIN TRANSACTIONS TRANSACTIONS_1 ON
(TRANSACTIONS_1.MEMBER_ID = MEMBERS_1.UID)
WHERE
( TRANSACTIONS_1.DATE_TO_APPLY BETWEEN '03/08/2008 00:00:00' AND '04/07/2008
00:00:00' )
AND ( MEMBERS_1.CURRENT_BALANCE >= 1 )
AND ( TRANSACTIONS_1.ITEM_NUMBER IN ('10010','10011') )
AND ( MEMBERS_1.HOME_CLUB = 'LPA' )
AND ( MEMBERS_1.DELETION_DATE IS NULL )
ORDER BY MEMBERS_1.LAST_NAME, MEMBERS_1.FIRST_NAME
This discussion has been closed.
Comments
Depending on which database you are connecting to and which connectivity you
are using, ReportBuilder uses the SQL statements you mention below to
retrieve the schema of a table. These queries should be used for any report
you are designing at runtime with DADE.
Which database and connectivity are you using?
I believe this has been optimized over time for later releases. You might
try downloading the trial edition of the latest version of ReportBuilder and
see if that helps the issue.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com