Digital Workshop

Welcome to the Digital Workshop Message Boards
It is currently December 23rd, 2024, 2:57 am

All times are UTC [ DST ]




Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: SQL CREATE VIEW
PostPosted: April 27th, 2010, 6:15 pm 
Offline

Joined: November 25th, 2004, 1:24 pm
Posts: 512
Location: Scotland
Opus: 9.75
OS: Win 10
System: Asus i7-7700K 16Gb
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

Code:
records = myDB.ExecuteSQL("SELECT * FROM [ECLIPSE_EMC$]WHERE Part_Tested_1 LIKE '%"+SpecSearch+"%' OR Part_Tested_2 LIKE '%"+SpecSearch+"%' OR Part_Tested_3 LIKE '%"+SpecSearch+"%';");

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

Code:
myDB.ExecuteSQL("CREATE VIEW SandyList AS SELECT * FROM [ECLIPSE_EMC$];")
var records = myDB.ExecuteSQL("SELECT * FROM SandyList ;")


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

_________________
Whoever designed this, never actually used it!


For this message sandyn has been thanked by : David, mackavi


Top
 Profile  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 27th, 2010, 9:35 pm 
Offline
Godlike
Godlike

Joined: November 12th, 2005, 1:56 am
Posts: 1474
Location: SFBay Area
Opus: OpusPro v9.0x, & Evol.
OS: Vista32
System: Core 2 duo 2Ghz, RAM 3GB, Nvidia Go 7700 - laptop
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

_________________
_good things come to those who wait(0)_


For this message Lar_123 has been thanked by : mackavi


Top
 Profile  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 27th, 2010, 10:45 pm 
Offline

Joined: November 25th, 2004, 1:24 pm
Posts: 512
Location: Scotland
Opus: 9.75
OS: Win 10
System: Asus i7-7700K 16Gb
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

_________________
Whoever designed this, never actually used it!


For this message sandyn has been thanked by : mackavi


Top
 Profile  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 27th, 2010, 11:06 pm 
Offline
Godlike
Godlike
User avatar

Joined: March 21st, 2007, 10:44 am
Posts: 3188
Location: UK
Opus: Evolution
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

_________________
When you have explored all avenues of possibilities, what ever remains, how ever improbable, must be the answer.

Interactive Solutions for Business & Education
Learn Anywhere. Learn Anytime.

www.interaktiv.co.uk
+44 (0) 1395 548057


Top
 Profile Visit website  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 28th, 2010, 9:29 am 
Offline
User avatar

Joined: October 25th, 2004, 10:33 am
Posts: 257
Location: UK
Opus: Pro 8
OS: Windows 7 Professional x64
System: Dell XPS15 i7x4 2.1Ghz 6GB 128GB SSD
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:

Code:
myDB.ExecuteSQL("CREATE VIEW SandyList AS SELECT * FROM [ECLIPSE_EMC$];")
var records = myDB.MadeUpFunctionNameToCreateAnError("SELECT * FROM SandyList ;")


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

Code:
var records = myDB.ExecuteSQL("CREATE VIEW SandyList AS SELECT * FROM [ECLIPSE_EMC$]; SELECT * FROM SandyList;")


For this message Dave Emberton has been thanked by : mackavi


Top
 Profile Visit website  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 28th, 2010, 11:05 am 
Offline

Joined: November 25th, 2004, 1:24 pm
Posts: 512
Location: Scotland
Opus: 9.75
OS: Win 10
System: Asus i7-7700K 16Gb
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

_________________
Whoever designed this, never actually used it!


For this message sandyn has been thanked by : mackavi


Top
 Profile  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 28th, 2010, 11:39 am 
Offline
Godlike
Godlike
User avatar

Joined: March 21st, 2007, 10:44 am
Posts: 3188
Location: UK
Opus: Evolution
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

_________________
When you have explored all avenues of possibilities, what ever remains, how ever improbable, must be the answer.

Interactive Solutions for Business & Education
Learn Anywhere. Learn Anytime.

www.interaktiv.co.uk
+44 (0) 1395 548057


Top
 Profile Visit website  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 29th, 2010, 11:44 am 
Offline

Joined: November 25th, 2004, 1:24 pm
Posts: 512
Location: Scotland
Opus: 9.75
OS: Win 10
System: Asus i7-7700K 16Gb
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

_________________
Whoever designed this, never actually used it!


For this message sandyn has been thanked by : mackavi


Top
 Profile  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 29th, 2010, 11:47 am 
Offline
User avatar

Joined: October 25th, 2004, 10:33 am
Posts: 257
Location: UK
Opus: Pro 8
OS: Windows 7 Professional x64
System: Dell XPS15 i7x4 2.1Ghz 6GB 128GB SSD
Not sure if this applies to Excel, but this says the Access driver doesn't support Create View:

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


For this message Dave Emberton has been thanked by : mackavi


Top
 Profile Visit website  
 
 Post subject: Re: SQL CREATE VIEW
PostPosted: April 29th, 2010, 2:26 pm 
Offline

Joined: November 25th, 2004, 1:24 pm
Posts: 512
Location: Scotland
Opus: 9.75
OS: Win 10
System: Asus i7-7700K 16Gb
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

_________________
Whoever designed this, never actually used it!


For this message sandyn has been thanked by : mackavi


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC [ DST ]


Who is online

Users browsing this forum: Bing [Bot] and 41 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group