Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Truncated fields using Crystal Reports v9

Status
Not open for further replies.

infomania

Programmer
Oct 27, 2002
148
I am using CR 9 and a MySQL database (XP Pro OS). I have updated CR 9 to address MySQL database issues. I have constructed a SQL query and am running it from within the Command feature. The results I am getting indicate that string fields are getting arbitrarily truncated. For example I have a field in the database which is varchar 100. The SQL output sets the field as a String [5]. I am having similar difficulty with other string fields which may truncate at different lengths.

Here is the SQL:
SELECT
test.test_name
, test_score.test_date
, test_score.score
, user.user_last_name
, user.user_organization
, user.user_id
, user_group.group_name - varchar 100 is now String [5]
, user_group.group_id
, test_score.sco_status_code
, user.user_first_name
, test_score.version_no
, test_score.test_set_id
, test_score.test_id
, test_score.course_id
, course.course_name - varchar 100 is now String [26]
FROM table_link
INNER JOIN test_score ON table_link.table1_id = test_score.user_id
INNER JOIN user_group ON user_group.group_id = table_link.table2_id
INNER JOIN test ON test.test_id = test_score.test_id
INNER JOIN user ON user.user_id = test_score.user_id
LEFT JOIN course ON course.course_id = test_score.course_id
WHERE table_link.relationship = 'user_group'
AND table_link.cust_id = 1
AND table_link.table2_id = {?GroupName}

If I run a simple report with just the one table (user_group), the group_name has a type String [100]

Is there any way to stop the truncating? I cannot use the database wizard to construct the query, MySQL doesn't like the syntax CR 9 generates for outer joins. (not a problem with CR 10). Any LEFT JOINs have to be scripted in the Command window.

I tried doing a left(user_group.group_name,100) to match the MySQL data type. This increased the CR data type to String [17]. I need it all.

Thanks.... Infomania
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top