Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MyODBC & Excel VBA - Too long query ?
Hello,
I've tried to record a macro in ms excel to connect to mySQL server using myODBC driver, then execute SQL query and return the data to excel sheet. When I run the query through MS Query, it works fine. But when I try to use this query in macro, it ends with run-time error 13: type mismatch. I've also tried truncating lines to 127 chars with no positive results. My SQL query looks like this: ( SELECT '4',CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as PID, trn.created_date as date_time,slacko.value as priority, acv.description, att.attribute_name as Operation, acv.old_value value_from, acv.new_value value_to, concat(user.first_name,' ' ,user.last_name ) as Person,issuet.value as IssueType FROM scarab_issue iss JOIN scarab_activity acv on iss.issue_id = acv.issue_id JOIN scarab_transaction trn ON acv.transaction_id = trn.transaction_id left outer join scarab_issue_attribute_value slacko on slacko.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 7 ) LEFT OUTER JOIN scarab_attribute att on att.attribute_id = acv.attribute_id JOIN turbine_user user ON trn.created_by = user.user_id left join scarab_issue_type itype on iss.type_id = itype.issue_type_id left outer join scarab_activity ipr on iss.issue_id = ipr.issue_id and ipr.attribute_id = 112 left outer join scarab_issue_attribute_value issuet ON issuet.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 112 ) WHERE iss.module_id = 101 ) union ( SELECT '5', CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as PID, trn.created_date as date_time, slacko.value as priority, null, 'To workgroup', null, area.value as value_to, null,null FROM scarab_issue iss JOIN scarab_activity acv on iss.issue_id = acv.issue_id and acv.activity_id = (SELECT min( activity_id ) from scarab_activity where acv.issue_id = issue_id ) JOIN scarab_transaction trn ON acv.transaction_id = trn.transaction_id LEFT OUTER JOIN scarab_issue_attribute_value status on status.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 3 ) left outer join scarab_issue_attribute_value issuet ON issuet.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 112 ) left outer join scarab_issue_attribute_value cldate ON cldate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 111 ) left outer join scarab_issue_attribute_value asdate ON asdate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 108 ) left outer join scarab_issue_attribute_value cmdate ON cmdate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 109 ) left outer join scarab_issue_attribute_value area on area.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 12 ) left outer join scarab_issue_attribute_value slacko on slacko.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 7 ) WHERE iss.module_id = 101 ) Any solution to this problem ? -- Peter M. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MyODBC & Excel VBA - Too long query ?
Peter
I think you need to split up your SQL statement into an array where each element has no more than 255 characters. Excel (or MSQuery, I'm not sure which) will then join all the elements together into one long SQL statement. You can run the string variable that contains your Sql through this function Function StringToArray(sSQL As String) As Variant Dim aTemp() As String Dim i As Long, j As Long ReDim aTemp(1 To (Len(sSQL) \ 255) + 1) j = 1 For i = 1 To Len(sSQL) Step 255 aTemp(j) = Mid(sSQL, i, 255) j = j + 1 Next i StringToArray = aTemp End Function -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Peter M. wrote: Hello, I've tried to record a macro in ms excel to connect to mySQL server using myODBC driver, then execute SQL query and return the data to excel sheet. When I run the query through MS Query, it works fine. But when I try to use this query in macro, it ends with run-time error 13: type mismatch. I've also tried truncating lines to 127 chars with no positive results. My SQL query looks like this: ( SELECT '4',CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as PID, trn.created_date as date_time,slacko.value as priority, acv.description, att.attribute_name as Operation, acv.old_value value_from, acv.new_value value_to, concat(user.first_name,' ' ,user.last_name ) as Person,issuet.value as IssueType FROM scarab_issue iss JOIN scarab_activity acv on iss.issue_id = acv.issue_id JOIN scarab_transaction trn ON acv.transaction_id = trn.transaction_id left outer join scarab_issue_attribute_value slacko on slacko.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 7 ) LEFT OUTER JOIN scarab_attribute att on att.attribute_id = acv.attribute_id JOIN turbine_user user ON trn.created_by = user.user_id left join scarab_issue_type itype on iss.type_id = itype.issue_type_id left outer join scarab_activity ipr on iss.issue_id = ipr.issue_id and ipr.attribute_id = 112 left outer join scarab_issue_attribute_value issuet ON issuet.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 112 ) WHERE iss.module_id = 101 ) union ( SELECT '5', CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as PID, trn.created_date as date_time, slacko.value as priority, null, 'To workgroup', null, area.value as value_to, null,null FROM scarab_issue iss JOIN scarab_activity acv on iss.issue_id = acv.issue_id and acv.activity_id = (SELECT min( activity_id ) from scarab_activity where acv.issue_id = issue_id ) JOIN scarab_transaction trn ON acv.transaction_id = trn.transaction_id LEFT OUTER JOIN scarab_issue_attribute_value status on status.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 3 ) left outer join scarab_issue_attribute_value issuet ON issuet.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 112 ) left outer join scarab_issue_attribute_value cldate ON cldate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 111 ) left outer join scarab_issue_attribute_value asdate ON asdate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 108 ) left outer join scarab_issue_attribute_value cmdate ON cmdate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 109 ) left outer join scarab_issue_attribute_value area on area.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 12 ) left outer join scarab_issue_attribute_value slacko on slacko.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 7 ) WHERE iss.module_id = 101 ) Any solution to this problem ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MyODBC & Excel VBA - Too long query ?
I remember this 255 character trouble when I started with SQL in Excel via
MS Query. There is no problem with SQL statements longer than 255 characters when you run your SQL with ADO and it may be worth it to start using this rather than MS Query. RBS "Peter M." wrote in message om... Hello, I've tried to record a macro in ms excel to connect to mySQL server using myODBC driver, then execute SQL query and return the data to excel sheet. When I run the query through MS Query, it works fine. But when I try to use this query in macro, it ends with run-time error 13: type mismatch. I've also tried truncating lines to 127 chars with no positive results. My SQL query looks like this: ( SELECT '4',CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as PID, trn.created_date as date_time,slacko.value as priority, acv.description, att.attribute_name as Operation, acv.old_value value_from, acv.new_value value_to, concat(user.first_name,' ' ,user.last_name ) as Person,issuet.value as IssueType FROM scarab_issue iss JOIN scarab_activity acv on iss.issue_id = acv.issue_id JOIN scarab_transaction trn ON acv.transaction_id = trn.transaction_id left outer join scarab_issue_attribute_value slacko on slacko.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 7 ) LEFT OUTER JOIN scarab_attribute att on att.attribute_id = acv.attribute_id JOIN turbine_user user ON trn.created_by = user.user_id left join scarab_issue_type itype on iss.type_id = itype.issue_type_id left outer join scarab_activity ipr on iss.issue_id = ipr.issue_id and ipr.attribute_id = 112 left outer join scarab_issue_attribute_value issuet ON issuet.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 112 ) WHERE iss.module_id = 101 ) union ( SELECT '5', CONCAT( iss.ID_PREFIX, RIGHT(concat('000',iss.id_count), 4)) as PID, trn.created_date as date_time, slacko.value as priority, null, 'To workgroup', null, area.value as value_to, null,null FROM scarab_issue iss JOIN scarab_activity acv on iss.issue_id = acv.issue_id and acv.activity_id = (SELECT min( activity_id ) from scarab_activity where acv.issue_id = issue_id ) JOIN scarab_transaction trn ON acv.transaction_id = trn.transaction_id LEFT OUTER JOIN scarab_issue_attribute_value status on status.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 3 ) left outer join scarab_issue_attribute_value issuet ON issuet.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 112 ) left outer join scarab_issue_attribute_value cldate ON cldate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 111 ) left outer join scarab_issue_attribute_value asdate ON asdate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 108 ) left outer join scarab_issue_attribute_value cmdate ON cmdate.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 109 ) left outer join scarab_issue_attribute_value area on area.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 12 ) left outer join scarab_issue_attribute_value slacko on slacko.value_id = (SELECT max( value_id ) fROM scarab_issue_attribute_value where issue_id = iss.issue_ID AND attribute_id = 7 ) WHERE iss.module_id = 101 ) Any solution to this problem ? -- Peter M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query Wizard Long Delay in Excel 2003 | Setting up and Configuration of Excel | |||
How can I use a long URL in a web query? | Excel Discussion (Misc queries) | |||
How to enter a long address to new web query | Excel Discussion (Misc queries) | |||
Excel Database Query String Too Long | Excel Discussion (Misc queries) | |||
Excel Database Query String Too Long | Excel Programming |