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
32 views

Select multiple columns from subquery - Connect by

I have a table which manage folder structure hence has parent child relationship and we trigger a query to sum all document in the complete heirarchy SELECT folder_name name, document_count, ...
0
votes
2answers
155 views

using update query with subquery in Oracle sql

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement ...
0
votes
1answer
24 views

On the fly MySQL balance

Please excuse my English translation that makes by Google Translate I need to solve the following in a MySQL query without php or another language: In the “A” column I have a string value, in ...
3
votes
2answers
11k views

How to use RESTRICT for Foreign Key in mysql?

In the database structure of CREATE TABLE Country ( name varchar(40) NOT NULL, PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE City ( name varchar(40) NOT NULL, ...
0
votes
1answer
497 views

Connect by prior - multiple fields and query efficiency

I have 2 tables one of which defines the hierarchy for the other equipment id desc 1 item1 2 item2 3 item3 4 item4 5 item5 6 item6 7 item7 8 item8 ...
0
votes
2answers
22 views

Slow query / Indexes creation (PostgreSQL 9.2)

I have the following Query: explain analyze SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
0
votes
0answers
9 views

How to use column result as column name with MySQL [duplicate]

At the moment I have sample table as such doc_name | processed_date | doc_version ---------------------------------------- doc_1 | 2015-01-01 | 1.0 doc_1 | 2015-01-02 | 1.1 doc_1 | ...
0
votes
1answer
9 views

Server configuration SQL Server 2008

I am really fresh in the DB world so firstly,sorry about my noob question. I have a data warehouse project to perform in a group of 3 people. When installing SQL Server 2008 R2 Developer in my own ...
0
votes
0answers
11 views

Error connecting from other SQL instance using powershell script

I have a job to run a powershell script that will rebuid fragmented index through source SQL instance to target SQL instance. The poweshell script didn't run due to the following error: Sqlcmd: ...
1
vote
2answers
13k views

exporting oracle tables on command line as sql files

I use exp and expdp to export database tables on command line but it generates binary .dmp files. I want to export/import tables as .sql* files on command line like pl/sql developer app can do. Is ...
0
votes
1answer
30 views

Get dates between two months

Can I know why am I getting weird output in my SQL statement whereby my table has two dates of the month May but it only appears for one month, the same things I test for dates range it is working. ...
16
votes
7answers
391 views

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

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. There can be various gaps in the sequence ...
1
vote
1answer
12 views

I have a query which does not return result with the desired alias names of columns in PostgreSQL

The following is my query which does not return result with the desired alias column names: with d as (select distinct emp.ahrims_id, rank.name as rank from hr_employee as emp, ana_rank as rank, ...
1
vote
1answer
36 views

Capitalize only the first letter of a name

I have a table Vendors that contains a column VendorContactFname, which is written in lowercase. I want to capitalize only its first letter. I am thinking of concatenating an Upper on the first ...
0
votes
2answers
10 views

postgresql - how to find and extract text from a description column

I'm trying to create a new table using data from an existing. I have the following structure: Table "public.def" Column | Type ...
0
votes
0answers
4 views

Python and SQL exception handling when internet cuts out

I have a python temperature monitoring program that runs every 30 seconds on my raspberry pi, and uploads the data to an SQL server. I am using try: and except: however this doesn't handle if the ...
17
votes
5answers
581 views

SARGable WHERE clause for two date columns

I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup: USE [tempdb] SET NOCOUNT ON ...
5
votes
3answers
172 views

Selecting one row from each group based on a Varchar field

Given a table Avenger whose data looks like this: id Avenger Type Power 1 Captain America foo 2 2 Captain America bar 3 3 Me foo 5 4 You ...
0
votes
1answer
116 views

Update query for Minimum absolute difference in Access [SQL]

I have 3 tables in an Access db. Table_1 and Table_2 are where the source data comes from and I am filling Table_3. Table_1 has the following fields: Acct_Name Amt Table_2 has the following ...
1
vote
1answer
55 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: ...
0
votes
1answer
205 views

Suitable schema to record email sent and response received and invoicing in DB

When a form submission is made on a website: An email with form's details is sent to a set of recipients email addresses stored in a DB. The email contains a link for the recipient to show interest. ...
1
vote
2answers
25 views

Using Variables to contain column names for SQL Server Merge statemtns

I'm trying to automate the column list grab for a merge job, and running into a bit of a head scratcher. I don't want to have to manually type out all of the column names, and thought I may be able to ...
0
votes
1answer
13 views

SQL 2012 Availability Group issue - The core cluster resources do not move when we initiate AG fail-over

I have a Windows 2012R2 cluster with two nodes and a File Share quorum. I am running SQL 2012 SP2 and I have configured a test AG with a dummy database. When I fail-over the AG, the Availability ...
11
votes
3answers
178 views
+250

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 ...
1
vote
1answer
22 views

Using a join operation with conditionals

I have two tables that I'm working with in my database. I am using SQL Server 2014. One is 'versions', that contains version_id, version_number, start_date, end_date and server_id. The other is ...
1
vote
1answer
10 views

Is DISTINCTROW in MS-Access the same as selecting all the fields and doing a group by on all of them?

I was looking though some queries in MS Access, and I found an SQL keyword I was unfamilar with called DISTINCTROW, from what I can tell, it's the same as an SQL GROUP BY which includes all the ...
0
votes
1answer
32 views

List of queries run

I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user. I have this code for now: SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY ...
0
votes
1answer
35 views

Altering column data SQL [on hold]

I have a set of car data, one of the columns represents the registration year. I want to change this column to age. As the data is from 2010 I assume that the following statement should calculate the ...
0
votes
1answer
625 views

Select Query takes more than a minute to retrieve data

I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK. The Table ...
1
vote
1answer
20 views

Display dates not in ascending for To_Char

can i know why and how i can solve the problem whereby if i use To_Char it does not sort the date in ascending. Below is my code select distinct To_Char (attendance_Date, 'dd/mm/yyyy') from ...
1
vote
1answer
25 views

Using SQLCMD with Variables

Im trying to do some general script that i can use to run the same INSERT/UPDATE or DELETE statements on different site databases. This was my first attempt which works just fine, but i have to copy ...
2
votes
2answers
193 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
0
votes
1answer
19 views

Get data from previous years dynamically

I have a table with columns id, fname, date_field. date_field's records are: 2011-03-01 2012-03-01 2013-03-01 2014-03-01 2015-03-01 2016-03-01 I want the data for the past 2 years for each record ...
0
votes
1answer
102 views

Restore InnoDB from .frm files

My database crashed and my PC and I now only have the .frm and .ibd files left. Is there any way to restore my database?
1
vote
1answer
27 views

Duplicate key value violates a unique constraint

I've been looking at other posts. It seems many people have had this problem, but I'm somewhat new to Postgres and tried other solutions with no luck. I have ny_stations table: CREATE TABLE ...
1
vote
1answer
19 views

index creation - Slow Query - PostgreSQL 9.2

I've got the following query: SELECT * FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
2
votes
1answer
23 views

Relating 2 tables with possible wildcards?

I am having trouble getting the desired results when trying to relate the following tables. NULL in the following table essentially means that if it is not an exact match with another row then choose ...
3
votes
2answers
46 views

SQL Server - What access do I have during a transaction rollback?

As the title says, do I have read/write access to my entire database while it is rolling back a transaction? Or read/write access to only the tables that the transaction did not write to? What is the ...
0
votes
2answers
43 views

How can I create a join on three tables, each from a different database?

Here is a simplified example of what I'd like to do: SELECT a.create_time, b.create_time FROM DOB.dob_state AS a JOIN numID.numID_history AS b ON a.create_time = b.create_time; I'd like to join ...
0
votes
0answers
28 views

best practice to remove leading zeros when writing data to a destination

I have a query like in my other question DBA Question with Source Code of Select statement And i want to remove the leading zeros of the customer Number (KUNDE) with the answer of this question ...
1
vote
2answers
42 views

Join two tables using pivot combining two columns in one table

I have two tables in this format: Job_Skill ID_Skill Min_Job Idea_Job Max_Job ===================================== 1 0 0 1 2 0 1 1 7 1 ...
1
vote
1answer
23 views

Select rows until a condition is met

Given: CREATE TABLE T ( x int not null primary key , y int not null ); INSERT T(x,y) VALUES (1, 0) ,(2, 0) ,(3, 0) ,(4, 0) ,(5, 3) ,(6, 0) ,(7, 4) ,(8, 0) ,(9, 0); The ...
0
votes
2answers
23 views

Using Select Statement to get column name and table name from the same table

I have a table containing 2 Columns : Column1 (table_name) and column2 (column_name) like this : (Column 1) (Column 2) |Column_Name | Table_Name | -------------|------------| ...
1
vote
1answer
23 views

Tricky condition for PostgreSQL UPDATE query

I need help with a PostgreSQL query. I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to ...
1
vote
1answer
24 views

sql getting sum of calculated columns

How can I get the sum of TotalAmount as single value for example Product Name UnitPrice Quantity Discount% Amount SharingCost% ShippingCost CustomsCost TotalAmount pro1 ...
0
votes
1answer
9 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
428 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
253 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
23 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
43 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 ...