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
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