[RBG7-L] - Re: UNION command

[David M. Blocker]

Brad, it's not clear exactly what you did.  Please send the code so we look it over!   David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: Brad Davidson To: RBG7-L Mailing List Sent: Monday, October 10, 2005 3:49 PM Subject: [RBG7-L] - UNION command Ready for a basic SQL question?   Really have never had to do a UNION command, rather, have used the power of temporary tables/views to get data needed from the DB.   But, over the weekend, attempted the Union command of a temporary view and a temporary table.   Upon first pass, no problem.   Second pass, drops the table formed from the union ok, but, get an error message: “Internal Lock of table, disconnect, and re-connect with 1 user, etc ”   Anyone know why? What am I not understanding with this command – what should I be looking for?   Thanks for any ideas on this.   -Brad Davidson     -----Original Message----- From: rbg7-l@rbase.com [mailto:[EMAIL PROTECTED] On Behalf Of van der Zwaag, Frank Sent: Monday, October 10, 2005 11:29 AM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Creating a Temporary Table - solution   Add a bracket at the end. Like this   CREATE TEMPORARY TABLE `tInvoiceHeader` + (`TransID` INTEGER, +  `CustID` INTEGER, +  `EmpID` INTEGER, +  `TransDate` DATE, +  `BillToCompany` TEXT (40), +  `BillToAddress` TEXT (30),  +  `BillToCity` TEXT (20), +  `BillToState` TEXT (2), +  `BillToZip` TEXT (10), +  `ShipToCompany` TEXT (40), +  `ShipToAddress` TEXT (30), +  `ShipToCity` TEXT (20), +  `ShipToState` TEXT (2), +  `ShipToZip` TEXT (10), +  `NetAmount` CURRENCY, +  `Freight`= ( netamount* .01) CURRENCY, +  `Tax`= ( netamount* .081) CURRENCY, +  `InvoiceTotal`= (NetAmount+Freight+Tax) CURRENCY)+ )   From: rbg7-l@rbase.com [mailto:[EMAIL PROTECTED] On Behalf Of Myron Sent: Tuesday, 11 October 2005 8:16 a.m. To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Creating a Temporary Table Bernie, Somewhere along the way I remember Razzak saying that with the new release(s) you should be using IDQUOTES. So, the question becomes this; looking forward what is the best way to write the program so that it will work with future versions?   Once I set the IDQUOTES for ` it worked fine. I just needed help with the IDQUOTES. Thanks for your help. Myron   From: rbg7-l@rbase.com [mailto:[EMAIL PROTECTED] On Behalf Of Bernard Lis Sent: Monday, October 10, 2005 1:59 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Creating a Temporary Table   Myron, I don't use quotes around col name, try it without quotes. Bernie Lis ----- Original Message ----- From: Myron To: RBG7-L Mailing List Sent: Monday, October 10, 2005 12:55 PM Subject: [RBG7-L] - Creating a Temporary Table   I’m having difficulty creating a temporary table. So, I went back to RRBYW10. The code below comes from RRBYW10, the file is TempTbls.rmd. When I trace the code in RRBYW10 it traces fine. I then move the code into a new database and I get an error message: -ERROR-Syntax error (2367) Can someone please explain to me why it works in one database and not another?     SET ERROR MESSAGE 2038 OFF DROP TABLE tInvoiceHeader CREATE TEMPORARY TABLE `tInvoiceHeader` + (`TransID` INTEGER, +  `CustID` INTEGER, +  `EmpID` INTEGER, +  `TransDate` DATE, +  `BillToCompany` TEXT (40), +  `BillToAddress` TEXT (30),  +  `BillToCity` TEXT (20), +  `BillToState` TEXT (2), +  `BillToZip` TEXT (10), +  `ShipToCompany` TEXT (40), +  `ShipToAddress` TEXT (30), +  `ShipToCity` TEXT (20), +  `ShipToState` TEXT (2), +  `ShipToZip` TEXT (10), +  `NetAmount` CURRENCY, +  `Freight`= ( netamount* .01) CURRENCY, +  `Tax`= ( netamount* .081) CURRENCY, +  `InvoiceTotal`= (NetAmount+Freight+Tax) CURRENCY)   Thanks Myron ____________________________________________________________________ CAUTION - This message may contain privileged and confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you have received this message in error please notify Air New Zealand immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of Air New Zealand. _____________________________________________________________________ For more information on the Air New Zealand Group, visit us online at http://www.airnewzealand.com _____________________________________________________________________