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

learn more… | top users | synonyms (2)

0
votes
0answers
6 views

Group by multiple columns with different members of the same hierarchy in MSSQL?

I have customer data in a table structure like this: customerID | productLine1 | productLine2 | productLine3 1 | 1 | null | null 2 | 2 | 1 | null 3 | 4 | 2 | 1 Each customer is assigned to at least ...
0
votes
1answer
33 views

Stored Procedure Contradiction in book

I was reading the 70-461 TSQL training book and the part about stored procedures said this... "Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
1
vote
0answers
10 views

Master-master replication, is it viable?

I'm trying to think of an architecture for my game server. Here is how it would work for two regions: - A server instance runs on one machine - For each machine there is a slave database to speed up ...
1
vote
1answer
158 views

Oracle connection from Squirrel SQL

I am trying to set up Squirrel SQL for the first time on my Ubuntu Desktop. Here is the SQLPlus command I use to connect to my DB: sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com" ...
-6
votes
0answers
16 views

how can i subract TotalExpnese from BudgetAmount In Budget Table? [on hold]

select sum(ExMount) as totalExpense from Budget b, Expense ex where b.BudgetDate=ex.Date
2
votes
1answer
458 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
1
vote
1answer
22 views

Simple Database Design - Many to many? - First database (newbie)

This is my first database I am designing. However I am getting confused on the many to many relationships. I have a products table, each product can have inventories in many different warehouses. So ...
-1
votes
1answer
22 views

Copy tables from SQL Server on another PC to my SQL Server database [on hold]

I want to copy a table's data from a database on another PC to my local database. Can anyone help me with that? I have a different methods of doing that but I need another method. I want to automate ...
1
vote
0answers
28 views

Sql - sort according to other PK column

I Have this table : table : [SheetGroupRank] ------------------------- | Sheet_ID | int | | Group_ID | int | | Rank | int | ------------------------- Sheet_ID and Group_ID ...
1
vote
2answers
37 views

Inserting BLOBs through PHP are always 0 bytes

I'm making a site that allows users to upload small thumbnails for books they post. I want to store them as a BLOB. The first row there was inserted manually through phpMyAdmin. The second row was ...
1
vote
2answers
37 views

How can I hide a column using a Select statement?

declare @bid varchar(4) = '1007' select sr.release_date, sr.customer_id, sr.customer_name, sr.item_id, sr.item_desc, sr.order_no, sr.qty_on_hand, sr.release_qty, ...
0
votes
2answers
319 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
0
votes
2answers
17 views

KILLED/ROLLBACK query doesn't stop running

I've tried to kill a query on ms sql server management studio. Now it has the status killed/rollback. When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I ...
-3
votes
0answers
40 views

Get user rankings based on points from database [on hold]

I've created a website where members collect points to get a reward at the end of the contest. I want to create a page that shows the top 10 members who have the most points. I've tried a few pieces ...
0
votes
2answers
293 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
1
vote
1answer
18 views

Using Case with no column result

I made this solution but it continues not returning any value If the user not exists. select case when TypeId = 1 then 1 when TypeId = 0 then 2 when count(1) < 1 then 3 ...
1
vote
0answers
56 views

Fastest way to shred large xml data into a table

I have a nightly process that shreds XML data from a 17 million row table (120 GB) into another table, it takes about 3 hours and inserts about 1.5 million records. I currently do a complete drop ...
0
votes
1answer
19 views

How to display name on a column chart itself in SSRS, not on a legends

How can I display legends name on a column chart itself in SSRS?
1
vote
0answers
18 views

where to find TPC-C queries

I've been looking around for specific TPC-C queries but haven't found good sources. Does anyone have a pointer? As I'm aware of, TPC-H only has 22 queries which could be easily found here http://www....
1
vote
0answers
59 views

The query processor ran out of stack space during query optimization. Please simplify the query

I'm using SQL Server 2014. When running this query: DELETE FROM shuser WHERE datecreated > '6/21/2016' AND FkShUserCreatedBy = 1 GO I get an error: Msg 8621, Level 17, State 1, Line 1 ...
7
votes
0answers
136 views

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)

Consider these two functions: ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) As far as I understand, they produce exactly the same result. In other ...
0
votes
1answer
24 views

Joining columns with null values into one row [on hold]

I used a sub select to get the data I want. I'm struggling to merge it all into one row now though. This is an example of the result where its bike but there are loads on them: bike bike ...
1
vote
1answer
33 views

How to select user that doesn't exist in the second table

I am trying to understand how to select a user from users table that is not associated with current user in user_follows table: neither in users_id not in shown_users_id. Basically a new user that ...
6
votes
3answers
128 views

Good example of MDX vs SQL for analytical queries

Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results. Wikipedia ...
0
votes
0answers
10 views

