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)

0
votes
1answer
22 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
21 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
29 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
25 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
14 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
182 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
57 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
69 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
40 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
30 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
33 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
23 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
57 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
137 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
31 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
41 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
28 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
24 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
52 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
38 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
31 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
206 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
60 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
35 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
46 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
43 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
31 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
13 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
34 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
58 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
34 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
15 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
64 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
40 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
71 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
22 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
27 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
34 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?
0
votes
0answers
28 views

Adding a second database to the same instance of a server

I already have a multi user software program running on Windows 7 work stations and the server is running Windows Server 2008r2. I am trying to setup another program using the same server. The code is ...
0
votes
0answers
24 views

How to determine is a value exists in a column within a partition

I have a set of data where I need to look at partitioned data and determine if a value exists within that partition. Example data: AccountNumber Code Status Credited ...
3
votes
2answers
152 views

Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger ...
1
vote
2answers
84 views

SQL Multiple JOINS Interpretation order

I have a big problem of using multiple joins in one SQL Statement. For example i have 10 Tables which they are tbl1,tbl2, tbl3,..,tbl10. I would like to select some columns from each table from tbl1 ...
1
vote
1answer
24 views

Alternate per 2 postgresql rows, but start over if there are rows remaining

I'm trying to select below table, grouped by two (=max, if there is only one, show one) of each supplier_id. But, after all suppliers are selected it needs to start over. So this is the table: Table: ...
0
votes
2answers
50 views

How to select the largest value of the same column in multiple tables?

I have multiple tables which have some columns with the same meaning, eg., event_timestamp. The schemas are like following: Table A: ...
1
vote
1answer
40 views

#2014 - Commands out of sync. you can't run this command now. Query was working before. But now it is not working

My table structure is ... There is Counter which has many Box in it. Box has a one BoxType. and Collection stores Box datewise revenue and data. I need data of particular 1 COUNTER (Data of All the ...
0
votes
1answer
30 views

Alternative to RDBMS: Modeling Exam/Testing data

I'm a born and bred relational database guy and C# programmer dude. IBM DB2 & VB.Net in school. TSQL & C# at work. I'm planning a project for fun: Designing a Testing platform that goes from ...
1
vote
2answers
87 views

How to do an =ANY(SELECT…query in postgresql?

I'm trying to select all the latitudes and longitudes for a group of users based on their id being in an array stored in another table. Here's my attempt: SELECT latitude, longitude FROM userloc ...
0
votes
1answer
36 views

Copy of a database with an existing database with schema and data in sql server

I have two databases one is having master kind of tables and other will have transaction data. I should create a new database with a structure of an existing database to my clients. Here is the ...
1
vote
0answers
39 views

Is it bad practice to launch more than 100 sql statements at once?

I have PHP code that is meant to updating the billing status of someone. I only want it to update if the bill has been successfully created and therefore I think a possible solution would be to ...
1
vote
1answer
39 views

Trigger AFTER INSERT on tables with different columns

I'm writing a trigger that works after an insert. This is what I have: create trigger trgInsereQuestao on tblquestaoOpcao after insert as begin set nocount on; insert into ...