Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 29th, 2006, 06:15 AM
Authorized User
 
Join Date: Mar 2006
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problems with Text driver and SQL

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
 
Old March 31st, 2006, 10:19 AM
Authorized User
 
Join Date: Mar 2006
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 9th, 2006, 01:48 AM
Registered User
 
Join Date: Apr 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 10th, 2006, 05:33 AM
Authorized User
 
Join Date: Mar 2006
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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







Similar Threads
Thread Thread Starter Forum Replies Last Post
ODBC to text driver mnolting Word VBA 0 October 6th, 2008 06:52 PM
Microsoft Text Driver Use ronhawker SQL Server ASP 15 December 20th, 2005 06:12 PM
"Driver not found" e message, el problems cruzer JSP Basics 2 December 19th, 2004 02:13 AM
Microsoft Text Driver ??? x23 SQL Server ASP 0 November 5th, 2004 05:54 AM
Insert and update problems with OLEDB driver Cos Classic ASP Databases 2 September 24th, 2003 01:52 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.