[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Type Mismatch error when implementing SQL to the Querytables.add property!!

Posted on 2007-10-18
6
Medium Priority
?
439 Views
Last Modified: 2013-12-07
Team,
VBA related!!

I have a pretty exhaustive sql statement which I need to implement into my code. For some reason, when I used the macro recorder, it did not seem to work because it cut my sql query to a limit (since it was too long). To remedy the situation, I created a bunch of string concatanated variables and figured this might be a better way to implement a solution. With this said, I am now running into a type mismatch at the .CommandText line. How can I make this work? Please help!!

Dim text5 As String


text1 = "Select count(*) as latecount from (Select distinct B.determination, B.stud_id from (Select alias_tree.stud_id as stud_id, case when alias_table.compl_dte > greatest(alias_table.rev_dte,alias_table.assgn_dte) + alias_table.init_pd then 'Late' when alias_table.compl_dte < greatest (alias_table.rev_dte,alias_table.assgn_dte) + alias_table.init_pd then 'On Time'end as determination from "
text2 = text1 & "(SELECT pa_stud_qual_cpnt.stud_id as stud_id, pa_stud_qual_cpnt.rev_dte as rev_dte, pa_stud_qual_cpnt.compl_dte as compl_dte, pa_stud_qual_cpnt.assgn_dte as assgn_dte, pa_cpnt.init_pd as init_pd "
text3 = text2 & "FROM pa_stud_qual_cpnt, pa_student, pa_cpnt WHERE pa_stud_qual_cpnt.retrng_int IS NULL AND emp_stat_id in ('A','L','P') AND pa_stud_qual_cpnt.stud_id = pa_student.stud_id AND pa_stud_qual_cpnt.compl_dte "
text4 = text3 & "BETWEEN TO_DATE ('01'|| (SELECT CASE WHEN (  TO_CHAR  (SYSDATE,'MM' )-1) < 10  THEN    '0'|| (  TO_CHAR (SYSDATE,'MM') - 1) WHEN (  TO_CHAR(SYSDATE,'MM') - 1) > 10 THEN (TO_CHAR(  TO_CHAR(SYSDATE,'MM') - 1)) END FROM DUAL) || (CASE WHEN TO_CHAR(SYSDATE,'mm') = 01 THEN TO_CHAR(TO_CHAR(SYSDATE,'yyyy')- 1) WHEN TO_CHAR(SYSDATE,'mm') <> 01 THEN TO_CHAR(TO_CHAR(SYSDATE,'yyyy')) END),'dd-mm--yyyy') AND TO_DATE('01'|| TO_CHAR(SYSDATE,'MM-YYYY') || ('00:00:00'),'dd-mm-yyyy HH24:Mi:SS') AND pa_cpnt.NOTACTIVE = 'N' AND pa_cpnt.cpnt_id = pa_stud_qual_cpnt.cpnt_id ORDER BY pa_stud_qual_cpnt.stud_id, pa_stud_qual_cpnt.compl_dte) alias_table, "
text5 = text4 & "(Select A.tree, A.stud_id from (select ltrim(sys_connect_by_path(fname || ' ' || lname,'<--'),'--<') as tree, stud_id as stud_id from pa_student where emp_stat_id  in ('A','L','P') start with stud_id ='10190346' connect by prior stud_id = super) A where A.tree like '%<--%') alias_tree where alias_table.stud_id = alias_tree.stud_id) B where B.determination = 'Late')"

    With ActiveSheet.QueryTables.Add(Connection:= _
        "OLEDB;Provider=MSDAORA.1;Password=XXXX;User ID=XXXX;Data Source=atmsp" _
        , Destination:=Range("A1"))
        .CommandType = xlCmdSql
        .CommandText = Array(text5)
        .Name = "atmsp_hierarchy_conn"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "\\Temsrc03.oneabbott.com\piyush\Project #2 Direct Report Metrics\atmsp_hierarchy_conn.odc"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Please note that this works for smaller SQL statements!!!
0
Comment
Question by:piyushdabomb
  • 4
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Mikkk
ID: 20105773
Did you tried simply with
.CommandText = text5
0
 

Author Comment

by:piyushdabomb
ID: 20105785
Yeah I did. What I noticed is that a string can not contain more than 256 characters.

I used text1 = "..."
text2 = text1 & "..."

.CommandText = (text2).

Utilizing this method bypasses the type mismatch but then I get an error on this line:

Refresh BackgroundQuery:=False

PLEASE help!!
0
 
LVL 8

Accepted Solution

by:
Mikkk earned 2000 total points
ID: 20105828
My guess is that there is a 255 character limitation that this overcomes.
If you choose to use arrays, the compiler will build the string from all the elements in the array in whatever order they are.
So, create different variables of maximiun 254  chars each one:
create an array
dim sentence(100) as string
iterate the text5 and move each 254 chars to a new array
sentence(1) = mid(text5,1,254)
sentence(2) = mid(text5,255,254)
... (do a bucle..)
then change:
.CommandText = sentence

that should work!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:piyushdabomb
ID: 20105850
I will try that at work tomorrow and keep you posted!!
0
 

Author Comment

by:piyushdabomb
ID: 20159763
Hi Mikkk,

Unfortunately its not working. Please help!! I could be doing something wrong. Take a look at my procedue

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/18/2007 by paggarwa
'

Dim text1 As String
Dim text2 As String
Dim text3 As String
Dim text4 As String
Dim text5 As String

text1 = "Select count(*) as latecount from (Select distinct B.determination, B.stud_id from (Select alias_tree.stud_id as stud_id, case when alias_table.compl_dte > greatest(alias_table.rev_dte,alias_table.assgn_dte) + alias_table.init_pd then 'Late' when alias_table.compl_dte < greatest (alias_table.rev_dte,alias_table.assgn_dte) + alias_table.init_pd then 'On Time' end as determination from "
text2 = "(SELECT pa_stud_qual_cpnt.stud_id as stud_id, pa_stud_qual_cpnt.rev_dte as rev_dte, pa_stud_qual_cpnt.compl_dte as compl_dte, pa_stud_qual_cpnt.assgn_dte as assgn_dte, pa_cpnt.init_pd as init_pd "
text3 = "FROM pa_stud_qual_cpnt, pa_student, pa_cpnt WHERE pa_stud_qual_cpnt.retrng_int IS NULL AND emp_stat_id in ('A','L','P') AND pa_stud_qual_cpnt.stud_id = pa_student.stud_id AND pa_stud_qual_cpnt.compl_dte "
text4 = "BETWEEN TO_DATE ('01'|| (SELECT CASE WHEN (  TO_CHAR  (SYSDATE,'MM' )-1) < 10  THEN    '0'|| (  TO_CHAR (SYSDATE,'MM') - 1) WHEN (  TO_CHAR(SYSDATE,'MM') - 1) > 10 THEN (TO_CHAR(  TO_CHAR(SYSDATE,'MM') - 1)) END FROM DUAL) || (CASE WHEN TO_CHAR(SYSDATE,'mm') = 01 THEN TO_CHAR(TO_CHAR(SYSDATE,'yyyy')- 1) WHEN TO_CHAR(SYSDATE,'mm') <> 01 THEN TO_CHAR(TO_CHAR(SYSDATE,'yyyy')) END),'dd-mm--yyyy') AND TO_DATE('01'|| TO_CHAR(SYSDATE,'MM-YYYY') || ('00:00:00'),'dd-mm-yyyy HH24:Mi:SS') AND pa_cpnt.NOTACTIVE = 'N' AND pa_cpnt.cpnt_id = pa_stud_qual_cpnt.cpnt_id ORDER BY pa_stud_qual_cpnt.stud_id, pa_stud_qual_cpnt.compl_dte) alias_table, "
text5 = "(Select A.tree, A.stud_id from (select ltrim(sys_connect_by_path(fname || ' ' || lname,'<--'),'--<') as tree, stud_id as stud_id from pa_student where emp_stat_id  in ('A','L','P') start with stud_id ='10190346' connect by prior stud_id = super) A where A.tree like '%<--%') alias_tree where alias_table.stud_id = alias_tree.stud_id) B where B.determination = 'Late')"

Dim testing(100) As String

testing(1) = text1
testing(2) = text2
testing(3) = text3
testing(4) = text4
testing(5) = text5
    With ActiveSheet.QueryTables.Add(Connection:= _
        "OLEDB;Provider=MSDAORA.1;Password=x;User ID=y;Data Source=source" _
        , Destination:=Worksheets("Sheet1").Range("A1"))
        .CommandType = xlCmdSql
        .CommandText = testing
        .Name = "atmsp_hierarchy_conn"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "\\Temsrc03.oneabbott.com\piyush\Project #2 Direct Report Metrics\atmsp_hierarchy_conn.odc"
       .Refresh BackgroundQuery:=False
    End With
End Sub

At the line << .CommandText = testing >>, I get the following error message:

Runtime error '5'
Invalid Procedure Call or Argument.

--------

Apologies for the late delay!. Please help with this error. I've spent numerous hours trying to figure this out and I can't!
0
 

Author Comment

by:piyushdabomb
ID: 20159772
What is awkward, however at the same line << .CommandText = testing >> , if I replace it with  <<.CommandText = testing (1)>> it accepts it (obviously its wrong though).
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month19 days, 10 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question