Question SQL in Eviews

[MSQ1]

Hi,
I'm trying to import time-series data from our SQL database into Eviews using wfopen(type=odbc)... It works fine until I add more than 9 variables (plus date variable). Then I get the error message "unmatched parenthesis". I can't find anything wrong in the SQL question (works fine in SQL Management Studio) so I wonder if there is any limits in Eviews on the SQL question, such as number of variables, length, complexity etc.
//Maria

[EViews Chris]

There shouldn't really be any limit based on the number of variables.

Could you post the complete command line for wfopen that you are using?

Could you also run a quick test whether the problem can be fixed by dropping just one particular variable from the query (the obvious possibility being the 10th variable?). I'm wondering if the problem is with fetching a particular variable rather than the total number of variables being fetched at once.

Chris

[MSQ1]

Hi,

Thank you for the reply. I've tried adding the variables one by one with different variables as the 11th but no luck. Posting the code that works in Eviews. Any other ideas on what I could try to make it work? Btw, is there a way to write the SQL code with line breaks - with long codes it's very difficult to read this way?

//Maria

wfopen(type=odbc) "tycheodbc" "select DateValue = index1.tmDate, WorldIndex = index1.fClose, SP500 = index2.fClose, Asia = index3.fClose, Euro = index4.fClose, Value = index5.fClose, Growth = index6.fClose, Small = index7.fClose, EUR = fx1.fCloseFxRate from (select i.strIndexShortName, hsi.tmDate, hsi.fClose from HS_Indexes as hsi inner join PaperIndexes as i on i.nIndexId = hsi.nIndexId where hsi.nIndexId = 43707) as index1 left join (select i.strIndexShortName, hsi.tmDate, hsi.fClose from HS_Indexes as hsi inner join PaperIndexes as i on i.nIndexId = hsi.nIndexId where hsi.nIndexId = 3980) as index2 on index2.tmDate = index1.tmDate left join (select i.strIndexShortName, hsi.tmDate, hsi.fClose from HS_Indexes as hsi inner join PaperIndexes as i on i.nIndexId = hsi.nIndexId where hsi.nIndexId = 48450) as index3 on index3.tmDate = index1.tmDate left join (select i.strIndexShortName, hsi.tmDate, hsi.fClose from HS_Indexes as hsi inner join PaperIndexes as i on i.nIndexId = hsi.nIndexId where hsi.nIndexId = 44134) as index4 on index4.tmDate = index1.tmDate left join (select i.strIndexShortName, hsi.tmDate, hsi.fClose from HS_Indexes as hsi inner join PaperIndexes as i on i.nIndexId = hsi.nIndexId where hsi.nIndexId = 47607) as index5 on index5.tmDate = index1.tmDate left join (select i.strIndexShortName, hsi.tmDate, hsi.fClose from HS_Indexes as hsi inner join PaperIndexes as i on i.nIndexId = hsi.nIndexId where hsi.nIndexId = 47608) as index6 on index6.tmDate = index1.tmDate left join (select i.strIndexShortName, hsi.tmDate, hsi.fClose from HS_Indexes as hsi inner join PaperIndexes as i on i.nIndexId = hsi.nIndexId where hsi.nIndexId = 47609) as index7 on index7.tmDate = index1.tmDate left join (select x.strCurrency, x.tmDate, x.fCloseFxRate from XP_CloseFxRates as x inner join Currencies c on c.strCurShortName = x.strCurrency where 1 = 1 and x.nBranchId = 3483 and x.strCurrency = 'EUR' ) as fx1 on fx1.tmDate = index1.tmDate order by index1.tmDate"

[EViews Glenn]

I'll let Chris continue deal with the SQL issue, but to answer your "by-the-way", as described in the manual, the continuation character for program lines is the "_". So you can break up your command by making the last character of each line the underscore...

[EViews Chris]

I'm still not sure what is happening I'm afraid - I don't think the column count itself should be a problem.

I wonder whether something is going on where the text of the query is getting chewed up somewhere so the query is incomplete and the parentheses appear unbalanced.

Would it be possible for you to set up the same query inside SQL server as a SQL server View and then just fetch it by:

wfopen(type=odbc) "tycheodbc" myview

This should provide some idea of whether it is the reading of the data itself or something to do with the passing around of the query text that is causing the problem.

Similarly, do you know whether is it possible to fetch more than 11 columns from a table when using a simpler (shorter) query? I tried something like this at my end and it seemed to be fine, but there could be something that I'm doing that isn't quite the same as your setup.

Chris

[MSQ1]

It works when I set up the query inside the SQL server as a View and then fetch it. I can then get the full dataset with 50 variables into Eviews without problems. So I guess it has something to do with Eviews not being able to read the full query correctly. But great to have a way to make it work anyway.

My question on line breaks was on breaks in the specific SQL query not in the general programming language in Eviews. But if I don't write the SQL query in Eviews that problem is solved also.

Thanks.

[EViews Chris]

OK. We'll try to replicate the problem at our end with using a very long SQL query string and see if there's anything we can do to fix things.

By the way, SQL Server stored procedures can be quite useful in cases where you have a long query where most of the query text is the same each time but a few parameters vary.

You may like to have a quick look at:

http://forums.eviews.com/viewtopic.php? ... ure#p15741

for a discussion of the syntax of invoking a SQL Server stored procedure over ODBC.

Thanks for your help.

Chris

[EViews Chris]

It looks like the SQL query text is currently being truncated at 2000 characters.

I'll try to get this fixed for you.

By the way, dropping the double quotes around the SQL query text string may also work in your case.

Chris