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

learn more… | top users | synonyms (2)

0
votes
1answer
10 views

A different number of AFTER INSERT trigger alarms in SQLite and SQL Server

I have trigger CREATE TRIGGER [Foo] AFTER INSERT ON [Bar] BEGIN UPDATE Log SET count = count + 1; END When i execute query insert into Bar (ColumnName) values ('SomeValue1'), ('SomeValue2'), ...
1
vote
1answer
429 views

Display a one-to-many relationship in one row

I have a "one to many" relationship in two tables: Table "products" contains: product_id, product_descr Table "packing" contains: product_id, package_id, package_desc One product has often more ...
8
votes
3answers
254 views

Unexpected CASE evaluation logic

I always understood that the CASE statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL ...
0
votes
1answer
24 views

Display the the result of a substraction in a MySQL query (1 table)

I've made a table that looks like this: stop (table name) -cityName {PK} -schedule {PK} -price -routeNo Basicly the customer wants to know what's the cost difference between 2 different stops by ...
2
votes
1answer
45 views

How do I guarantee inventory numbers are unique across all tables?

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
-1
votes
0answers
17 views

PL/pgSQL trigger function syntax error at END; [closed]

I'm writing a simple trigger function, as seen below. For context, it's meant to enforce uniqueness of a key across multiple tables. It will be made more generic once I've got it working. CREATE OR ...
-1
votes
0answers
23 views

Functional Dependency Diagram From the Result of a Query? [on hold]

I was wondering if someone could explain to me how this works. I understand the concept of functional dependency diagrams i.e being given a set of attributes and split the tables using the diagram to ...
0
votes
1answer
108 views

counting the number of a specific word in one SQL column

I'm trying to make a survey like a referundum to know if my neighboors in my small building are for or against a colectif compost to reduce the organics wastes. For this tool vote I'm using PHP and ...
2
votes
1answer
177 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...
30
votes
5answers
61k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
0
votes
3answers
62 views

Get all records from inner joined tables if second table does not have data

