Problems with Text driver and SQL

jrogers · March 29th, 2006, 06:15 AM

I am having probelems importing a csv file into Excel VBA using ADO

my VBA code is

Code:

Sub StressAnalysis()


    Dim cnx             As ADODB.Connection
    Dim rs              As ADODB.Recordset
    Dim strSQL          As String
    Dim StrFileName     As String
    Dim StrRiskFile     As String
    Dim RunDate         As Date

    Set cnx = New ADODB.Connection
    Set rs = New ADODB.Recordset

    RunDate = #2/28/2006#
    StrFileName = "StressAnalysis.sql"
    StrRiskFile = "Scenario" & Format(RunDate, "YYYYMMDD") & ".csv"
    cnx.CursorLocation = adUseClient
    'cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & RiskFileloc & _
            ";Extended Properties='text;HDR=YES;FMT=Delimited'"

    cnx.Open "DSN=ReechRisk"

    ' Load up sql from file
    Open SqlFileLoc & StrFileName For Input As #1
    Do Until EOF(1)
        strSQL = strSQL & Input(1, #1)
    Loop
    Close #1

    'enter variables
    strSQL = Replace(strSQL, "[FileName]", StrRiskFile)

    rs.Open strSQL, cnx, adOpenDynamic, adLockBatchOptimistic, adCmdText
    Range("A1").CopyFromRecordset rs
    rs.Close
    cnx.Close
    Set rs = Nothing
    Set cnx = Nothing
End Sub

I use this code for alot of importing but not via text drivers (so this may be the problem)

the sql code being used which is saved in a file called "StressAnalysis.sql", it breaks on the line in red

sql code is

Code:

select
       Portfolio,
       Sum(case when scenario_Name = 'EB_-0.1' then scenario_MV-Scenario_Stressed_Value else null end) as 'Equity-10',
       Sum(case when scenario_Name = 'EB_0.1' then scenario_MV-Scenario_Stressed_Value else null end) 'Equity+10',
       Sum(case when scenario_Name = 'VB_-0.1' then scenario_MV-Scenario_Stressed_Value else null end) 'Vol-10',
       Sum(case when scenario_Name = 'VB_-0.05' then scenario_MV-Scenario_Stressed_Value else null end) 'Vol-5',
       Sum(case when scenario_Name = 'VB_0.05' then scenario_MV-Scenario_Stressed_Value else null end) 'Vol+5',
       Sum(case when scenario_Name = 'VB_0.1' then scenario_MV-Scenario_Stressed_Value else null end) 'Vol+10'

From
       [FileName]

Group By
      Portfolio

error message is

Quote:

quote:Run time error '-2147217900 (80040e14)':

Quote:

[Microsoft][ODBC Text Driver] Syntax error (missing operator) in query expression
'Sum(case when scenario_name = 'EB_-0.1' then scenario_mv-Scenario_stressed_Value else null end)'

Sorry its such a long post, But I am completely at a loss

jrogers · March 31st, 2006, 10:19 AM

Resolved.. cannot use CASE statement via text driver, you have to use SWITCH

sssyed_in · April 9th, 2006, 01:48 AM

Could you please an example of using Switch statement in Text Driver Sql?

jrogers · April 10th, 2006, 05:33 AM

Sure.. I changed my code above to

Code:

sum(switch(scenario_Name = 'EB_-0.1',Scenario_Stressed_Value-scenario_MV)),
sum(switch(scenario_Name = 'EB_0.1',Scenario_Stressed_Value-scenario_MV)),
sum(switch(scenario_Name = 'VB_-0.1',Scenario_Stressed_Value-scenario_MV)),
sum(switch(scenario_Name = 'VB_-0.05',Scenario_Stressed_Value-scenario_MV)),
sum(switch(scenario_Name = 'VB_0.05',Scenario_Stressed_Value-scenario_MV)),
sum(switch(scenario_Name = 'VB_0.1',Scenario_Stressed_Value-scenario_MV))