Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

2
votes
0answers
379 views

What is causing all of the msrepl_tran_version columns to become out-of-sync between locations in transactional replication setup?

I have been having a recurring issue with transactional replication setups at the firm I work for. We use SQL 2008 R2. Seemingly without warning or any replication errors, all of the ...
3
votes
1answer
739 views

Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

I'm not a Database Administrator--just a Software Engineer. I would like to know if it is possible to reduce the following T-SQL query: SELECT SUM(Price * Quantity) as 'Total', SUM(Price * ...
2
votes
1answer
3k views

Named Parameters in SQuirreL SQL?

Is it possible to set named-parameters in queries SQuirreL SQL?
0
votes
1answer
3k views

how do I merge two views

I have basically two views named customerV and productV. the SQL statement in the customerV view is : SELECT CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME, ...
15
votes
1answer
30k views

Varchar(Max) field cutting off data after 8000 characters SQL Server 2008

I have a field to store some data, the field is declared as varchar(Max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest ...
4
votes
2answers
663 views

Using surrogate key after merging the person's data?

This question is about business intelligence with sql server 2008 R2. If you have a person named Jim Brown with his birthdate and social security number (SSN) being located in three different source ...
2
votes
1answer
424 views

Rewriting a query and eliminating subqueries

I have the following query : SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs, num_bases_in_singletons FROM stats_assembly WHERE ws_id IN ( ...
1
vote
1answer
3k views

Decode query truncates a date

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database SELECT DECODE( TO_CHAR(MIN(DATE), 'YYYYMMDD') , '' , NULL ...
0
votes
3answers
203 views

Dog slow query, needs optimizing, please help

I found this query in our web app that has been made by hired programmers. It performs like a dog and needs optimization, but I'm no MySQL wizard (yet), unfortunately. Please help, anything is ...
0
votes
1answer
6k views

Returning Row Count in a Stored Procedure in Oracle

I'm mostly SQL Server person, so forgive me if this is too close to SQL Server, but in a nutshell using Oracle 10 I'm trying to build a procedure something like this: PROCEDURE SP_INFO_EX_S( ...
3
votes
1answer
127 views

Complex MySQL Query returning almost all the correct rows

I've created a SQLFiddle with my schema and some test data. The calculated columns aren't working for some reason in SQLFiddle, but they come out fine in my MYSQL Workbench. Assume they work ...
5
votes
2answers
33k views

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
8
votes
5answers
48k views

Update existing rows with squence number/char or any unique data

I added a new column in the Table X This column "cn" has to be unique and mandatory, but old data don't have any value. How to update the existing records with sequecely or random unique data? ...
8
votes
3answers
6k views

How to boost performance of virgin queries in MS SQL Server?

I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
3
votes
3answers
31k views

How to select/count rows where the same value exists in a column but not in other column all at the same table

I have this table structure and data: CREATE TABLE IF NOT EXISTS `default_relations_users` ( `id_user_rq` int(11) NOT NULL, `id_user_ap` int(11) NOT NULL, UNIQUE KEY `rusers_rq_ap_idx` ...
4
votes
1answer
630 views

SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this ...
2
votes
2answers
114 views

Performing a “Spread Negitive” over a dataset

I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
0
votes
2answers
146 views

full outer join where join key in result always has value [closed]

I wish to do a full outer join between two tables A, B joining on A.ID = B.ID. Now, it is quite easy to include A.ID and B.ID in a result set. this will include rows where: A.ID is null, B.ID is not ...
2
votes
1answer
171 views

How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time?

I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected ...
-1
votes
2answers
1k views

Parsing a string result from a query

My sql server 2008 query returns a very long strings such as: GET ...
0
votes
1answer
72 views

Select a value for the max

I have a query which selects a max value over all the values of in a datetime column (dtimesent). select dtimesent, max(ResponseTime) AS maxtime FROM allrequests where Request like '%Requestsa%' ...
5
votes
1answer
390 views

Query to find optimal filegroups for objects

I have been assigned a task to create four file groups (one per volume) locate tables so that they are located in a manner that during an ETL process no filegroup is being read and written to at the ...
2
votes
3answers
3k views

Copying distinct rows from one table to another

In SQL Server, I can copy one table into another by doing: Select * into desttable from sourcetable However, there are duplicate rows in sourcetable and I would like to just copy distinct rows. How ...
2
votes
1answer
71 views

Rewriting a query to be more efficient

Currently I'm using: SELECT t.CustomerLastName FROM ( SELECT SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, Orders.Id, Customer.Lastname AS CustomerLastName FROM OrderLine, Product, ...
2
votes
1answer
2k views

SET QUOTED_IDENTIFIER issue

I have created a Database Project in Visual Studio 2010 for a database which is in 2008. I am having issues in deploying the database as I don't know why, but at the time of deployment in some ...
8
votes
4answers
77k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
1
vote
1answer
86 views

Filter On Indexed, Formatted DATE

How do I write my calling query (without modifying V_SPECIAL_SAMPLES) to use the index on sample.sample_date (filtering by date first)? I believe it's the TO_CHAR and TO_DATE conversions that prevent ...
2
votes
3answers
349 views

How to get all non deleted messages from one user

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like: CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
820 views

NULL values on MDX query (Total Column has Values)

I'm a novice with MDX, so sorry if I've missed something. (and sorry about my english too!) The following query: WITH SET [ROWMEMBERS] AS '{([BIN_SCH_INV_GRO].&[1]) , ...
6
votes
2answers
1k views

Is there a better way to create dynamic time averaged series over a dynamic frequency?

I've got a series of tables with lots of high precision data collected from various devices. The intervals that they were collected on varies and even wanders over the time series. My users want the ...
3
votes
2answers
3k views

Why can the SA account no longer access the master database?

I'm somewhat stuck. We have a database server which was set up and configured by a third party. It's for a third party product so unfortunately we're stuck with the way they've configured it. Their ...
3
votes
1answer
832 views

How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')

Consider a simple query as SELECT * FROM posts JOIN tags USING(post_id) JOIN tag_map USING(tag_id) WHERE tags.tag IN('tag1', 'tag2', 'tag3') tag_map is a many-to-many relationship between tables ...
2
votes
1answer
256 views

How to split tables without affecting all stored procedures

We have merge replication setup between corporate database and remote clients. Lately we realized that people working on the corporate server do not need data to be tracked by merge replication as ...
2
votes
0answers
139 views

sql data to a csv-file via cobol

I 'm using the following code : string 'insert into ' delimited by size 'tgamGameTeams ' delimited by size '(Id,Name,RemainingBudget,TeamGamePoints,GameId) ' ...
5
votes
5answers
3k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
1
vote
2answers
325 views

Why are my results returned twice?

I've this query: SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id, `default_wall_posts_comments`.`comments` as comments, UNIX_TIMESTAMP() - default_wall_posts_comments.date_created ...
2
votes
1answer
817 views

Disabling Indexes and Auditing for large data import

Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are: 1. Is it ...
5
votes
2answers
8k views

Grouping records based on intervals of time

I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the ...
1
vote
1answer
4k views

What joins are available in Informix 9.2?

I'm testing some queries with an old version of Informix (9.2.1) and can't seem to execute even the most basic queries containing RIGHT OUTER JOIN or FULL OUTER JOIN. Tried several variations (RIGHT ...
1
vote
1answer
893 views

SQL Database to XML?

I would like to export SQL data to an XML file so it can be read in COBOL. I am working with SQL Express 2008 r2.
1
vote
0answers
61 views

SQL Server Database Size much higher than the backup size after restore [duplicate]

Possible Duplicate: Why is a .bak so much smaller than the database it's a backup of? The database backup size is around 120 MB but when I run Restore FileListOnly, I see that the data ...
2
votes
1answer
198 views

SELECT for values in a table

I have this table structure: id1 id2 1 2 1 3 2 1 2 4 id1 and id2 are not unique as you can see in the values. I need to retrieve those values that exists in the two columns ...
3
votes
1answer
15k views

What does COUNT(1) actually count? [duplicate]

Possible Duplicate: What is the difference between select count(*) and select count(any_non_null_column)? I have heard it commonly stated that when counting the number of rows in a query, ...
1
vote
2answers
6k views

Error while importing a “.sql” file in database

I am trying to import a .sql file in to my local database. whenever i type the command to import the file, I get an error ERROR at line 1: Unknown command '\S'. This is the first line of the sql file ...
0
votes
0answers
46 views

Database security through stored procedures [duplicate]

Possible Duplicate: Do stored procedures prevent SQL injection? I'm curious about some database security techniques I've been learning in class recently. Namely, I have implemented a SQL ...
2
votes
1answer
147 views

Minimizing the number of parent rows for a distinct column in a child table

I have three tables with the following format A B C ----- ----- ----- aKey bKey fk_c_b fk_b_a c_enum c_value aKey and bKey are the primary key ...
6
votes
5answers
15k views

What is the easiest way to get started using databases with real data?

I have a project that could benefit from using a database, but I have no experience with databases, don't have access to a server, and have relatively little experience working with things living ...
14
votes
1answer
5k views

Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything. ...
2
votes
3answers
1k views

Find and remove gaps in sequence across two different columns

I've tried to solve this problem using a set-based approach. However, since I need to look at each row I think I must use a cursor; please correct me if I'm wrong. The table: Project, item, method, ...
1
vote
1answer
5k views

Clarify - Need to add Multiple foreign key constraints

I am not sure if I am thinking about this process the wrong way. Just to Surmise if I create a table(TeamList) whose sole purpose is to capture the coaches team list of players and positions for a ...