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

learn more… | top users | synonyms (2)

2
votes
2answers
62 views

Why am I able to query for more than 1024 columns but I can not make a view of the same query?

My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am ...
0
votes
1answer
105 views

Error importing SQL Azure Bacpac to SQL Server 2014 LocalDb

Downloaded a v12 SQL Azure bacpac, and trying to import it into a SQL Server 2014 LocalDB... I get: TITLE: Microsoft SQL Server Management Studio ------------------------------ Could not load schema ...
1
vote
0answers
57 views

Max returns multiple rows [closed]

I am quite sure I am missing something. Could someone tell me why these two tables are not returning the same row count, please? SELECT Person_id, COUNT(*) FROM table GROUP BY person_id; And these ...
0
votes
0answers
31 views

Distinct Rows Based on Field Value

I can't seem to get around this problem. I am trying to query into a SQL table I have and limit the results. I have an org_id. Also a field named address_id. The table has a "primary_address" column ...
1
vote
1answer
31 views

Mapping Fields based on unknown relationship

I have a challenging SQL query to work out (SQL Server). I have one table that will show mappings between the two tables. So below, field F1 is a scanner id and table two, has the scanner Id in F1. ...
1
vote
1answer
45 views

SQL getting IDs from one table with multiple entries in another table

In my database I have two tables: Persons and PersonAttributes. One Person may have several Attributes associated with it. What I would like to do is get a list of all Persons(/IDs) that have at ...
-1
votes
1answer
67 views

how can I add multiple values in a single column?

I'm working on a project and I have a scenario We have a vendors table, some vendors have many contact no. how can i add multiple phone numbers on a single contact no. column?
4
votes
1answer
58 views

How to get all connected values as a single column

I'm trying to use CTE recursion. This is my scenario SQL Fiddle. It is a simple tree structure: CREATE TABLE [dbo].[AL]( [IdAL] [int] IDENTITY(1,1) NOT NULL, [ID1] [int] NOT NULL, [ID2] ...
0
votes
1answer
22 views

Where are sql profiler trace files stored?

Can't seem to find an answer to this.. During a recent sql profile I noticed C:\ was rapidly filling up. Does anyone know exactly where these files are stored? is it part of SSMS?
2
votes
2answers
75 views

Does UDP Port 1434 need to be open for named instances with static TCP?

I have built a named instance which is running on a static port, 50101. The network guys have opened the port on the firewall to allow incoming connections. I have asked them open UDP port 1434 too, ...
2
votes
1answer
89 views

Optimize performance for query with CTE using a multicolumn index

I've got this slow query: WITH flks AS (SELECT id FROM public.ja_feedlog WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT AND EXTRACT(EPOCH FROM ...
2
votes
1answer
44 views

Is there any particular reason LIMIT 0 is allowed?

I've just tried running SELECT * FROM tbl LIMIT 0, which unsurprisingly, returns 0 results. I could see an argument for making it be an error, because it will never return any result, but then again ...
-1
votes
1answer
29 views

Trend Calculation [closed]

I have a table with the below data: FHANUMBER Quarter Value ------------------------- 001 1 5 001 2 4 001 3 3 001 4 2 I need ...
10
votes
3answers
142 views

Theil-Sen estimator function in T-SQL

Does anyone have a Theil-Sen regression function written in T-SQL? I found one written in Perl, but I'm not able to recode it into SQL.
2
votes
3answers
76 views

Update a column for every row of a large table to a unique value in a set range

We have a SQL Server table with over 3 million rows in it. Each row represents a product. These products are provided by about 500 suppliers, and are updated each night via a file from each supplier. ...
1
vote
1answer
56 views

How to add Subpartition to all partitions

I believe adding sub-partition in Oracle 11g is easy ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS'); I have close to 250 partitions..namely jan07 feb07 ... ...
0
votes
1answer
25 views

Create a file with variable name in power shell Power shell

