DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

2
votes
1answer
46 views

Order by where conditions

SELECT * FROM table WHERE key REGEXP 'some long regular expression' OR key REGEXP 'some other long regular expression' ORDER BY key REGEXP 'some long regular expression' DESC, key REGEXP 'some other ...
1
vote
1answer
158 views

Can Execute trigger in SQL Server 2008 R2

I'm trying to create a simple trigger to update a table's column after it was inserted. But when I execute the trigger it produces an error: Cannot execute script. Additional information: Unable ...
1
vote
2answers
99 views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
0
votes
0answers
48 views

How can I make a foreign key from a fragment of the primary key?

I have this table: CREATE TABLE Phones ( Brand VARCHAR(20), Model VARCHAR(20), PRIMARY KEY (Brand, Model) ); A mobile phone can't be uniquely identified just by its model (for example: HTC (One) or ...
0
votes
1answer
50 views

Convert an interaction matrix into a ternary relation

I have what I'm calling a sparse "interaction matrix" of data in MS Excel that I wish to convert into a "ternary relation" (table) in Access. The spreadsheet data are in this pattern: Name Act-1 ...
0
votes
1answer
71 views

Is there a reason my CTE with a basic subquery in the second stage of the CTE is failing?

Ok... anyone aid with this sorry...i've been playing with some of my works reports and i thought id found a way to mix two results into one when i came across a small issue... if i run my CTE code ...
1
vote
1answer
95 views

Modifying a non managed table partition

I ran across a partition that was initially setup (sql 2012) beginning of 2014 to break by month but wasn't updated for 2015. The main issue is that the last partition was setup as Jan 2015 so Feb ...
1
vote
1answer
43 views

SQL server compress backup for lower version databases

Recently our development team migrated couple of sql server 2000 Databases onto SQL2K8R2. Now we creating the backup strategy, and just had a doubt, that: 1) Compatibility level of those DB's still ...
2
votes
2answers
176 views

SELECT WHERE CASE

I have the following table (MySQL): -------------------------------------------------------- | id |product_id| store_id | stock_id | priority | ...
0
votes
4answers
42 views

ISNULL() OR Is NULL in UPDATE statement

I have to run UPDATE statement on live online database about 5 lac records so i want to know which statement are run faster Update Table set REC_ID = isnull(REC_ID,'') Update Table set REC_ID = '' ...
1
vote
2answers
62 views

DB backups for production to DR without creating jobs in prod

I had two servers(SQL 2005) A and B. B is Production server and A is DR. I want to take backups for B server databases from A server. We can not create any maintenance plans or jobs in B production ...
0
votes
0answers
89 views

count rows with same value and get column from other table

I'm trying to count product numbers for a period of time there are three tables. invoice (table) Column Column ------------------------------------------- |invoicenumber ...
0
votes
1answer
27 views

How to use a group by in inner join

I have Two tables Table 1 ID totalamount 100 1000 101 500 Table 2 ID Individualamount 100 500 100 300 100 100 101 ...
0
votes
1answer
34 views

HAVING ANY in Postgres

I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition. For example if I wanted to select a group where at ...
1
vote
1answer
46 views

invalid memory alloc request size on simple query with array_agg on Postgres 9.3

Postgresql 9.3 - Debian 7 54gb RAM 8 cores (On google compute engine) I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is: select ...
0
votes
1answer
27 views

Insert where not already available for FK?

I'm looking for a simpler way to do this as I'm clearly thinking like a developer instead of a DBA. How would a DBA tackle this? I have a table with 3 column: PK, Name, FK. PK Name FK 1 ...
1
vote
0answers
23 views

Removing Orphan Pull merge replication subscriptions

I have a merge replication set where I have removed one of the pulled subscriptions. On the subscriber the subscription shows up after deletion. It will not go away. I tried the methods provided on ...
0
votes
1answer
2k views

