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
11 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. Database - IBM db2 9.7 platform - windows How to do it?
5
votes
1answer
35 views

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
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 ...
12
votes
5answers
283 views
+250

Group daily schedule into [Start date; End date] intervals with the list of week days

Update: see comparison of answers below. I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. ...
-1
votes
1answer
6 views

Data Quality Service (DQS) and Master Data Services (MDS)

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
0answers
3 views

List objects in SQL file

We are trying to migrate a database from SQL 2014 to Azure SQL. In SSMS, you can right click on the database and then on a task to "Deploy Database to Windows Azure SQL Database..". This task ...
0
votes
1answer
15 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
35 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
52 views

Simple SQL question [on hold]

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 <>
7
votes
2answers
225 views
+250

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 ...
2
votes
1answer
59 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
0answers
9 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: $ ...
0
votes
1answer
143 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
92 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 [on hold]

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 ...
1
vote
2answers
29 views

How to Join two table using pivot with combining two field in one table

I have two table in this format Job_Skill ID_Skill Min_Job Idea_Job Max_Job ===================================== 1 0 0 1 2 ...
2
votes
1answer
53 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
34 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
232 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
85 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
17 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
11 views

transfer the new posts on local to host [on hold]

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
18 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
42 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: ...
2
votes
0answers
30 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 ...
0
votes
1answer
19 views

How to use pivot when there are three fields and u want to put them in one category

I have data in this format Skill Min Ideal Max ========================== C++ 0 1 1 I want to display it like this Criteria C++ ================= Min ...
0
votes
1answer
109 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
37 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
0answers
53 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
26 views

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

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
33k 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
14 views

cannot call json_array_elements on a scalar [on hold]

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
44 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
78 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
28 views

SQL Simple Question [on hold]

This looks correct to me, however I receive a table where figures exceed $40.0?
4
votes
1answer
187 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
2answers
343 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
25 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 ...
0
votes
0answers
40 views

Design a ER for hospital database system [on hold]

I trying to design an ER Diagram for a hospital database, and the developer of the database determent what they want in the system of the hospital .. but some of what they want I cannot understand it ...
-1
votes
0answers
33 views

normalize table data

Maybe my question is duplicate or asked many times, but it's rare, and I really need this. I have a table, which contents looks like: Id points 3 20 3 42 4 40 4 60 6 20 6 30 5 10 ...
0
votes
1answer
260 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in MySQL although I feel the schema should be optimized for it since it's based on the WordPress database model. I've got 4 tables: ...
2
votes
3answers
100 views

Return the uniqueidentifier generated by a default on insert

Goal Retrieve the latest guid value in real time after you have inserted the value in the table Problem Don't know how to do it Info The code should only specify new values for address and ...
0
votes
1answer
33 views

MySQL - Finding Gaps of Date Ranges - Overlapping Dates

I have been struggling for a long time with generating gaps in date ranges, according to a set. CREATE TABLE `terms` ( `id` int NOT NULL PRIMARY KEY, `term_begin` date NOT NULL, `term_end` date ...
0
votes
1answer
46 views

Storing Friendships: Should I use a multivalued column to retain Friend Identifiers?

Say I have a table UserFriendlist that has the columns: Id (set as Primary Key) UserId FriendListIds And let us suppose that it holds the following values: ...
1
vote
2answers
173 views

Need help in incrementing the date in oracle

Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this. select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' ...
5
votes
0answers
19 views

Writing a query that accepts multiple sets of values in postgresql

I am trying to write a query in postgres that accepts multiple sets of values. My knowledge of postgres is brief and this below query is the most complicated I have had to put together to finish off ...
0
votes
0answers
15 views

Grab value from the group SSRS

How can I grab a value for a particular month in SSRS if it's in a group? I have all 12 months as a group, in a column outside of the group I need to get the value for March that is in the group?