Re: are there limitations of SQL used in text driver via ADO

I have the following SQL query which I run from excel vba using ADO

SQL Query


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) as 'Equity+10',
Sum(case when scenario_Name = 'VB_-0.1' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Vol-10',
Sum(case when scenario_Name = 'VB_-0.05' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Vol-5',
Sum(case when scenario_Name = 'VB_0.05' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Vol+5',
Sum(case when scenario_Name = 'VB_0.1' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Vol+10'

From
filename.csv

Group By
Portfolio


VBA Code


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

cnx.CursorLocation = adUseClient
cnx.Open "DSN=ReechRisk"
'cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & RiskFileloc
& _
";Extended Properties='text;HDR=YES;FMT=Delimited'"


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

rs.Open strSQL, cnx, adOpenDynamic, adLockBatchOptimistic, adCmdText


If I remove the case statements in the sql and just have a simple select
query then it seems to work. I have other pieces of code which are similar to
this and it works just fine, so I am not sure if the sql used in odbc Text
driver is not compatable..

The error I get when I run this is

Run time error '-2147217900 (80040e14)':
[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)'


"Stephen Howe" wrote:


"KaptainKidd" <KaptainKidd@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1B708828-31A0-4562-A38E-60BAE5863FAF@xxxxxxxxxxxxxxxx
I am using a select.. case.. type query on a text file, I am connecting
via
ADODB.connection but keep getting an error when I try to open the
recordset

any ideas

Post the error your getting and your Connection/Recordset code

Stephen Howe



.

Relevant Pages

  • Re: Equivalent of Get Next Extended to retrieve only unique keys?
    ... The only real "extra" stuff is pertaining to SQL query optimization. ... Bill Bach wrote: ... Retaining the ability to filter on multiple>> items would probably ...
    (comp.databases.btrieve)
  • Re: Setting a Default Value
    ... the make the Sql call from within Application_Start in Global.asax. ... the query string value each time I needed to ... going to assign the default value through a database query. ... run the following SQL query: select * from table where x = 1 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: MultiSelect Property
    ... > For Each vItm In Me!lbSelectContract.ItemsSelected ... > Dim stCriteria As String ... >> SQL query). ... >> the multiselect list box appears in the SQL query. ...
    (microsoft.public.access.devtoolkits)
  • Re: Report in VBA - FilterName or WhereCondition
    ... strWhere clause as you advise. ... If you use un-bound controls in your sql query, ... strWhere in the filter parameter of the report, the defense is that you can ...
    (microsoft.public.access.modulesdaovba)
  • Noob question on how to use an insert query in a command button
    ... Normally i would do this type of project in PHP and SQL but the boss ... Dim dbLocation 'Location of our Access database file ... Set objADO = CreateObject'Create an ADO connection ... objADO.Execute 'Execute this SQL query ...
    (microsoft.public.access.forms)