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

learn more… | top users | synonyms (2)

-1
votes
1answer
2k 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
92 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 =...
4
votes
1answer
63 views

Checking for multiple identical values in a Posgresql array

I have a simple table in Postgresql: CREATE TABLE data (id integer, values integer[]); INSERT INTO data VALUES (1, '{1,2,3,4,5}'); INSERT INTO data VALUES (2, '{1,1,2,3,4,5}'); INSERT INTO data ...
2
votes
1answer
1k 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
38 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 ...
2
votes
1answer
94 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 ...
2
votes
1answer
70 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 ...
6
votes
4answers
7k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...
2
votes
1answer
753 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, ...
0
votes
1answer
80 views

How do I count and order by unique entries?

I have this: SELECT tblClient.[Printed Name], tblClient.[Address 1], tblClient.[Address 2], tblClient.City, tblClient.State, tblClient.Zip, tblClient.Phone, tblClient.[Email Address] FROM tblClient ...
0
votes
1answer
155 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) ...
1
vote
1answer
1k views

Error 1005: Can't create table can't find where the problem is

I have created a EER model using MySQL Workbench (latest release). Apparently all is fine but when I try to do a FORWARD ENGINEER I get the common Error 1005: Can't create table. As suggested in this ...
6
votes
3answers
27k views

T SQL Table Valued Function to Split a Column on commas

I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value. Ex: "one,two,three,four" would return a new table ...
0
votes
1answer
357 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) ...
51
votes
3answers
42k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
0
votes
1answer
328 views

How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records?

I've got a table schema in Oracle 11 which is storing records that are linked together. The table schema is roughly like this CREATE TABLE "SOMEDATA" ( "ID" INTEGER NOT NULL, "ITEMNAME" ...
1
vote
1answer
221 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. ...
2
votes
1answer
422 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
198 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 ...
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 ...
0
votes
1answer
31 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
730 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
1answer
50 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
193 views

GROUP BY performance

I have a query like this: SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) FROM visitors_log_table WHERE datetime >= CURDATE() GROUP BY Hour WITH ROLLUP It works fine. But I don'...
0
votes
0answers
224 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
1answer
221 views

UPSERT statement never completes, infinite loop?

Based on some of the stackoverflow answers I wrote the following UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would ...
1
vote
2answers
263 views

Automobile database logic [closed]

I am building a web based quoting system for a local automobile dealership. I think I have figured out the logic for the databases but I want to get a second opinion before I start just to make sure ...
1
vote
1answer
78 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 ...
0
votes
1answer
42 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 | 2013-...
1
vote
0answers
308 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?
4
votes
2answers
790 views

How to restore a SQL Server 2008 R2 database

Here's my issue. I have a server created from a windows image that includes SQL Server 2008 and two specific windows users, these windows users are a part of the sysadmin role and are users for the ...
0
votes
1answer
91 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
1answer
40 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: ...
2
votes
1answer
5k 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 ...
3
votes
2answers
5k 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
0answers
118 views

Locally connecting to SQL Server 2012 using Windows Authentication

I have a SQL Server 2012 cluster which I've updated with SP2, and since the update I can't locally connect to the server using Windows Authentication. Yet, I can connect using same credentials from ...
0
votes
1answer
766 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
56 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 ...
3
votes
1answer
17k views

SQL Server error log entry : Error: 17806, Severity: 20, State: 14

I have error in my log for a few weeks, I searched a lot but I couldn't found useful answer. I did closed SQL Server port for public IP, but I have problem yet. Error: 17806, Severity: 20, State: ...
0
votes
0answers
31 views

Can I create a constraint to a subset of data? [duplicate]

Here's a simplified view of my database |---------------| |-----------| | ORGANIZATION | | CONTACT | |---------------| |-----------| | OrgId | | ContactId | | OrgName ...
0
votes
0answers
25 views

table not taking records sequencially [duplicate]

While inserting rows into a table in oracle 10g, the rows are not inserted in the order I am putting them, like for the following inserts: insert into emp values (1000, 'debjani', 30); insert into ...
0
votes
0answers
119 views

What kind of data cannot be stored economically in a database / data-warehouse?

Someone mentioned this in a presentation but didn't provide any examples. This stuck with me and I am trying to figure out what kind of data couldn't be stored economically in datbases ever since. It ...
0
votes
0answers
57 views

merge two column into single columns and show them in separate rows

My Table looks like this Name Date1 Date2 Date3 A 25 23 20 B 20 15 10 I want it like this Name Date A 25 A 23 A 20 B 20 B 15 B 10 how i can ...
10
votes
6answers
12k views

SQL Query Formatter [closed]

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux (...
0
votes
1answer
64 views

How to store multiple vendor data in Google Cloud SQL?

I am designing a mobile app in which I need to host my vendor data and give access to that data after analysis to only the specified users. Since I will have multiple vendors with different set of ...
0
votes
0answers
21 views

How to setup a trigger for updated records

Prod database "Mother" Copy of database "Father" Here's my scenario I need help with: I have a database that has a table called User_Group table on our Production db "mother". The dev team wants me ...
0
votes
1answer
74 views

sql join without combining all rows

I've searched questions but can't seem to apply to my scenario. Please see attached code below. I am trying to join two subsets of this data but am getting duplicates/too many rows please see ...
1
vote
1answer
781 views

UPDATE/INSERT into Dynamic Columns

I am trying to update columns based on the value of the inserted column of the table 'my_table'. I want to be able to record the timestamp of the values changed. I am using the triggers but I can't ...
1
vote
0answers
207 views

finding out the Backup set physical name

I created a maintenance plan to back up two user databases and system databases over a month ago. I queried the msdb database to view the size of the backups using the following query. SELECT bs....
1
vote
2answers
7k views

Sum values from two tables ORACLE

I want to use two values, in two different tables, to be used as a total in a third table. For instance: table accommodation and flight Add value from acc_price with value from flight_price Add ...