Conversion failed when converting the nvarchar value '0.000000' to data type int

I have a code select CONVERT(NUMERIC, CASE ISNULL(FLEXFIELD4,'') WHEN '' THEN 0 ELSE FLEXFIELD4 END ) FROM DEIT_INDENT_DTL_INBOUND_RXL WHERE IND_INDENT_NO='NEE15SR000003' which is ...
1
vote
2answers
93 views

Count values from one table using two columns in another table

I have a problem. I have table_1 (col1, col2, value) and table_2 (col1, col2). I want to count values from table_1 but only the rows which exist in table_2. In both tables col1 and col2 are key (not ...
-1
votes
1answer
201 views

use variable as column name in where clause in sql

I want to use a variable as column name in my SQL query. Problem: I can not use dynamic SQL as the SQL query that I have is inside of a user defined inline table function. What are other possible ...
0
votes
0answers
50 views

Query gives not result

I have a quite simple table with different values. When I search a specific column no results are given. What could be the error? For example I search for an "id": db=# select * from table where id ...
1
vote
1answer
32 views

How to include a where clause in a query from another column

I have two tables: pointer_table(col_name varchar, col_val varchar, wh_clause varchar) value_table(col_1 varchar, col_2 varchar, value varchar) col_name in pointer_table denotes the column in ...
2
votes
1answer
50 views

SQL Maintenance Rebuild Fails

I am pretty new to SQL and I have this maintenance job that keeps failing and I am not sure how to troubleshoot this. I have troubleshot other job failures with google help to success but this one is ...
0
votes
1answer
32 views

Querying sums of grouped consecutive rows in PostgreSQL 9

I have data about people traveling in different countries like this: country | begintimestamp | distance Germany | 2015-01-01 00:00:00 | 100 Germany | 2015-01-01 01:12:13 | 30 France | ...
0
votes
1answer
82 views

Insert/Update SQL Server Stored Procedure

I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and ...
2
votes
2answers
529 views

SQL Server: Frequent Login timeout expired in Linked server

I am using SQL Server 2012. I have scheduled a job to run every 1 hour, with 1 step calling 1 simple Stored Procedure. The Stored Procedure migrates around 1K data from production server to our local ...
0
votes
1answer
43 views

Convert string to number from xml at server side

I am having issues converting numbers stores as character (with . as decimal, sometimes are null) to number from xml on server side. Query is part of package. When I run the query below on my ...
0
votes
1answer
102 views

Comparing Column Value and Display the Differ Column

Supposed I have table temp A and table temp B, IF OBJECT_ID('tempdb..#A') IS NOT NULL BEGIN DROP TABLE #A END IF OBJECT_ID('tempdb..#B') IS NOT NULL BEGIN DROP TABLE #B END CREATE TABLE #A ( ID ...
2
votes
1answer
46 views

How to Create Aggregate function in SQL Server 2008

My requirement is to create concatenate function so I am trying to create Aggregate function as shown CREATE AGGREGATE [dbo].[Concatenate] (@value [NVARCHAR](MAX)) RETURNS[NVARCHAR](MAX) EXTERNAL ...
0
votes
1answer
27 views

Update statement with a function

I'm just curious. I was trying to optimize an update statement with a function Ex update circle set area = fnGetArea(radius) where ...<bunch of conditions> This update went 5-10mins and when ...
1
vote
2answers
118 views

Postgres multiple columns to json

I am running postgresql 9.3.4. I have a table with 3 fields: id name addr --- ---- ---- 1 n1 ad1 2 n2 ad2 ... I need to move the data to a new table with fields like: id data ...
0
votes
1answer
51 views

Query MST instead of UTC for timestamps

on SQL Server, i'm trying to query all of last months data based off of the time stamp, however, all those timestamps are in UTC and i need them to be in MST (the reason is mainly for the end dates) ...
2
votes
1answer
34 views

When to break large delete queries up?

I've got an auto generated join table (three columns, two of them keys to other tables), which was recently corrupted during an aborted migration. As a result there are around 1 million duplicate rows ...
2
votes
1answer
241 views

Foreign Key NULL vs Foreign Key to an empty string

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) CREATE TABLE medicine ( id serial PRIMARY KEY, ...
1
vote
2answers
92 views

Calculating percentage from two tables

I have two tables: First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table. Second one contains interactions of mentors with mentees on different ...
0
votes
1answer
57 views

Ranking not correct in sql server 2012 full text search

I am having a table like below: CREATE TABLE [dbo].[test_table]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NULL, [area] [varchar](50) NULL, [designation] [varchar](100) ...
1
vote
2answers
55 views

How to update the parent/child of all rows in the tree (ltree)?

Here are my configurations: DB type: postgresql (9.3 if that's necessary) Table name: product_sections Column names: section_id (integer) & section_path (ltree) Detail: I have one reference ...
0
votes
1answer
53 views

What's the point of SQL joins if you have OLAP?

I want to understand the purpose of using SQL joins from a business analysis perspective. Let's say I want to analyze customer data. Why would I want to pull data from multiple tables using join ...
1
vote
1answer
50 views

Which of these structures are good for maintaining history data?

Iam creating an application which is being used to send reminders. Here is the requirements. We have set of Students. Every student has a Student Pass, this need to be renewed in every month. ...
0
votes
0answers
17 views

Recommend a suitable DB for storing events

I have rows of the following type: identifier (string), type (int), date (date), count (int), extra info Rows have a unique constraint on (identifier, type, date). I want to perform the following ...
0
votes
0answers
35 views

Default value stays after I dropped the default constraint [duplicate]

So, here is a test for fun on SQL SERVER 2012. I have a test table, you could choose any table you have in your local. Then I add a NOT NULL column with default constraint: alter table test add ...
2
votes
1answer
80 views

Job to check the status of another job

I am looking to create a job which would check a status of another job every 5 minutes. select CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Success' WHEN 2 ...
0
votes
1answer
41 views

Optimizing a stored procedure

I have done some optimizations on the below mysql stored procedure, however it still runs pretty slow: CREATE DEFINER=`xxx`@`%` PROCEDURE `testing`(IN _MONTH INT, IN _YEAR INT, IN _PRODUCTS TEXT) ...
0
votes
1answer
17 views

Querying All Records of All Users who have any instance of purchase behavior in their history

I am trying to write a query that shows me all user ids that in any instance of usage have a particular type of "purchase credit" associated with their account. I have one table, "users" that shows ...
0
votes
1answer
147 views

UPDATE table SET value which SELECT from other table

I need to update the column on Table_1, and then SET the value which select from Table_2 UPDATE top(3) a SET col_1 = col_2_From_Table_2 // Problem here, select col_2 from Table_2 and update into ...
0
votes
0answers
94 views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. for linked servers

I am trying to execute a query to pull data from another server. I added the server as a linked server to the another one but I am getting the following error when I try to execute the query Login ...
1
vote
2answers
95 views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
0
votes
1answer
25 views

Postgres - Inject 0 values when rows do not exist

I have 3 tables that I need for reporting: *dates* date_sk | full_date | day_number_of_month 1 | 2013-01-01 | 1 2 | 2013-02-01 | 1 3 | ...
1
vote
1answer
44 views

Count of 3 consecutive unsynced dates

I have a table set out like so: user_id the_date minutes_asleep ---------------------------- 1 2015-01-01 480 1 2015-01-02 0 1 2015-01-03 0 1 2015-01-04 0 ...
1
vote
0answers
61 views

Are there limits to SSRS 2014 RDL files going over to SSRS 2008 (or 2012)?

Is there any incompatibility with reports developed in SQL Server SSRS 2014, and then exported to .RDL files that will be imported into SQL Server 2008?