I am trying to execute the below power shell script to script out the db in sql server. [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $s = new-object ...
1
vote
1answer
28 views

Compare Past Records to Most Recent Record

Is it possible to structure a single query that would return a 0 or 1 (true/false) by comparing first record to previous records? Sample records in table: ProductID SampleDate RunFailure ...
0
votes
0answers
23 views

Advice on query optmization - Multiple Unions

I'm trying to understand how this might be better optimized. I currently have these three different queries to union the results of each. The first case finds all records with the where condition of ...
2
votes
2answers
62 views

Why is user: system doing an hourly backup of my SQL databases?

I've got a pretty standard maintenance plan set up for my SQL databases; yet in addition to that plan, all of my databases, user and system, are backed up every hour by user: System... where is that ...
3
votes
1answer
58 views

Constraint to prevent overlapping Time periods

I have a pretty simple table that stores a StartTime and an EndTime, both with the data type of Time(0). I need to enforce a constraint that prevents any overlapping. 'Touching' time periods are OK, ...
0
votes
1answer
21 views

Create Folder - folder-name by entry in SQL table [closed]

I have the following question: I have a SQL-Table with a column ->'FOLDERNAME' So, I would need for each entry in my table a own folder on my local disk with the value from the column 'FOLDERNAME' ...
0
votes
1answer
32 views

Is the same thing(internally) putting conditions in a query over a view that directly on the view?

I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same? SCENARIO 1 View: CREATE VIEW TEST.SIMPLEVIEW AS SELECT ...
0
votes
1answer
42 views

Mariadb sql query get stuck

I have table A which size is 32GB and has the following 5 fields: a (int) b (varchar(128) c (varchar(128) d (int) e (int) a, b and c are unique key. Now, when I query like this: select * ...
3
votes
2answers
29 views

CMS (Central Manamenent Server) help/guidance?

We have just recently come across the Central Management Server feature in SQL2008. We currently have a number of environments and think that this feature could really help us in running multiple ...
4
votes
2answers
62 views

Finding earliest connected value over two columns

I have a, perhaps slightly esoteric, use case. Given a table like the following: id1 | id2 | timestamp -----+-----+--------------------- 3 | 4 | 2016-03-22 09:52:15 1 | 2 | ...
0
votes
1answer
29 views

How to replace an empty date value?

I am trying to replace many null date values in a single column using this query: UPDATE animal SET BirthDate = '1900-01-01 06:00:00 AM' WHERE BirthDate = ''' MySQL shows this error: You ...
1
vote
0answers
30 views

use IFNULL in laravel

I have a query in laravel which is working fine. $subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id') ...
1
vote
1answer
44 views

How to convert varchar2 column to number in oracle?

I have a table emp_details. The column emp_salary is of type varchar2 in which certain rows are null: emp_salary 100,34 null 20,2 30,3 null I need a result in which the column type is number: ...
4
votes
1answer
86 views

Finding changed column values

Table 1 ID |COL2|COL3|COL4|COL5| ROW1| 1 | 1 | 1 | 1 | ROW2| 1 | 3 | 2 | 6 | ROW3| 1 | 1 | 1 | 1 | ROW4| 1 | 1 | 1 | 1 | ROW5| 1 | 1 | 1 | 1 | ROW6| 1 | 1 | 1 | 1 | Problem ...
1
vote
1answer
55 views

Empty string values - PostgreSQL 9.2 [closed]

I've got some empty string values being putting into my DB. Code I've used: CREATE FUNCTION get_as_byte_array(anyelement) RETURNS INTEGER[] AS $$ SELECT CASE WHEN ...
2
votes
2answers
32 views

How to handle nested groupings with possible null elements in one query

As you can see in this sql-fiddle, I have three tables: events, event_dates, and event_times. The original tables contain various other fields, which I omitted as they aren't relevant, I just need to ...
1
vote
1answer
56 views

Finding list of Duplicate records from 2 tables in DB2

I have a Person table and Address Table. Their relationship is Person.AddrId = Address.Id Person table has First_name, Last_name, and AddrId. The Address table has Address1, Zipcode, and Id. I ...
8
votes
5answers
642 views

Invert a Boolean expression which can return UNKNOWN

Example I have a table ID myField ------------ 1 someValue 2 NULL 3 someOtherValue and a T-SQL Boolean expression which can evaluate to TRUE, FALSE or (due to SQL's ternary logic) UNKNOWN: ...
0
votes
0answers
28 views

Case Statement to Identify Multiple Errors

I'm trying to write a statement that identifies any of 200 potential errors / illogical combinations in a data set for each line item. My first thought was to write a case statement for each error ...
1
vote
1answer
24 views

can we call CRUD operations as service methods?

I was asked this question by one of my superiors. The usual operations we do on database table from Service layer in java is Create, Update, Retrieve, Delete. These operations are basically services, ...
4
votes
1answer
121 views

Efficiently search for a prefix range in SQL Server

Let's say I want all records with a prefix between two user-supplied alphanumeric values of the same length. Thus, if the user enters A010 and A025, I want to return A0101, A0200 and A0259. What I've ...
4
votes
1answer
56 views

How to create a 'keywords' table from a column's values

I have a table with 8 million+ rows in it. It stores jobs and their characteristics. There is a column in it that holds job titles. I am planning to create a keyword table based on the job titles from ...
4
votes
1answer
105 views

Why isn't this PosgreSQL LIKE query working?

PostgreSQL 9.2. [local] postgres@host=# select * from units where ip_address LIKE '192.168.43.%'; ERROR: 42883: operator does not exist: inet ~~ unknown LINE 1: select * from units where ip_address ...
1
vote
1answer
49 views

SQL query returning cartesian and duplicates and not single records

So I have four tables as follows: CREATE TABLE softdrinks ( drinkID INT NOT NULL AUTO_INCREMENT, drinkName VARCHAR(100), PRIMARY KEY (drinkID) ); CREATE TABLE categories ( categoryID INT ...
1
vote
1answer
40 views

Reuse subquery as in another result

In a typical social-network-like application I have the following query that gathers news from a set of followed channels. The main query gets the news, while the subquery gets the followed channels: ...
1
vote
1answer
35 views

Need help on UPDATE query [closed]

This is my table tracks id | art | time id is an auto-increment primary key art is a varchar(512) time is a timestamp not null predefined as current_timestamp and on update current_timestamp ...
1
vote
2answers
64 views

Building a where clause based on user selections

My group and I are developing a multi agent system (MAS), a system that is supposed to conduct statistics on specific fields of the agents' databases using semantic search and ontology (the user ...
0
votes
2answers
48 views

Get date based on interval days in sql

I have a table containing range table like this. DayFrom Dayto Frequent 60 46 3 = interval 15 days (15/3 = Every 5 days send email for 3 times) 45 37 3 = interval 9 days (9/3 ...
0
votes
1answer
41 views

on duplicate key update

I'm trying to execute the following statement in MySQL: INSERT IGNORE INTO shop_item_details (shop_id,product_id,product_name,Product_model,Product_category,Product_details,price) VALUES (1, NULL, ...
0
votes
0answers
22 views

Use a Trigger After Insert and Update to Sync Two SQL Server Databases

I am not very familiar with Triggers in SQL Server and need help creating one to sync customer information from one database into another as it is added or updated. To make a long story short I am ...
1
vote
1answer
64 views

Finding interval overlap between two tables

I have two tables that have top_depth, bottom_depth, lith_type. One defines the different layers found in a borehole and the other define where samples are extracted in each of the layers. I would ...
1
vote
2answers
55 views

SQL minimum permissions to manage any job

I am looking for a way to grant minimum rights to a login to manage ANY SQL Server Agent job. The SQLAgentOperatorRole allows to manage own jobs and disable/enable any job, however I need to allow ...
1
vote
1answer
32 views

SQL db2 remove specific item from the query

Can you please help me out with the below query? What I am looking for is a code to exclude items with wording '90003' from column COR_ID. So far I have a code that extracts data with date older than ...
0
votes
1answer
39 views

Flagging duplicates

I have a huge table with a couple of duplicates: I want to add a new column that shows some value when duplicates occur, something like: case when fist.firstname = last.firstname then 'yes' else ...