Using Toad for Oracle, I can generate full DDL files describing all tables, views, source code (procedures, functions, packages), sequences, and grants of an Oracle schema. A great feature is that it separates each DDL declaration into different files (a file for each object, be it a table, a procedure, a view, etc.) so I can write code...
Hi all, I have a simple table based queue system. In its simplest form, it consist of an id, a queue name, and a status. When reading the next message from a given queue, we need to ensure FIFO (first in first out), i.e. the lowest id from the given queue with the given status. This all works fine with some thousand rows, but when we re...
I have a quick question about a select statement condition. I have the following table with the following items. What I need to get is the object id that matches both type id's. TypeId ObjectId 1 10 2 10 1 11 So I need to get both object 10 because it matches type id 1 and 2. SELECT ObjectId FROM Table WHERE TypeI...
Hi Sirs, i hope you can help me with the resolution of this task we have. originally we have these tables: hwtype id name 1 router 2 switch hwelement id idhwtype name 1 1 RTR1 2 1 RTR2 3 2 SWT1 hwattributes id idhwtype name 1 1 speed 2 1 IP 3 2 ports hwtypeattri...
I have a table with data that looks like this: create table demo_patient_info ( attend timestamp, patient_id int, blood_pressure double ); I would like to write (preferably ANSI) SQL queries that allow me to do the following: query1: return the difference between bp of all patients with each other (using a WHERE clause to ...
My table is organised like this: With key as the primary field. The records shown are in the renamed table. I need to get out the original_name by the key. The key coluimn is the primary key of the table. This is my SQL code: SELECT original_name FROM `renamed` WHERE key='fb166' However, it does not return any results. I have tri...
Say, there are 2 tables: Person | id | name | LanguagesSpoken | person_id | language_name | Is there a way to create a view that would contain 2 columns: person_name and languages_spoken as a comma-separated list? I'm developing against SQLite. ...
I'm preparing to the SQL Server exam (70-431). I have the book from Sybex "SQL Server 2005 - Implementation and Maintenance". I'm little confused about estimating a size of a table. In the 2nd chapter there is explained how to do this: Count the row size from the formula: Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +...
Using MYSQL I would like to refactor the following SELECT statement to return the entire record containing the newest invoice_date: > SELECT id, invoice, invoice_date FROM invoice_items WHERE lot = 1047 id invoice_id invoice_date ----------------------------------- 3235 1047 2009-12-15 11:40:00 3295 1047 2009-...
I have a query that creates several temporary tables and then inserts data into them. From what I understand this is a potential cause of Table Spool. When I look at my execution plan the bulk of my processing is spent on Table Spool. Are there any good techniques for improving these types of performance problems? Would using a view ...
I have a few tables representing geographical entities (CITIES, COUNTIES, ZIPCODES, STATES, COUNTRIES, etc). I need to way represent sets of geographical entities. A set can contain records from more than one table. For example, one set may contain 3 records from CITIES, 1 record from COUNTIES and 4 from COUNTRIES. Here are two possi...
I need to ID/tag all contact records of our active clients, even if only ONE of the client's contact records is tagged as ACTIVE. Specifically, I want to pull all contact records from all companies in my GoldMine database in which ANY ONE of the company's contact records has the company's account status tagged as "ACTIVE CLIENT". Then ...
I'm trying to spot-test an Oracle backed database with hsqldb and dbunit, but I've run into a snag. The problem is with the following EJB-QL (simplified a bit): SELECT o FROM Offer o WHERE :nowTime BETWEEN o.startDate AND o.startDate + 7 This seems to only work in Oracle's version of SQL. What's the easiest way for me to conver...
I have a dateTime object in C# and i want to do an insert into SQL Server datetime field. What is the correct format for this? ...
I always struggle with dealing with normalised data, and how I display it. Maybe its because I don't fully understand the normalisation rules, like how to get it fully into Boyce-Codd. Performance is not really an issue at this stage, though maintainability of the schema is. user ID Name 1 Alice 2 Bob 3 Charlie skills ID ...
The goal is to store activities such as inserting, updating, and deleting business records. One solution I'm considering is to use one table per record to be tracked. Here is a simplified example: CREATE TABLE ActivityTypes ( TypeId int IDENTITY(1,1) NOT NULL, TypeName nvarchar(50) NOT ...
The TrackingData table is inside a database named Tracking. The stored procedure is being run inside the same database. I get data back with the query, but not with the SP. SELECT * FROM dbo.TrackingData LEFT OUTER JOIN SSMain.dbo.EmailCampaignTracking ON (dbo.TrackingData.emailCampaginTrackingID = SShMain.dbo.EmailCampa...
Is there a way to export a database query to OPML? ...
What are the disadvantages to do so? ...
i have a table called orders. one column on order is customer_id i have a table called customers with 10 fields Given the two options if i want to build up an array of order objects and embedded in an order object is a customer object i have two choices. Option 1: a. first query orders table. b. loop through records and query the pe...