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

learn more… | top users | synonyms (2)

3
votes
1answer
63 views

Wich is the most efficient/easy way for end-user to get data from Analysis database?

I know that you can quey a cube by connecting from excel to Analysis database and using formulas like cubevalue() or cubemember(). I also know that after converting the power pivot to formulas you can ...
1
vote
4answers
94 views

PostgreSQL execute a default query when no row are returned

I need to calculate the average score of a list of userId. I have a table user_place_score: (user_id, place_id, score) Sometimes I do not have the score (no row for (user_id,place_id) in the table)...
2
votes
1answer
70 views

Auditing/Logging Help

I'm working on a request to start logging some additional data for PCI compliance. I need to log who, when, and what changed on a set of tables. I set up SQL auditing which would work fine except ...
0
votes
1answer
69 views

same table on different commands (inner join)

Testing some commands of MySQL, I've come across these two comands resulting on the same table. Command 1: select sales.relatory_code, sales.date, destiny.exportation_name from sales, destiny where ...
2
votes
1answer
136 views

I can't create relationship in my database sql server

I have designed a database for my website. It has 3 tables: user, topic and comment. A user has many topics (or articles) A topic (or article) has many comments A user has many comments I ...
2
votes
1answer
2k views

Alter table Statement Failed -Msg 207, Level 16, State 1

I wrote a script to add a column in a table and on successful execution update its values. I know it’s just a simple script but what is wrong with the ‘IF NOT EXISTS’ statement why script between ...
1
vote
1answer
935 views

Why is Excel File Opened Exclusively By Another?

I'm creating an SSIS package to loop through a series of Excel files (.xls). I create my while loop container and then create my data flow, which includes an Excel source. When I edit my Excel ...
1
vote
3answers
162 views

Remove duplicates from different columns

I'm trying to remove duplicates from a set, but the duplicates are in different columns, so for example with this table: ColA, ColB, ColC, ColD ---------- 1, 1, 'ABC', 'DEF' ---------- 1, 1, 'DEF', ...
1
vote
0answers
87 views

Does partitioning help if you don't query on the partition value?