Restoring a backup of 1 database to another puts both databases into Restoring mode

We have a 2 databases- Staging Test At times, we refresh out Test environment with a copy of Staging. We do this by taking a full backup of the Staging database and restoring it over the Test ...
0
votes
0answers
11 views

read-ahead reads in 'workfile' cannot be disabled

In SQL Server, I used "DBCC TRACEON (652)" to disable read-ahead reads. However, the output of TPCH query 9 (http://www.qdpma.com/tpch/TPCH100_Query_plans.html), shows: Table 'supplier'. Scan count 9,...
2
votes
1answer
3k views

Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

we are using a Sybase SQL Anywhere 12 db. In the db there are indices, which are unique, but shouldn't be unique. Therefore I search for a quick way to list all tables with unique primary keys. I ...
1
vote
0answers
27 views

Dynamic table name in Insert query

I have asked a question about dynamic table name in a prepared statement here , but I want to know if Mysql server support dynamic table name like this : SET @tbl = 'Dawin'; INSERT INTO @tbl (col1,...
2
votes
2answers
393 views

Teradata view with “With Clause” syntax

I am trying to a view with "With" clause in Teradata but it is not allowing to create a view. Please suggest with any options. Given below is the query which I have tried: create view derived_table(...
3
votes
1answer
16 views

Sybase ASE 15.5 : Create a custom function for partitionning

I want to create a partitioned table to store messages. If the message belongs to "an even week number" => go to the partEven partition ! If the message belongs to "an odd week number" => go to the ...
1
vote
2answers
56 views

How to select sets of data from a table

I have a table containing data that represents a tree structure of data, using the nested set model. I'd like to store data for more than one tree in the table and have only one of the sets of data ...
3
votes
1answer
69 views

Foreign Key Constraint Set Back to Trusted

We have some production tables with numerous constraints that are not trusted at the moment. We're having them checked/trusted so they can be used again. We know that the optimizer has not been ...
0
votes
0answers
25 views

Write SQL Server query result set to Event Viewer

Is it possible to write the output of a SQL Server query as a log entry in the Event Viewer?
0
votes
0answers
15 views

SQL Server 2014 Availability Group Listener only visible to primary node of AG

I'm having some trouble configuring the Availability Group Listener for SQL Server. Here's my current setup: I have one server, lets say server DC, which acts as the domain controller for AD DS with ...
1
vote
1answer
55 views

Aggregation using CASE

I have a table called country: CountryName | Sales A 200 B 100 C 300 D 500 E 400 Now I in my report I have hardcoded the parameter of ...
1
vote
0answers
20 views

SELECT UNION from multiple tables with same schema in MySQL

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT ...
7
votes
1answer
945 views

Using Two LEFT JOIN or use AND with single LEFT JOIN?

Using two LEFT JOINs i.e. SELECT <some columns> FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON <condition1> LEFT JOIN Table2 AS t3 ON <condition2> is this same as using AND in ...
0
votes
1answer
26 views

Returning data based on if another column in another table is NULL

I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been ...
1
vote
0answers
29 views

Query Adjustment Issue - going Haywire

I am having difficulty with a query and after a day or researching, etc, I have decided to seek some advice here. My current query runs and looks like the table below: Type Resource Code ...
3
votes
2answers
73 views

Time based query help

I can't seem to get this to work the way I need it to. I have two tables Assignment and Event. Assignment StartDate EndDate Equipment 4/1/2016 4/23/2016 1001 4/3/2016 4/10/...
3
votes
2answers
75 views

What is wrong with this SQL statement?

I cannot get this statement running on MySQL server: DECLARE @tabl varchar(40) SET @tabl = :uId; SET @location = CONCAT('data_',@tabl); SET @b = 'INSERT INTO' + @location + '(Id, uId , name , address,...
4
votes
1answer
4k views

Split comma separated entries to rows

I have a Table like this: | ID | OtherID | Data +--------+-----------+--------------------------- | 5059 | 73831 | 5103,5107 | 5059 | 73941 | 5103,5104,5107 | 5059 | 73974 | ...
4
votes
1answer
383 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
2
votes
1answer
4k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
3
votes
1answer
22 views

UPDATE a column in one table according to a column in another

In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the ...
4
votes
2answers
148 views

Update old database with SQL dump

I had to recover a database with a some months old full backup. I have however in my possession a most recent but unfortunately incomplete SQL dump for this same database done with mysqldump. Is ...
3
votes
1answer
53 views

Self-JOIN with occurrence count

I use PostgreSQL 9.4 and I have a table called certificates containing the following columns: CREATE TABLE certificates ( id serial PRIMARY KEY, common_name varchar(255) NOT NULL, state ...