Digital Workshop

I have an Excel database. I was using database actions, which were OK to a point, but the queries got too complex, so now I have changed to scripting and getting out the 'Teach yourself SQL in 10 min' book!

This is difficult to explain.....My database is a list of tests done on equipment. There are many different types of tests, and many different types of equipment. But the database is very 'free-form'. There are 4 columns for the tests and 12 colums for the parts (Part_Tested_1, Part_Tested_2....etc), so the search has to extract if a piece of equipment has been tested to a particular test.
The excel sheet is called ECLIPSE_EMC
the search for a part is


then I would need to AND that to a similar chunk of code to find the test, but in order to get the correct priority of AND and OR in the SQL, it gets very very messy.
So I thought of creating a VIEW, however Opus doesn't like the code.
To test, I simplified the query to this:-



But I keep getting function not found, so my syntax is incorrect or it can't do a create view??
Has anyone used CREATE VIEW??

Hi Sandy,

One thing you can try very easily: CREATE VIEW IF NOT EXISTS viewname...


It is very possible the cause of the problem (error msg) resides somewhere in the code or syntax before your SQL query. You may find this post has some interesting reading (though admittedly lacking specifics at times and also hard to follow). viewtopic.php?f=4&t=3044

Thanks Lar,
Unfortunately it didn't help. If I remove "CREATE VIEW SandyList AS" the query works fine. I have tried lots of other options, but still the same error.
I checked the link.....I remember those problems, but fortunately my Opus has been rock stable (touch wood) for a long time. Everything else in the publication is fine, but as soon as I try to CREATE VIEW....function not found.

Sandy

Hi Sandyn,

You probably need one of the DW programmers to answer this as my understanding is SQL centred using Crystal or Toad but from what I gather there are different method of connecting to the database including ODBC; DAO and ADO. I'd hazard a guess that Opus uses the ODBC APIs which means that CREATE VIEW will fail as I believe it needs to use ADO. I might be completely wrong...

However, towards a solution, I not completely sure about your data structure but is it possible to introduce an MS Access database. This could be used to link to the Excel file(s) and then you can create a query that organises the data into an accessible format.

Alternatively, you can create a record set in Opus and loop through this whilst running the second query.

It all depends on how the data is structured and what you're actually trying to achieve.

Mack

Is it Opus that's saying function not found or is it the database? If it's a database error (and Opus just passes the SQL to the database), it should appear in the database section of the script console.

Is it different if you do this:



Also I wonder if you can do it all in one command:

Thanks Mack, Thanks Dave.

I didn't realise there was a database section in the script console!!

Nothing suggested worked. The error reported in the database script console is
CREATE VIEW SandyList AS SELECT * FROM [ECLIPSE_EMC$]; SELECT * FROM SandyList;
HY000: fffffc17: [Microsoft][ODBC Excel Driver] Operation is not supported for this type of object.

It sounds as if the implementation of SQL doesn't support Create View?? or is it still possible that a syntax error would throw up this error?
I don't think the statement should have any brackets, inverted commas, +, $ or whatever round the view name. I have tried adding column names CREATE VIEW SandyList (Test).....

http://support.microsoft.com/kb/178717 doesn't say anything about Create View not being supported, but I'm not sure which version of ODBC driver I have...needs more research.

I can do it another way. I already create a report file, which I can then query, but I was feeling quite smug for remembering 'Create View' and it would have been a good solution. I'll keep trying!

Sandy

Again clutching at thin straws...

...I think it's the method of connection rather than the SQL. If you try to use CREATE VIEW directly in MS Access it will also fail because Access uses DOA (internally) not ADO - the ODBC bit is irrelevant. The whole thing is rather complicated and thankfully not something I've ever needed to understand However, I wonder, and it is a wild thought, if ADO is the correct method, that perhaps Opus could use a DLL to create the view which might be accessible to Opus - after all Opus can access (pun excluded) Access Queries.

CREATE VIEW however, is (was?) a good idea. We used it frequently with Oracle as CREATE TABLE would have probably given the DB Admin a heart attack

Mack & the Straws

Thanks Mack,
I think I've almost reached the conclusion that Create View is not possible, so my next easiest option it to use a report file created by the first query , then do a second query on it.
I was also interested in the idea of linking to the Excel spreadsheet via access, and creating a view in Access, can the view be created dynamically? I'll have a play with that.

Sandy

Not sure if this applies to Excel, but this says the Access driver doesn't support Create View:

http://support.microsoft.com/kb/157055

Thanks Dave,
I saw that as well, but I wondered if there was some clever way of creating a view dynamically in Access, then using SQL on the created view? but I doubt it.
I tried linking the excel sheet to access, but I couldn't find a way to link all the sheets at the same time.

I may have found a lazy way for my particular problem by just doing one query in SQL. Since the basic query is an AND of two things (x1 or x2 or x3 or x4 AND y1 or y2 or y3), if I search for the X's with SQL.it will return the required record set plus some extra where the result has X's, but no Y's ..., then I can just take one record at a time and check if it contains Y.
I already go through the records in a loop and write them to a file, so I can add a chunk of script before it creates the file and filter for y1 or y2 or y3.
so the record set from the first SQL query becomes an 'on-the-fly' created view..... I have one 'sinmple' query in SQL,and another 'simple' query in script, but I get the advantage of the SQL query putting all the returned results into variables!. but don't have to create a huge SQL query