I follow below syntax by which I achieve the result. but it takes time IF NOT EXISTS(select * from #tblxyz) BEGIN INSERT INTO #tblxyz select NULL END Select a.* FROM tblabc a ...
2
votes
2answers
46 views

Select rows where smaller row does not exist

Consider the following table data: ID NAME 1 Kshitiz 2 Aman 5 Aakash 6 Manjeet Here there are records missing between 2 and 5. What query can I use to select row 5 in this table?
1
vote
1answer
1k views

How can I select data from two differents servers?

I have two databases: data1 and data2 in two different servers (server1 and server2). I am trying to join two tables of my two databases in MySQL like this: SELECT left(a.code,9) as fourn, ...
1
vote
1answer
41 views

Can the subquery be simplified/optimized?

Here's a simplified version of a query I run. I've removed a bunch of key/value pairs for simplicity. The constraint is that I can't change anything outside of the ARRAY function; but the ARRAY ...
0
votes
0answers
64 views

Trying to find a MIN() workaround for an indexed view

I'm trying to speed up a SQL Server query using indexed views and I have one slow element which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped to an ID ...
0
votes
1answer
207 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
0
votes
1answer
13 views

(SQL) User tables are separated into two sections

Using SQL Plus, I create 13 tables via an .sql file, each containing primary and foreign keys. When I execute "select table_name from user_tables;" they are displayed in this partitioned fashion. ...
0
votes
1answer
19 views

Dumping and restoring tables in PostgreSQL - ERROR: extra data after last expected column

I am trying to dump and restore multiple tables from a database on one server, to a different database on another server. Both are running Postgres 9.4 on linux. The command for the backup was: $ ...
-1
votes
3answers
50 views

how to find the last date from the week number as input

I need to find the last date of week when i pass year and week number as input in my stored procedure. week starts on monday and last day of week will be friday. Database - IBM db2 9.7 platform - ...
0
votes
1answer
142 views

Converting sql into mongodb nosql

I am trying to convert oracle sql into its mongodb equivalent version. create table my_hll as select mod(ora_hash(n), 1024) bucket, max(num_zeroes(trunc(ora_hash(n)/1024)))+1 val from previous_data ...
-1
votes
1answer
19 views

Data Quality Service (DQS) and Master Data Services (MDS) [closed]

Best practice question. Is anyone using one of these in production? Can you share your experience and the business need you were solving with it? I am learning about them and do not really see the ...
0
votes
1answer
20 views

Result consisted of more than one row in procedure

i confused with this i try not using cursor and same get error so i back using cursor but before this day my query success and can generate but this get error please help me i already look other ...
83
votes
8answers
20k views

Where are some useful SQL puzzles to teach SQL in a workplace?

I'm looking for beginner and intermediate level SQL puzzles, that I can point trainees at for practice. I'm aware of http://sqlzoo.net/ which is a great resource - is there anything else out there ...
2
votes
1answer
38 views

Search for X at the beginning of a line within a multi-line string

On SQL Server (2008), I have a column which is varchar(max). I want to query for all rows where the blurb contains a lettered list: Some sentence is here a. foo b. bar c. baz Some more text later ...
0
votes
1answer
53 views

Simple SQL question [closed]

I'm try to resolve this but I don't understand? My query Select FirstName, City FROM Customer WHERE City NOT IN ('Paris') Error Your answer does not contain the expression <>
2
votes
1answer
61 views

Why is Include Actual Execution Plan not included in @@Rowcount when it produces a row count?

While updating a column in a table the last day I was shown a new technique by our Senior DBA to reduce reads and contention. The pseudo-code is WHILE [always true condition] BEGIN UPDATE ...
0
votes
1answer
146 views

Switching a partition on a table with one to many relation

There is two main tables in database (SQL Server 2014) table1: ---------------------------------------------- id name datetime image table2: -------------------------------- ...
6
votes
3answers
111 views

Query wont compile/run

I have a query that won't run on the production server but it runs on the test server. It seems like the query plan won't compile or its compiling a very bad query plan. I have updated all statistics ...
0
votes
1answer
15 views

VMware vFabric Data Director [closed]

https://www.brentozar.com/archive/2013/04/cloud-alternatives-to-microsoft-sql-server/ I liked the idea of VMware vFabric Data Director so I can host data in own cloud / data centre. It seems this ...
3
votes
1answer
62 views

Amazing improvement of script execution speed when split - not understood

I have an ETL job that merges data into about 20 columns of a fact table, all integer non-null (default 0) columns. There are 8 merge statements to completely update all the columns and they involve ...
1
vote
1answer
36 views

Use a scalar hashing function in a computed column - non-deterministic?

I have a scalar function select [dbo].[fn_get_hash_fields] ('blah','dbo') to which is passed a table and a schema and which returns an expression representing a concatenation of all column values ...
1
vote
1answer
234 views

Query multiple rooms with different conditionals (Adults, Children)

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per ...
5
votes
3answers
103 views

Need help with null field values

I am having a problem with syntax. I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the ...
0
votes
1answer
26 views

Script to Delete Tables With A Prefix

I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing. The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix ...
0
votes
0answers
21 views

How to use a cross reference table for different features of a product in different versions?

I am creating a database for one of my classes. I am using SQL Server 2014. I am designing a database that monitors the source control of development of different products. This company has different ...
0
votes
0answers
17 views

transfer the new posts on local to host [closed]

I want instead of creating new posts on webhost, firstly created on localhost and then I transferred to the webhost; this is not problem for me till here, but After transferring a number of posts, if ...
0
votes
1answer
22 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. ...
2
votes
3answers
49 views

Extract string in PostgreSQL 9.2

I got the following column: full_path "gorfs"."absolute_pathname" It keeps the path of a BLOB, like on the example below: Note that the 4865 is the account_id.The path is always like: ...
0
votes
1answer
114 views

ALTER SESSION from a Control File

I am using a Control File to import data from a CSV file into a table, using sqlldr to import the data, where a large number of fields are numeric values with integer and decimal part separated by ...
0
votes
0answers
56 views

SQL Server data file growing

We have an odd situation happening with one of our tables in our database and I am hoping someone can shed some light on it. This table has 3 columns id(bigint), ...
0
votes
0answers
30 views

Convert table values into column header without PIVOT [number of column values is dynamic] - Oracle 10g [duplicate]

I have a table in the following format ID NUM P_VAL Q_VAL ---------------------------------------- 445120000 00 101 25 445120000 10 103 25 445120002 00 ...
13
votes
3answers
34k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
0
votes
0answers
17 views

cannot call json_array_elements on a scalar [closed]

I have JSON values like this: [{"lang_id":179,"language_id":26,"phy_id":82,"language":"Duch"}, {"lang_id":177,"language_id":16,"phy_id":82,"language":"ENGLISH"}, ...
0
votes
2answers
45 views

Name already been used by an existing constraint after renaming the table and creating a new one

I would like to know why, if I alter a table name to different name and then create a table with the same name, I am getting the error "name already been used by existing constraint". This is the ...
1
vote
1answer
21 views

Versioning entities in db with draft option, select query

I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after ...
1
vote
2answers
80 views

How can I remove 'Using temporary ' and speed up my query

Hi I was working for hours to speed up this query using indexes, but its useless.. im tyring to optimize by eliminating 'Using temporary' and using only indexes. I wonder if there is another ...
-6
votes
1answer
30 views

SQL Simple Question [closed]

This looks correct to me, however I receive a table where figures exceed $40.0?
4
votes
1answer
188 views

Query to determine start and end dates based on time overlap

Given the following data: id | user_id | started | closed | dead ------------------------------------------------------------------------------------------- 7714 ...
2
votes
1answer
345 views

Select a range of values

I have the following data in rows: 1 2 3 6 7 15 16 I want to write a SELECT statement to return contiguous ranges. Given the sample data above, the output would be: 1,2,3 6,7 15,16
1
vote
0answers
28 views

Is this database in 1NF, 2NF and 3NF?

I'm doing a software development course and I have to normalise my tables as part of it. Can anyone double check that my data is normalised correctly? The database is for a gym. It has these tables ...