Say I have an orders.Order table that is partitioned on CreatedWhen (a datetime column set to GetDate() when the row is created). The table has over 10 million rows and the partition is quarterly (...
2
votes
2answers
200 views

Should I set document version field to be part of a primary key

Should I set document version as a primary key I am working on a document management system, and I have these two tables representing part of the whole tables. On the DocumentVersion table I have ...
0
votes
1answer
58 views

Aggregating two queries and returning a COUNT of some permutations

Hopefully the title can make sense to somebody, so I can get somebody working with a database (BigQuery in my case) to look into into. Given two quarters, I need to find out the # of orders placed by ...
1
vote
1answer
114 views

T-SQL Create Query - Compare and Calculate

I got a mini fantasy league (NBA) and would like some help with a query that turns averages into "ranking points". Basically I got two tables. Source table: This is a table with statistical per ...
1
vote
1answer
41 views

How to use insert and select simultaneously? [duplicate]

I have table test and it has columns id,name and age.I want the id so I am querying like it select id from test where age=12 After I get the id I am updating the name using update query Now lets ...
0
votes
1answer
389 views

SQL access query to count and delete rows > n

Can someone help me with a SQL (Access) query to delete all rows greater than n entries ordered by ID? I.e. say I have 1200 rows of data with ID incremented . I need delete the first amount leaving ...
1
vote
2answers
464 views

How to List SYNONYMS that no longer point to a valid object?

I have a table (OBJECTS_TO_UPDATE) with several objects I need to update, some are SYNONYMs for objects that no longer exist. I want to create a query to list them. But I am stuck. My table ...
8
votes
3answers
6k views

Using DISTINCT in window function with OVER

I'm trying to migrate a query from Oracle to SQL Server 2014. Here is my query which works great in Oracle: select count(distinct A) over (partition by B) / count(*) over() as A_B from MyTable ...
1
vote
0answers
50 views

Is there any way to improve this code?

SELECT tblstud.studid, tblsched.ip FROM tblstud LEFT JOIN tblstudsched ON (tblstud.studid=tblstudsched.studid) LEFT JOIN tblsched ON (tblstudsched.schedid=tblsched.schedid) ...
0
votes
1answer
70 views

selecting table1 and table2 with single data row and table 3 with multiple data rows

I want to retrieve data from table1(payments), table2(properties), and table3 (property.assessments). See my visual representation below: tables screenshot Snippets: SELECT a.payment_id, a....
1
vote
1answer
301 views

Get all the data between Characters [closed]

I am trying to extract all the variable from the following string; string = 'factor1|factor2|factor3' This is just a test string. In real string, I do not know the number of factors. What I want is ...
0
votes
0answers
58 views

Help with Joins - Cartesian Product

I have 5 regions that each contain 1 store and 1 warehouse. I have a table that contains the regions and corresponding stores and warehouses. I also have a table that lists the inventories of both ...
2
votes
0answers
36 views

Snapshot Agent - Transactional Replication - Why a complete snapshot?

Often, I will add a single article to an existing transactional replication publication. I then start the snapshot agent to copy the new article and it's data over to the subscriber. Most of the time, ...
2
votes
2answers
1k views

SQL Server DROP Database which is in a long roll back process

I have a DataBase that is taking very long to rollback a transactions. Which could be normally because it is rolling back a hugh amount of data. However I have the scripts to create the DB again and ...
0
votes
0answers
1k views

Unable to connect to or create SQL Server database using asp.net [closed]

In my asp.net project I have SQL Server database with some tables. Unfortunately my PC was not properly shut down and and after that I am unable to connect to my database from Visual Studio (I am ...
0
votes
2answers
107 views

what could be the Best practices with very very large amount of data [closed]

I have a question on building a solution.I have a few txt/csv file having huge amount of data approx 30000000000 rows .By size they are 1.5 TB.The tables based on these data are only for lookup,I mean,...
4
votes
3answers
2k views

Help with SQL Query to XML output

I have the following query: SELECT [ID] AS [NewID] , ( CASE WHEN LEN([MiddleName]) > 0 THEN [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.' ...
0
votes
1answer
3k views

Behaviour of RAISERROR within a TRY/CATCH within a loop

I have just encountered a section of SQL that is behaving not as I would expect (See below for a distilled version of the SQL in question that demonstrates the problem pattern). (This is on SQL ...
1
vote
1answer
2k views

DB2 - Not enough storage in the database heap error when trying to create database

So like the title says I'm trying to create a database using this script db2 -v -t "CREATE DATABASE %DBNAME% ON '%DBDRIVE%' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM CATALOG TABLESPACE ...
1
vote
1answer
111 views

“No Loop” Constraint for hierarchical data in postgresql

I've got table like this in postgres: id primary key parent_id foreign key on id some data columns I need no a constrain which prevents cycles on the parent relations. There might be more tree in ...
1
vote
2answers
3k views

Stored proc getting ANSI_NULLS and ANSI_WARNINGS error when executed by application

I have a stored procedure that is getting the following error when executed via an application : Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection....
0
votes
1answer
45 views

Will canceling InnoDB compression that's already in progress corrupt data?

I have a 155GB table that is in the process of being compressed. ALTER TABLE $table_name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; This compression has been running for a long time and is impeding my ...
0
votes
1answer
168 views

How to get average according to last update time?

There is table - Item below, how can I get the average Value according to its 15 minute value from last record time? I am using SQL Server 2008. Table -- Item Table 15min_AVG
2
votes
1answer
137 views

Help Changing Collate of ALL databases

I have approximately 80 Databases with various collations. I would like to modify the collation of all 80 databases. I have the following code: USE [COLLATE_ADM] GO SET ANSI_NULLS ON GO SET ...
0
votes
0answers
28 views

Question? Creating a website that has 2 sets of intermidate tables

I am creating a web site that uses 6 tables from one database in mysql (example) 2 sets of tables that connect to intermediate tables (using foreign keys to join the tables). 1) Category -> ...
0
votes
2answers
109 views

MS Access Query to update

Trying to update Packet.MSGState to P where if not R or D and it is Y in table Packet.MSGState UPDATE Packet SET Packet.MSGState = 'P' Where (MSGState <> 'R' and MSGState <> 'D') and ...
1
vote
1answer
942 views

Insert rows in a table, via a row-level trigger, activated after insertion in that same table

I have to design a schema and trigger system in SQL that will save words and their synonyms. I thought of this basic schema: create table words ( id int primary key auto_increment, word ...
1
vote
1answer
99 views

Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value

I am new to triggers and SQL in general. Sorry in advance if this forum is only for experts and this is a newbie question... I need to create a trigger that fires when someone tries to submit a grade(...
1
vote
1answer
353 views

Sql Server 2012 Change Data Capture weird behaviour

In my Sql Server 2012 I have the following table in a database CREATE TABLE [dbo].[HumanResource]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [IsHired] [bit] NULL,...
1
vote
2answers
66 views

Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine. SELECT DISTINCT nom.pais_id FROM negocio.fabricante_producto_solicitud ...
0
votes
1answer
21 views

Selecting one variant out of different similar results

I have a table that looks as follows: Book Format Contents A html ... A markdown ... B html ... C markdown ... I want to return only 1 version of a book. ...
1
vote
1answer
269 views

What do these parameters mean at the beginning of my SQL Server query plan?

I'm looking at query plan in SQL Server Mgmt Studio and I see something like this: (@P0 nvarchar(4000), @P1 nvarchar(4000)) Update... What does the type mean beside the parameters? For this example ...
0
votes
2answers
2k views

Adding Last 12 Months to a row in a table (SQL Server)

I want to add the last 12 months to a SQL Server table, something like the following: FixedText Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Please can ...
1
vote
2answers
1k views

Understanding multiple table join with aggregation

I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2 CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); ...
2
votes
1answer
69 views

Having problems with concurrency while trying to lock a record in custom messaging system (Queue)

We are building our own custom messaging system and are having concurrency issues. Here are the rules: A process (EXE) console application locks 3 records and returns them No other process running (...
0
votes
2answers
1k views

How can i automate the process of log shipping monitoring

I have log shipping configured between my Prod SQL server 2005 and QA SQL server 2005. Per the requirement from Business we need to provide them with a report at the end of the day for the LOG ...
8
votes
2answers
251 views

Are “out of the row” fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored. I have the ...
1
vote
1answer
46 views

Having troubles creating table SQL [closed]

I'm new to SQL and Im trying to create a simple database, but I'm having problems with second one, I'm using Oracle Database express 11 and when I try to run this code I get error: Missing right ...
7
votes
1answer
5k views

Why not rebuild indexes with page count <1000?

I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as '...
0
votes
2answers
44 views

sync 2 tables in a single database in mysql with no duplicacy [closed]

I have 2 tables in a single database of MySQL. The structures are like this: mysql> desc main_tbl; +-----------------+--------------+------+-----+-------------------+----------------+ | Field ...
2
votes
1answer
5k views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date? Also is it good ...
4
votes
1answer
705 views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 Refer to this question to see how the query was produced. The version in the ...