When we join more than 2 tables, oracle or for that matter any database decides to join 2 tables and use the result to join with subsequent tables. Is there a way to identify the intermediate join size. I am particularly interested in oracle. One solution I know is to use Autotrace in sqldeveloper which has the column LAST_OUTPUT_ROWS. B...
Jruby is hanging on connection to sqlserver and I cannot figure out why. Here is my code ... puts "make connection" ActiveRecord::Base.establish_connection( :adapter => 'jdbc', :driver => 'com.microsoft.jdbc.sqlserver.SQLServerDriver', :url => 'jdbc:sqlserver://test:1433;databaseName=test;integratedSecurity=true', :username=>'test', :pa...
I've got some data in the following format: -1,-1,-1,-1,701,-1,-1,-1,-1,-1,304,390,403,435,438,439,442,455 I need to insert it into a temp table like this: CREATE TABLE #TEMP ( Node int ) So that I can use it in a comparison with data in another table. The data above represents separate rows of the "Node" column. Is there an ea...
I have a table which has an Id and a name field. I usually bind the name to the dropdownlist but I was told that any dml should be on the Id so how can I use the name in the dropdownlist and at the same time still use the Id? ...
I'm trying to write a query that returns the shortest string value in the column. For ex: if ColumnA has values ABCDE, ZXDR, ERC, the query should return "ERC". I've written the following query, but I'm wondering if there is any better way to do this? The query should return a single value. select distinct ColumnA from ( select Colum...
can we always convert a usual subquery/correlated subquery to join type query? ...
Now we have a firebird database with 1.000.000 that must be processed after ALL are loaded in RAM memory. To get all of those we must extract data using (select * first 1000 ...) for 8 hours. What is the solution for this? ...
I would like to use following sql to avoid constructing sql dynamically: SELECT CommentID, Comment, FROM Comments --if Author id is not null then filter upon author id otherwise get all comments (ignore author id) WHERE AuthorID LIKE COALESCE(@AuthorId, '%') --if comment type is present filter upon it, otherwise get all comments (i...
I have a classified_id variable which matches one document in a MySql table. I am currently fetching the information about that one record like this: SELECT * FROM table WHERE table.classified_id = $classified_id I wonder if there is a faster approach, for example like this: SELECT 1 FROM table WHERE table.classified_id = $cla...
I have a column that contains page titles, which has the website name appended to the end of each. (e.g. Product Name | Company Name Inc.) I would like to remove the " | Company Name Inc." from multiple rows simultaneously. What SQl query commands (or query itself) would allow me to accomplish this? To re-illustrate, I want to convert m...
Background I'm designing a Rails app to record research data. Most of it can be conceptualized as being "survey" (or "questionnaire") data. We already have several Access databases and CSV files that hold this data. The existing design is that each survey has its own table with one column per question. Many of these tables have 100+...
I have implemented the Autocomplete extender in my ASP.NET webpage using a Textbox. The textbox displays the name however is there a way to bind it also with the ID? ...
I need to change the default port(1433) of SQL EXPRESS 2008 instance in c#. ...
Hi, i have two tables Order(id, date, note) and Delivery(Id, Note, Date) I want to create a trigger that updates the date in Delivery when the date is updated in Order. I was thinking to do something like CREATE OR REPLACE TRIGGER your_trigger_name BEFORE UPDATE ON Order DECLARE BEGIN UPDATE Delivery set date = ??? where id = ...
here we got a positional parameter: SELECT u FROM ForumUser u WHERE u.id = ?1 and here a named parameter: SELECT u FROM ForumUser u WHERE u.username = :name this is DQL (doctrine query language) but i think the concept is the same. could someone please explain what these mean and do? ...
For example I have: create table a (i int); Assume there are 10k rows. I want to count 0's in the last 20 rows. Something like: select count(*) from (select i from a limit 20) where i = 0; Is that possible to make it more efficient? Like a single SQL statement or something? PS. DB is SQLite3 if that matters at all... UPDATE PP...
Hello, I reading some old ScottGu's blogs on Linq2SQL. Now I'm doing the SPROC part. I'd like to know what's the exact meaning of @variable. See this from ScottGu's Blog ALTER PROCEDURE dbo.GetCustomersDetails ( @customerID nchar(5), @companyName nvarchar(40) output ) AS SELECT @companyName = CompanyName FROM Customers WHERE Custo...
hi, I'm trying to design a db to store the timetable for 300 different bus routes, Each route has a different number of stops and different times for Monday-Friday, Saturday and Sunday. I've represented the bus departure times for each route as follows, I'm not sure if i should have null values in the table, does this look ok? route...
To illustrate the problem, I make an example: A tag_bundle consists of one or more than one tags. A unique tag combination can map to a unique tag_bundle, vice versa. tag_bundle tag tag_bundle_relation +---------------+ +--------+ +---------------+--------+ | tag_bundle_id | | tag_id...
The following database view rounds the date back to the closest fiscal year (April 1st): CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS SELECT CASE WHEN to_number(to_char(SYSDATE, 'MM')) < 4 THEN to_date('1-APR-'||to_char(add_months(SYSDATE, -12), 'YYYY'), 'dd-MON-yyyy') ELSE to_date('1-APR-'||to_char(SYSDATE, 'YYYY'...