sql

References/walkthroughs for maintaining database schemas with Visual Studio 2010?

I have Visual Studio 2010 Beta 2 and SQL Server 2008 installed. I'm working with a populated database and want to modify various column types. SQL Server Management Studio requires me to drop tables to do this, and get pretty finicky given my moderate level of knowledge of SQL Server. However, I heard the new database project type supp...

How can I improve a Ruby on Rails code that has a lot of SQL as strings?

I have a piece of Ruby on Rails code that has a complex SQL query (well, not that complex, but as far as I know beyond the ORM capabilities) and for my taste it has too many strings and harcoded values. I'd like to improve it as much as possible, so my question is open ended, what else can I do to improve it? Some particular issues I ha...

Firebird multiple statements

Hello, is there any way to execute multiple statements (none of which will have to return anything) on Firebird? Like importing a SQL file and executing it. I've been looking for a while and couldn't find anything for this. ...

Trying to add a where clause for row_number

I am trying to add a where clause to the following query: SELECT TOP 200 ROW_NUMBER() OVER (ORDER BY KEY_TBL.RANK DESC) AS RowNumber, FT_TBL.JobId, FT_TBL.Title, FT_TBL.[Description], FT_TBL.Location, KEY_TBL.RANK FROM Jobs AS FT_TBL INNER JOIN FREETEXTTABLE (Jobs, (Title, [Description]), 'packaging') AS KEY_TBL ON FT_TBL.JobId = KEY...

Adding Up Values From A SQL Query

I have a table like this (using wordpress) +---------+----------+------------+ | meta_id | meta_key | meta_value | +---------+----------+------------+ | 1 | views | 3 | | 2 | blahh | a value | | 3 | smthn | boo | | 4 | views | 4 | | 5 | views | 5 | | 6 ...

Weighted average in T-SQL (like Excel's SUMPRODUCT)

I am looking for a way to derive a weighted average from two rows of data with the same number of columns, where the average is as follows (borrowing Excel notation): (A1*B1)+(A2*B2)+...+(An*Bn)/SUM(A1:An) The first part reflects the same functionality as Excel's SUMPRODUCT() function. My catch is that I need to dynamically specify...

Keeping ordering in a WHERE IN clause in my SQL

I am running the following SQL query: SELECT * FROM cms_albums WHERE id IN (SELECT album_id FROM cms_albums_collections WHERE collection_id = 1 ORDER BY position ASC) Now, assume the inner query SELECT album_id FROM cms_albums_collections WHERE collection_id = 1 ORDER BY position ASC returns the following: album_id "4" "2" This...

How to separate positive and negative numbers into their own columns?

I have a table with the following columns and data: activity_dt | activity_amt 2009-01-01 | -500 2009-01-01 | 750 Can I write a query that looks at the sign of activity_amt and puts it in the credits column if it's positive, and the debits column if it's negative? (I'm using Sybase) activity_dt | debits | credits 2009-01-01 ...

sql, order by column A and then by column B

Hi , How to write the sql so that the result can be ordered first by column A than by column B. Something like below: SELECT * FROM tbl WHERE predictor ORDER by col_A and ORDER by col_B ...

resolving Deadlock

I have a Store Procedure being called to fill one table whenever we receive incoming files. Sometimes we receive more than one file and the procedure will be called simultaneously. Inside the Procedure the statements are quite simple as given below IF NOT EXISTS (SELECT.... WHERE A=1 B=2) INSERT ... ELSE UPDATE ... WHERE ...

Need help in executing the SQL via shell script and use the result set.

Hi All, I currently have a request to build a shell script to get some data from the table using SQL(ORACLE). The query which I'm running return n number of rows.. Is there a way to use something like result set. Currently , I'm re-directing it to a file, but I'm not able to reuse the data again for the further processing.. Any hel...

Rewriting correlated subquery as JOIN?

UPDATE: Thanks to Sifu Bill's advice I have amended the SQL query. Now it returns the correct number of distinct assets (five). Is it possible to rewrite the following correlated subquery as a JOIN? SELECT TOP 100 PERCENT Asset_ID, work_order_id, status_id, downtime_hours, date_completed FROM dbo.mtvw_wo_reason1 WHERE (Asset_ID IN (SEL...

Linq to SQL nvarchar problem

Hi, I have discovered a huge performance problem in Linq to SQL. When selecting from a table using strings, the parameters passed to sql server are always nvarchar, even when the sql table is a varchar. This results in table scans instead of seeks, a massive performance issue. var q = ( from a in tbl where a.index == "TEST" s...

what the difference between not in - and - not exists in oracle query ?

hi what the difference between not in - and - not exists in oracle query ? when i use not in and when i use not exist ? thank's in advance ...

Rollback for Atomic and Durability

Hi, I am trying to understand the ACID property of database transction: how they are achieved; which part is atomicity and which part is durability ,etc. Let's say I have a transction with two actions,A and B. Unfortunately, system powered off when performing action B. After a system reset, we know the database will preserve (through t...

SQL to fill columns based on columns of associated table

Suppose I have table first_table which has an FK to table second_table table second_table which has column called name_field Now, I want to add a column in first_table called name_field and fill it with the one on the associated second_table. How should I fill the values purely using SQL? (this is Oracle, if that matters) ...

Accessing data filename from within SQL*Loader control file

How do I access the input data file name from within SQL*Loader control file so that I can insert it into the table along with data from the input file? Let's say for example I have the following control file: LOAD DATA APPEND INTO TABLE STG_AM02_BA_RAW WHEN (1:2) = 'DT' ( SUBSCRIBER_NO POSITION(11:18)CHAR, ...

Can you SELECT WHERE something's LIKE and IN at the same time?

I need to write what I'd like to call a Valley Girl-query. I need to SELECT something that's LIKE IN - something like this: SELECT * FROM Table1 WHERE Name LIKE IN ( SELECT Name FROM Table2 ) The reason for this is I've got a table full of company names, but they're not exactly the same - f.ex. in Table1 it might say "Chrysler Gr...

Fabricate entities with multiple IDENTITY PKs?

Disclosure: I'm a 'natural key' advocate myself and averse to the IDENTITY PK approach. But I do have a 'live and let live' approach to lifestyle choices, so no religious arguments here please :) I have inherited a table where the only key is the IDENTITY PK column; let's call it ID. There are a many tables that reference ID. The intend...

SQL to have one specific record at the top, all others below

Hey all, I am trying to put together a query that will display one specific record (found by the record's primary ID) at the top, and display all other records below it, sorted by date (I have "date_added" as one of the fields in the table, in addition to primary ID). I could do this with a UNION (first select would locate the record I...