Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 12 total |
Create a view in DB1 that uses data from a table in DB2?? |
Wed, Apr 7 2021 7:35 PM | Permanent Link |
Ian Branch | Hi Team,
Working in EDBMgr, I thought the following should work.. {sql} CREATE VIEW "DB1.Parts" AS select * from DB2.Parts WITHOUT CHECK OPTION VERSION 1.00 {sql} It 'prepares' OK but I get the following error on Execute.. "ElevateDB Error #401 The table or view Parts does not exist in the schema Default" What am I doing wrong? Or, for that matter, can I do this? Regards & TIA, Ian |
Thu, Apr 8 2021 3:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
To check things out I altered your statement a bit First using database DBiWorkflow {sql} CREATE VIEW "DBiWorkflow.PartsX" AS select * from DBiWorkflow.Parts WITHOUT CHECK OPTION VERSION 1.00 {sql} ran the query from and << ElevateDB Error #406 Invalid view identifier 'DBiWorkflow.PartsX' in the schema Default>> This I would expect because you're not allowed database modifiers in DDL queries next {sql} CREATE VIEW "PartsX" AS select * from DBiWorkflow.Parts WITHOUT CHECK OPTION VERSION 1.00 {sql} That works OK run in database DBiHistory and <<ElevateDB Error #401 The table or view Parts does not exist in the schema Default>> since the query "select * from DBiWorkflow.Parts" is valid and runs fine in the database DBiHistory I think its a bug. However, since I haven't read the SQL spec I may well be wrong. I suggest reporting to Tim unless someone points out that it is what the spec says. Roy Lambert |
Thu, Apr 8 2021 5:28 PM | Permanent Link |
Ian Branch | And that's a No!
I checked with Tim too. "Unfortunately, no. You cannot have any references to another database in any database objects because doing so would make the catalog unable to be loaded if the other database isn't available or present." Sigh! Back to the drawing board. Regards & Tks, Ian |
Fri, Apr 9 2021 2:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
This is where you need to cheat - I'll knock up an example but it will take a while because I have cudgel my memory into co-operating Essentially what you do is create a temporary table in the target database using the definition stored in the information database for the desired database and then populate it. The other alternative is to just use a standard query. Do you need to edit the data and have it get back to the source? If so that gets a bit trickier. Roy Lambert |
Fri, Apr 9 2021 4:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
Not as hard as I thought SCRIPT (IN TblName VARCHAR) BEGIN DECLARE Grabber VARCHAR DEFAULT 'SELECT Name,CreateSQL FROM DBiWorkflow.Information.Tables WHERE Name = ''XX'''; DECLARE Transfer VARCHAR DEFAULT 'INSERT INTO XX SELECT * FROM DBiWorkFlow.XX'; DECLARE DBCursor CURSOR FOR DBStmt; DECLARE Maker VARCHAR DEFAULT ''; PREPARE DBStmt FROM REPLACE('XX' WITH TblName IN Grabber); OPEN DBCursor; FETCH FIRST FROM DBCursor ('CreateSQL') INTO Maker; CLOSE DBCursor; EXECUTE IMMEDIATE REPLACE('CREATE TABLE' WITH 'CREATE TEMPORARY TABLE' IN Maker); EXECUTE IMMEDIATE REPLACE('XX' WITH TblName IN Transfer); END May need a bit more work depending on what you want to do with it. Roy Lambert |
Fri, Apr 9 2021 7:00 PM | Permanent Link |
Ian Branch | Hi Roy,
Thank you. I think. I have no idea what it is doing. Let's assume I need a view of the DBiWorkflow.Company table in DBiArchive. I figure I put Company in place of XX but what is TblName? What does this leave me with to use? No editing is involved, all read only for reporting. Regards, Ian |
Fri, Apr 9 2021 8:23 PM | Permanent Link |
Ian Branch | OK. Sussed it out enough to apparently get it working in EDBMgr.
I can select records from the Temp tables but they do not appear as a table under the Database. Thus they can't be 'seen' in my program. |
Sat, Apr 10 2021 2:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
As you probably figured out TblName is the name of the table you want. The table is created as a temporary table (means it will disappear when the app is closed). Those don't show in EDBManager's list of tables but are accessible simply by referring to them. Do you have a small demo app that shows what you're trying to do? Are you, for example and guessing from your post, trying to use then as a lookup table? Since you quote Company are you trying to show the company name in AJobTickets? Roy Lambert |
Sat, Apr 10 2021 4:47 AM | Permanent Link |
Ian Branch | Hi Roy,
Within Delphi code it isn't an issue, I can refer to either database. It is for Reporting, the report engine can only address/use one database. As the selected Live data records will move to the Archive when they run the Archive app, I have decided to copy/update the additional relevant tables, Company, Parts, Customers & Accounts from Live to Archive each time the Archive app is run. They will just sit there for any time they need to be interrogated by the Report engine. Tks for your efforts, Ian |
Sat, Apr 10 2021 7:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
Couldn't you just use queries in that case? You can have persistent fields, and a query can point to the other database. The only thing you might need and can't have is indices. Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |